❤
npm
Sign UpSign In

@arlequins/aurora-data-api
TypeScript icon, indicating that this package has built-in type declarations

2.0.0-alpha.1 • Public • Published

Aurora Data API Client

Master NPM

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

Name Description Required Default
resourceArn The Amazon Resource Name (ARN) of the Aurora Serverless DB cluster. Yes undefined
secretArn The name or ARN of the secret that enables access to the DB cluster. Yes undefined
database The name of the database. No undefined
schema The name of the database schema. No undefined
includeResultMetadata A value that indicates whether to include metadata in the results. Note: must be true for the results to be transformed. No true
continueAfterTimeout A value that indicates whether to continue running the statement after the call times out. By default, the statement stops running when the call times out.

For DDL statements, we recommend continuing to run the statement after the call times out. When a DDL statement terminates before it is finished running, it can result in errors and possibly corrupted data structures.		 No undefined
resultSetOptions Options that control how the result set is returned. No undefined
valueTransformer See Value Transformer. No undefined
...rest Unspecified properties (i.e. properties from RDSDataService.ClientConfiguration) will be used to construct the RDSDataService client. See the AWS SDK docs for more info. Conditional - see note below. undefined

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

Class Properties

Name Description
client Instance of the RDSDataService.
requestConfig Object containing properties to send to the RDSDataService methods.

AuroraDataAPI methods

Name Description
query Performs an SQL query.
batchQuery Runs a batch SQL statement over an array of data.
beginTransaction Starts a SQL transaction.
commitTransaction Commits and ends a SQL transaction.
rollbackTransaction Rolls-back and ends a SQL transaction.
executeStatement Abstraction of the RDSDataService#executeStatement method.
batchExecuteStatement Abstraction of the RDSDataService RDSDataService#batchExecuteStatement method.

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

Name Description Required
sql The SQL query string to perform. Yes
params See Query Params. No
options See options below. No

Options

Name Description Default
includeResultMetadata A value that indicates whether to include metadata in the results. Note: must be true for the results to be transformed. Defined in constructor.
continueAfterTimeout A value that indicates whether to continue running the statement after the call times out. By default, the statement stops running when the call times out.

For DDL statements, we recommend continuing to run the statement after the call times out. When a DDL statement terminates before it is finished running, it can result in errors and possibly corrupted data structures.		 Defined in constructor.
database The name of the database. Defined in constructor.
resourceArn The Amazon Resource Name (ARN) of the Aurora Serverless DB cluster. Defined in constructor.
resultSetOptions Options that control how the result set is returned. Defined in constructor.
schema The name of the database schema. Defined in constructor.
secretArn The name or ARN of the secret that enables access to the DB cluster. Defined in constructor.
transactionId The identifier of a transaction that was started by using the BeginTransaction operation. Specify the transaction ID of the transaction that you want to include the SQL statement in. undefined
valueTransformer See Value Transformer. Defined in constructor.

Further reading

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

Name Description Required
sql The SQL query string to perform. Yes
params An array of Query Params. Maximum of 1,000. No
options See options below. No

Options

Name Description Default
database The name of the database. Defined in constructor.
resourceArn The Amazon Resource Name (ARN) of the Aurora Serverless DB cluster. Defined in constructor.
schema The name of the database schema. Defined in constructor.
secretArn The name or ARN of the secret that enables access to the DB cluster. Defined in constructor.
transactionId The identifier of a transaction that was started by using the BeginTransaction operation. Specify the transaction ID of the transaction that you want to include the SQL statement in. undefined

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:

A transaction can run for a maximum of 24 hours. A transaction is terminated and rolled back automatically after 24 hours.

A transaction times out if no calls use its transaction ID in three minutes. If a transaction times out before it's committed, it's rolled back automatically.

DDL statements inside a transaction cause an implicit commit. We recommend that you run each DDL statement in a separate ExecuteStatement call with continueAfterTimeout enabled.

Arguments

Name Description Required
options See options below. No

Options

Name Description Default
database The name of the database. Defined in constructor.
resourceArn The Amazon Resource Name (ARN) of the Aurora Serverless DB cluster. Defined in constructor.
schema The name of the database schema. Defined in constructor.
secretArn The name or ARN of the secret that enables access to the DB cluster. Defined in constructor.

Further reading

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

Name Description Required
transactionId The identifier of the transaction to end and commit. Yes
options See options below. No

Options

Name Description Default
resourceArn The Amazon Resource Name (ARN) of the Aurora Serverless DB cluster. Defined in constructor.
secretArn The name or ARN of the secret that enables access to the DB cluster. Defined in constructor.

Further reading

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

Name Description Required
transactionId The identifier of the transaction to roll back. Yes
options See options below. No

Options

Name Description Default
resourceArn The Amazon Resource Name (ARN) of the Aurora Serverless DB cluster. Defined in constructor.
secretArn The name or ARN of the secret that enables access to the DB cluster. Defined in constructor.

Further reading

AuroraDataAPI#executeStatement

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

Abstraction of the AWS SDK RDSDataService executeStatement operation.

From the SDK Docs:

Runs a SQL statement against a database.

If a call isn't part of a transaction because it doesn't include the transactionId parameter, changes that result from the call are committed automatically.

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

Name Description Required
options See options below. Yes

Options

Name Description Default
sql (Required) The SQL statement to run. undefined
includeResultMetadata Includes the column metadata. undefined
continueAfterTimeout A value that indicates whether to continue running the statement after the call times out. By default, the statement stops running when the call times out.

