1.0.0-alpha.20 • Published 1 year ago

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

Weekly downloads
-
License
MIT
Repository
github
Last release
1 year ago

Aurora Data API Client

Master npm (scoped)

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

NameDescriptionRequiredDefault
resourceArnThe Amazon Resource Name (ARN) of the Aurora Serverless DB cluster.Yesundefined
secretArnThe name or ARN of the secret that enables access to the DB cluster.Yesundefined
databaseThe name of the database.Noundefined
schemaThe name of the database schema.Noundefined
includeResultMetadataA value that indicates whether to include metadata in the results. Note: must be true for the results to be transformed.Notrue
continueAfterTimeoutA 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.Noundefined
resultSetOptionsOptions that control how the result set is returned.Noundefined
valueTransformerSee Value Transformer.Noundefined
...restUnspecified 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

NameDescription
clientInstance of the RDSDataService.
requestConfigObject containing properties to send to the RDSDataService methods.

AuroraDataAPI methods

NameDescription
queryPerforms an SQL query.
batchQueryRuns a batch SQL statement over an array of data.
beginTransactionStarts a SQL transaction.
commitTransactionCommits and ends a SQL transaction.
rollbackTransactionRolls-back and ends a SQL transaction.
executeStatementAbstraction of the RDSDataService#executeStatement method.
batchExecuteStatementAbstraction 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

NameDescriptionRequired
sqlThe SQL query string to perform.Yes
paramsSee Query Params.No
optionsSee options below.No

Options

NameDescriptionDefault
includeResultMetadataA value that indicates whether to include metadata in the results. Note: must be true for the results to be transformed.Defined in constructor.
continueAfterTimeoutA 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.
databaseThe name of the database.Defined in constructor.
resourceArnThe Amazon Resource Name (ARN) of the Aurora Serverless DB cluster.Defined in constructor.
resultSetOptionsOptions that control how the result set is returned.Defined in constructor.
schemaThe name of the database schema.Defined in constructor.
secretArnThe name or ARN of the secret that enables access to the DB cluster.Defined in constructor.
transactionIdThe 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
valueTransformerSee 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

NameDescriptionRequired
sqlThe SQL query string to perform.Yes
paramsAn array of Query Params. Maximum of 1,000.No
optionsSee options below.No

Options

NameDescriptionDefault
databaseThe name of the database.Defined in constructor.
resourceArnThe Amazon Resource Name (ARN) of the Aurora Serverless DB cluster.Defined in constructor.
schemaThe name of the database schema.Defined in constructor.
secretArnThe name or ARN of the secret that enables access to the DB cluster.Defined in constructor.
transactionIdThe 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:

Arguments

NameDescriptionRequired
optionsSee options below.No

Options

NameDescriptionDefault
databaseThe name of the database.Defined in constructor.
resourceArnThe Amazon Resource Name (ARN) of the Aurora Serverless DB cluster.Defined in constructor.
schemaThe name of the database schema.Defined in constructor.
secretArnThe 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

NameDescriptionRequired
transactionIdThe identifier of the transaction to end and commit.Yes
optionsSee options below.No

Options

NameDescriptionDefault
resourceArnThe Amazon Resource Name (ARN) of the Aurora Serverless DB cluster.Defined in constructor.
secretArnThe 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

NameDescriptionRequired
transactionIdThe identifier of the transaction to roll back.Yes
optionsSee options below.No

Options

NameDescriptionDefault
resourceArnThe Amazon Resource Name (ARN) of the Aurora Serverless DB cluster.Defined in constructor.
secretArnThe 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:

Arguments

NameDescriptionRequired
optionsSee options below.Yes

Options

NameDescriptionDefault
sql (Required)The SQL statement to run.undefined
includeResultMetadataIncludes the column metadata.undefined
continueAfterTimeoutA 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.
databaseThe name of the database.Defined in constructor.
resourceArnThe Amazon Resource Name (ARN) of the Aurora Serverless DB cluster.Defined in constructor.
resultSetOptionsOptions that control how the result set is returned.Defined in constructor.
schemaThe name of the database schema.Defined in constructor.
secretArnThe name or ARN of the secret that enables access to the DB cluster.Defined in constructor.
transactionIdThe 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:

Arguments

NameDescriptionRequired
optionsSee options below.Yes

Options

NameDescriptionDefault
sql (Required)The SQL statement to run.undefined
parameterSetsThe parameter set for the batch operation. The maximum number of parameters in a parameter set is 1,000.undefined
databaseThe name of the database.Defined in constructor.
resourceArnThe Amazon Resource Name (ARN) of the Aurora Serverless DB cluster.Defined in constructor.
schemaThe name of the database schema.Defined in constructor.
secretArnThe name or ARN of the secret that enables access to the DB cluster.Defined in constructor.
transactionIdThe 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:

NameDescription
queryPerforms an SQL query in the transaction.
batchQueryPerforms an SQL query over an array of data in the transaction.
commitCommits and ends the transaction.
rollbackRolls-back and ends the transaction.
executeStatementAbstraction of the RDSDataService#executeStatement method in context of the transaction.
batchExecuteStatementAbstraction 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

NameDescriptionRequired
sqlThe SQL statement string to perform.Yes
paramsSee Query Params.No
optionsSee options below.No

Options

NameDescriptionDefault
includeResultMetadataA value that indicates whether to include metadata in the results. Note: must be true for the results to be transformed.Defined in #beginTransaction.
continueAfterTimeoutA 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.
resultSetOptionsOptions that control how the result set is returned.Defined in #beginTransaction.
valueTransformerSee 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

NameDescriptionRequired
sqlThe SQL query string to perform.Yes
paramsAn 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

NameDescriptionRequired
optionsSee options below.Yes

Options

NameDescriptionDefault
sql (Required)The SQL statement to run.undefined
includeResultMetadataA value that indicates whether to include metadata in the results.Defined in #beginTransaction.
continueAfterTimeoutA 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.
resultSetOptionsOptions 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

NameDescriptionRequired
optionsSee options below.Yes

Options

NameDescriptionDefault
sql (Required)The SQL statement to run.undefined
parameterSetsThe 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 TypeTransformed 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.