@caspiandb/aurora-data-api v1.0.0-alpha.20

Aurora Data API Client

Summary

Aurora Data API Client is an abstraction of the RDSDataService that implements the Data API for Aurora Serverless.

Query requests are simplified; instead of expecting clunky query params it expects only a JSON object:

// using AuroraDataAPI
await auroraDataAPI.query('SELECT * FROM users where id = :id', { id: 42 })

// using RDSDataService
await rdsDataService.executeStatement({
  sql: 'SELECT * FROM users where id = :id',
  includeResultMetadata: true,
  parameters: [{
    value: {
      longValue: 42
    }
  }]
}).promise()

Results are also simplified by transforming the records into a ready-to-use JS object:

// response from AuroraDataAPI#query
{
  rows: [
    {
      id: 7,
      name: 'Filip J Fry'
    },
    {
      id: 9,
      name: 'Bender Bending Rodriguez'
    }
  ],
  metadata: {
    id: {
      tableName: 'users',
      name: 'id',
      typeName: 'int8',
      // ...everything else from columnMetadata
    },
    name: {
      tableName: 'users',
      name: 'name',
      typeName: 'varchar',
      // ...
    }
  },
  // ...includes the original result payload too
}

// response from RDSDataService#executeStatement
{
  records: [
    [
      {
        longValue: 7
      },
      {
        stringValue: 'Filip J Fry'
      }
    ],
    [
      {
        longValue: 9
      },
      {
        stringValue: 'Bender Bending Rodriguez'
      }
    ]
  ],
  columnMetadata: [
    {
      tableName: 'users',
      name: 'id',
      typeName: 'int8',
      // ...everything else from columnMetadata
    },
    {
      tableName: 'users',
      name: 'name',
      typeName: 'varchar',
      // ...
    }
  ]
}

Data API for Aurora Serverless Suite

This library is part of the Data API for Aurora Serverless Suite, a monorepo that includes libraries, Serverless Framework plugins and development tools to simplify and enhance the development, deployment and use of the Data API for Aurora Serverless on Amazon Web Services.

Installation

$ npm install aurora-data-api --save

Usage

import * from AuroraDataAPI
const auroraDataAPI = new AuroraDataAPI({ ...config })
auroraDataAPI.query('SELECT * FROM users').then(console.log)

AuroraDataAPI#constructor

new AuroraDataAPI(
  config: AuroraDataAPI.Config
) => AuroraDataAPI

Constructs a new instance of AuroraDataAPI.

Config

Note: The RDSDataService can be constructed without any properties, for instance when the Global Configuration Object is set.

Class Properties

AuroraDataAPI methods

AuroraDataAPI#query

query<T = AuroraDataAPI.UnknownRow>(
  sql: string,
  params?: AuroraDataAPI.QueryParams,
  options?: AuroraDataAPI.QueryOptions
) => Promise<AuroraDataAPI.QueryResult<T>>

Performs an SQL statement.

The response size limit is 1 MB or 1,000 records. If the call returns more than 1 MB of response data or over 1,000 records, the call is terminated.

Arguments

Options

Further reading

• https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#executeStatement-property
• https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_ExecuteStatement.html

AuroraDataAPI#batchQuery

batchQuery(
  sql: string,
  params?: AuroraDataAPI.QueryParams[],
  options?: AuroraDataAPI.BatchQueryOptions
) => Promise<AuroraDataAPI.BatchQueryResult>

Runs a batch SQL statement over an array of data.

Arguments

Options

AuroraDataAPI#beginTransaction

beginTransaction(
  options?: AuroraDataAPI.BeginTransactionOptions
) => Promise<AuroraDataAPI.Transaction>

Starts a SQL transaction and resolves an instance of AuroraDataAPI.Transaction.

From the AWS SDK Docs:

Arguments

Options

Further reading

• https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#beginTransaction-property
• https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_BeginTransaction.html

AuroraDataAPI#commitTransaction

commitTransaction(
  transactionId: string,
  options?: AuroraDataAPI.CommitTransactionOptions
) => Promise<AuroraDataAPI.CommitTransactionResult>