For DDL statements, we recommend continuing to run the statement after the call times out. When a DDL statement terminates before it is finished running, it can result in errors and possibly corrupted data structures.		 Defined in constructor.
database The name of the database. Defined in constructor.
resourceArn The Amazon Resource Name (ARN) of the Aurora Serverless DB cluster. Defined in constructor.
resultSetOptions Options that control how the result set is returned. Defined in constructor.
schema The name of the database schema. Defined in constructor.
secretArn The name or ARN of the secret that enables access to the DB cluster. Defined in constructor.
transactionId The identifier of a transaction that was started by using the BeginTransaction operation. Specify the transaction ID of the transaction that you want to include the SQL statement in. undefined

Further reading

AuroraDataAPI#batchExecuteStatement

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

Abstraction of the AWS SDK RDSDataService batchExecuteStatement operation.

From the SDK docs:

Runs a batch SQL statement over an array of data.

You can run bulk update and insert operations for multiple records using a DML statement with different parameter sets. Bulk operations can provide a significant performance improvement over individual insert and update operations.

If a call isn't part of a transaction because it doesn't include the transactionId parameter, changes that result from the call are committed automatically.

Arguments

Name Description Required
options See options below. Yes

Options

Name Description Default
sql (Required) The SQL statement to run. undefined
parameterSets The parameter set for the batch operation. The maximum number of parameters in a parameter set is 1,000. undefined
database The name of the database. Defined in constructor.
resourceArn The Amazon Resource Name (ARN) of the Aurora Serverless DB cluster. Defined in constructor.
schema The name of the database schema. Defined in constructor.
secretArn The name or ARN of the secret that enables access to the DB cluster. Defined in constructor.
transactionId The identifier of a transaction that was started by using the beginTransaction method. Specify the transaction ID of the transaction that you want to include the SQL statement in. undefined

Further reading

AuroraDataAPI.Transaction methods

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

Name Description
query Performs an SQL query in the transaction.
batchQuery Performs an SQL query over an array of data in the transaction.
commit Commits and ends the transaction.
rollback Rolls-back and ends the transaction.
executeStatement Abstraction of the RDSDataService#executeStatement method in context of the transaction.
batchExecuteStatement Abstraction of the RDSDataService RDSDataService#batchExecuteStatement method in context of the transaction.

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

Name Description Required
sql The SQL statement string to perform. Yes
params See Query Params. No
options See options below. No

Options

Name Description Default
includeResultMetadata A value that indicates whether to include metadata in the results. Note: must be true for the results to be transformed. Defined in #beginTransaction.
continueAfterTimeout A value that indicates whether to continue running the statement after the call times out. By default, the statement stops running when the call times out.

For DDL statements, we recommend continuing to run the statement after the call times out. When a DDL statement terminates before it is finished running, it can result in errors and possibly corrupted data structures.		 Defined in #beginTransaction.
resultSetOptions Options that control how the result set is returned. Defined in #beginTransaction.
valueTransformer See Value Transformer. Defined in #beginTransaction

Further reading

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

Name Description Required
sql The SQL query string to perform. Yes
params An array of Query Params. Maximum of 1,000. No

Further reading

AuroraDataAPI.Transaction#commit

Commits and ends the transaction.

commit() => Promise<AuroraDataAPI.CommitTransactionResult>

Further reading

AuroraDataAPI.Transaction#rollback

rollback() => Promise<AuroraDataAPI.RollbackTransactionResult>

Rolls-back and ends the transaction.

Further reading

AuroraDataAPI.Transaction#executeStatement

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

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

Arguments

Name Description Required
options See options below. Yes

Options

Name Description Default
sql (Required) The SQL statement to run. undefined
includeResultMetadata A value that indicates whether to include metadata in the results. Defined in #beginTransaction.
continueAfterTimeout A value that indicates whether to continue running the statement after the call times out. By default, the statement stops running when the call times out.

For DDL statements, we recommend continuing to run the statement after the call times out. When a DDL statement terminates before it is finished running, it can result in errors and possibly corrupted data structures.		 Defined in #beginTransaction.
resultSetOptions Options that control how the result set is returned. Defined in #beginTransaction.

Further reading

AuroraDataAPI.Transaction#batchExecuteStatement

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

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

Arguments

Name Description Required
options See options below. Yes

Options

Name Description Default
sql (Required) The SQL statement to run. undefined
parameterSets The parameter set for the batch operation. The maximum number of parameters in a parameter set is 1,000. undefined

Further reading

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

Value Type Transformed Field
string { stringValue: value }
boolean { booleanValue: value }
number (integer) { longValue: value }
number (float) { doubleValue: value }
string[] { arrayValue: { stringValues: values } }
boolean[] { arrayValue: { booleanValues: values } }
number[] (integer) { arrayValue: { longValues: values } }
number[] (float) { arrayValue: { doubleValues: values } }
instanceof Buffer { blobValue: value }
instanceof Uint8Array { blobValue: value }

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.

Readme

Keywords

Package Sidebar

Install

npm i @arlequins/aurora-data-api

Repository

github.com/arlequins/data-api-suite

Homepage

github.com/arlequins/data-api-suite/tree/master/packages/aurora-data-api

Weekly Downloads

1

Version

2.0.0-alpha.1

License

MIT

Unpacked Size

77.5 kB

Total Files

24

Last publish

Collaborators

  • arlequins
Try on RunKit
Report malware