Ends a SQL transaction started with the beginTransaction method and commits the changes.

Arguments

Options

Further reading

• https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#commitTransaction-property
• https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_CommitTransaction.html

AuroraDataAPI#rollbackTransaction

rollbackTransaction(
  transactionId: string,
  options?: AuroraDataAPI.CommitTransactionOptions
) => Promise<AuroraDataAPI.RollbackTransactionResult>

Ends a SQL transaction started with the beginTransaction method and rolls-back the changes.

Arguments

Options

Further reading

• https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#rollbackTransaction-property
• https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_RollbackTransaction.html

AuroraDataAPI#executeStatement

executeStatement(
  options: AuroraDataAPI.ExecuteStatementOptions
) => Promise<RDSDataService.ExecuteStatementResponse>

Abstraction of the AWS SDK RDSDataService executeStatement operation.

From the SDK Docs:

Arguments

Options

Further reading

• https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#executeStatement-property
• https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_ExecuteStatement.html

AuroraDataAPI#batchExecuteStatement

batchExecuteStatement(
  options: AuroraDataAPI.BatchExecuteStatementOptions
) => Promise<RDSDataService.BatchExecuteStatementResponse>

Abstraction of the AWS SDK RDSDataService batchExecuteStatement operation.

From the SDK docs:

Arguments

Options

Further reading

• https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#batchExecuteStatement-property
• https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_BatchExecuteStatement.html

AuroraDataAPI.Transaction methods

AuroraDataAPI#beginTransaction resolves an instance of AuroraDataAPI.Transaction that exposes the following methods:

AuroraDataAPI.Transaction#query

query<T = AuroraDataAPI.UnknownRow>(
  sql: string,
  params?: AuroraDataAPI.QueryParams,
  options?: AuroraDataAPI.Transaction.TransactionQueryOptions
) => Promise<AuroraDataAPI.QueryResult<T>>

Performs an SQL statement in the transaction.

The response size limit is 1 MB or 1,000 records. If the call returns more than 1 MB of response data or over 1,000 records, the call is terminated.

Arguments

Options

Further reading

• https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#executeStatement-property
• https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_ExecuteStatement.html

AuroraDataAPI.Transaction#batchQuery

batchQuery(
  sql: string,
  params?: AuroraDataAPI.QueryParams[]
) => Promise<AuroraDataAPI.QueryResult<T>>

Performs a batch SQL statement over an array of data in the transaction.

Arguments

Further reading

• https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#batchExecuteStatement-property
• https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_BatchExecuteStatement.html

AuroraDataAPI.Transaction#commit

Commits and ends the transaction.

commit() => Promise<AuroraDataAPI.CommitTransactionResult>

Further reading

• https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#commitTransaction-property
• https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_CommitTransaction.html

AuroraDataAPI.Transaction#rollback

rollback() => Promise<AuroraDataAPI.RollbackTransactionResult>

Rolls-back and ends the transaction.

Further reading

• https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#rollbackTransaction-property
• https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_RollbackTransaction.html

AuroraDataAPI.Transaction#executeStatement

executeStatement(
  options: AuroraDataAPI.Transaction.ExecuteStatementOptions
) => Promise<RDSDataService.ExecuteStatementResponse>

Abstraction of the RDSDataService#executeStatement method in context of the transaction.

Arguments

Options

Further reading

• https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#executeStatement-property
• https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_ExecuteStatement.html

AuroraDataAPI.Transaction#batchExecuteStatement

batchExecuteStatement(
  options: AuroraDataAPI.Transaction.BatchExecuteStatementOptions
) => Promise<RDSDataService.BatchExecuteStatementResponse>

Abstraction of the RDSDataService RDSDataService#batchExecuteStatement method in context of the transaction.

Arguments

Options

Further reading

• https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/RDSDataService.html#batchExecuteStatement-property
• https://docs.aws.amazon.com/rdsdataservice/latest/APIReference/API_BatchExecuteStatement.html

Query Params

Query params are, to put simply, just a JS object of names and values. The name is attributed to the placeholder in your SQL statement, for instance:

query('SELECT * FROM users WHERE id = :id', { id: 99 })

The SQL statement with the placeholder :id will be interpreted as SELECT * FROM users WHERE id = 99.

The Data API for Aurora Serverless however, expects parameters to be defined like so:

[
  {
    name: 'id',
    value: {
      longValue: 99
    }
  }
]

AuroraDataAPI transforms your query params by inspecting the type of the value. If you need more control, for instance, when inserting JSON, you can define your params using AWS's structure, for example:

query('UPDATE users SET json_data = :jsonData WHERE id = :id', {
  id: 99,
  jsonData: {
    typehint: 'json',
    value: {
      stringValue: JSON.stringify({ foo: 'bar' })
    }
  }
})

Note that name is omitted from the object because it is defined by the key.

See The docs on RDSDataService#executeStatement and the SqlParameter documentation for more information on how to structure your parameter in the "RDSDataService way".

How values are transformed

Multidimensional arrays are also transformed recursively:

// these AuroraDataAPI Query parms...
{
  threeDimensions: [
    [
      ['1.1.1', '1.1.2'],
      ['1.2.1', '1.2.2']
    ],
      ['2.1.1', '2.1.2'],
      ['2.2.1', '2.2.2']
  ]
}

// are transformed into these RDSDataService parameters...
[
  {
    name: 'threeDimensions',
    value: {
      arrayValue: {
        arrayValues: [
          {
            arrayValues: [
              {
                arrayValues: {
                  stringValues: ['1.1.1', '1.1.2']
                }
              },
              {
                arrayValues: {
                  stringValues: ['1.2.1', '1.2.2']
                }
              }
            ],
            arrayValues: [
              {
                arrayValues: {
                  stringValues: ['2.1.1', '2.1.2']
                }
              },
              {
                arrayValues: {
                  stringValues: ['2.2.1', '2.2.2']
                }
              }
            ]
          }
        ]
      }
    }
  }
]

Custom value classes

Support for CustomValue classes make it easy to "DRY" your code. They offer a way to encapsulate your values so they can be transformed into an SqlParameter. Here is an example of a uuid value:

class UUIDValue implements AuroraDataAPI.CustomValue {
  private value: string

  constructor (value: string) {
    this.value = value
  }

  toSqlParameter (): AuroraDataAPI.SqlParameter {
    return {
      typeHint: 'uuid',
      value: {
        stringValue: this.value
      }
    }
  }
}

await query(
  'SELECT * FROM users WHERE uuid = :uuid',
  { uuid: new UUIDValue(req.params.uuid) }
)

// it works with arrays too:
await query(
  'SELECT * FROM users WHERE uuid = ANY(:uuids)',
  { uuids: req.params.uuids.map((uuid) => new UUIDValue(uuid)) }
)

A few predefined custom values are available: AuroraDataAPI.JSONValue and AuroraDataAPI.BlobValue, for more information please take a look at src/customValues.ts.

Value Transformer

When includeResultMetadata is true, the Data API response payload includes metadata for each of the columns in the result set. In cases such as timezone, json or jsonb columns, the column value will be returned as a stringValue (or stringValues in the case of an array).

By default AuroraDataAPI some types (such as those described above) into the expected objects. e.g. a timezone column will be parsed with new Date, a jsonb column will parsed using JSON.parse.

You can override and/or extend the default behavior by providing your own valueTransformer like so:

const valueTransformer = (
  value: any,
  metadata: RDSDataService.ColumnMetadata,
  next: Function
) => {
  if (
    metadata.typeName === 'varchar' &&
    typeof value === 'string'
  ) {
    return value.toUpperCase()
  }
  return next() // remove this line to disable the default value transformer
}

// construct AuroraDataAPI with your new value transformer
const client = new AuroraDataAPI({
  ...config,
  valueTransformer
})

// or add it to the options argument on #query or transaction#query
const result = await client.query(
  'SELECT email FROM users',
  undefined,
  { valueTransformer }
) // => { rows: [{ email: 'EXAMPLE@EXAMPLE.COM' }] }

Example

Take a look at the example folder for a complete example app that uses all the Data API for Aurora Serverless Suite packages.

MIT License

Copyright (c) 2020 Marc Greenstock

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.