sequelize

Aurora DSQL with Sequelize

Overview

This code example demonstrates how to use Sequelize with Amazon Aurora DSQL. The example shows you how to connect to an Aurora DSQL cluster with Sequelize using node-postgres, create entities, and read and write to those entity tables.

Aurora DSQL is a distributed SQL database service that provides high availability and scalability for your PostgreSQL-compatible applications. Sequelize is a popular object-relational mapping framework for TypeScript that allows you to persist TypeScript objects to a database while abstracting the database interactions.

About the code example

The example demonstrates a flexible connection approach that works for both admin and non-admin users:

• When connecting as an admin user, the example uses the public schema and generates an admin authentication token.
• When connecting as a non-admin user, the example uses a custom myschema schema and generates a standard authentication token.

The code automatically detects the user type and adjusts its behavior accordingly.

⚠️ Important

• Running this code might result in charges to your AWS account.
• We recommend that you grant your code least privilege. At most, grant only the minimum permissions required to perform the task. For more information, see Grant least privilege.
• This code is not tested in every AWS Region. For more information, see AWS Regional Services.

Run the example

Prerequisites

• You must have an AWS account, and have your default credentials and AWS Region configured as described in the Globally configuring AWS SDKs and tools guide.
• TypeScript: Ensure you have TypeScript 5.6+ installed

npx tsc --version

It should output something similar to Version 5.6.x or higher.

• You must have an Aurora DSQL cluster. For information about creating an Aurora DSQL cluster, see the Getting started with Aurora DSQL guide.
• If connecting as a non-admin user, ensure the user is linked to an IAM role and is granted access to the myschema schema. See the Using database roles with IAM roles guide.

Run the code

The example demonstrates the following operations:

• Opening a connection pool to an Aurora DSQL cluster using Sequelize
• Creating several Sequelize models
• Creating and querying objects that are persisted in DSQL

The example is designed to work with both admin and non-admin users:

• When run as an admin user, it uses the public schema
• When run as a non-admin user, it uses the myschema schema

Note: running the example will use actual resources in your AWS account and may incur charges.

Set environment variables for your cluster details:

# e.g. "admin"
export CLUSTER_USER="<your user>"

# e.g. "foo0bar1baz2quux3quuux4.dsql.us-east-1.on.aws"
export CLUSTER_ENDPOINT="<your endpoint>"

Run the example:

npm install
npm run build
npm run start

The example contains comments explaining the code and the operations being performed.

Usage notes

Connecting to DSQL

DSQL is PostgreSQL-compatible, so use the postgres dialect. The Aurora DSQL Connector for node-postgres handles IAM token generation automatically. Inject the connector into Sequelize via the dialectModule option.

import { AuroraDSQLClient } from '@aws/aurora-dsql-node-postgres-connector';
import * as pg from 'pg';

const sequelize = new Sequelize({
  host: clusterEndpoint,
  username: user,
  database: 'postgres',
  dialect: 'postgres',
  dialectModule: { ...pg, Client: AuroraDSQLClient },
  // ...
});

For non-admin users, set the search path to their granted schema using an afterConnect hook. Non-admin users cannot be granted access to the public schema.

hooks: {
  afterConnect: async (connection) => {
    await connection.query('SET search_path TO myschema');
  }
}

Connection configuration

Sequelize sets client_min_messages by default, which is not compatible with Aurora DSQL. Disable this by setting clientMinMessages: 'ignore' in dialect options.

new Sequelize({
  // ...
  dialect: 'postgres',
  dialectOptions: {
    clientMinMessages: 'ignore',
  },
});

Connection pooling

Connection pooling can be configured in the Sequelize constructor. The DSQL connector generates a new authentication token for each connection. DSQL connections close after one hour; the pool automatically opens new connections as needed.

new Sequelize({
  // ...
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  },
});

Table creation

Sequelize.sync() and Model.sync() are not compatible with Aurora DSQL because DSQL returns index metadata in a format that Sequelize v6 cannot parse (the INCLUDE clause in index definitions causes parsing failures). Use QueryInterface.createTable() to create tables, then initialize models in memory with Model.init().

// Instead of: await Model.sync();
const queryInterface = sequelize.getQueryInterface();
await queryInterface.createTable('owner', {
  id: { type: DataTypes.UUID, primaryKey: true, defaultValue: DataTypes.UUIDV4 },
  name: { type: DataTypes.STRING(30), allowNull: false },
});

// Then initialize the model in memory
Owner.init({ /* same attributes */ }, { sequelize, tableName: 'owner' });

Primary keys

Use UUID primary keys with Aurora DSQL:

id: { type: DataTypes.UUID, primaryKey: true, defaultValue: DataTypes.UUIDV4 }

Relationships

When defining relationships, set constraints: false. This uses application-layer referential integrity, with ORM-level relationships retained for queries.

Pet.belongsTo(Owner, { foreignKey: 'ownerId', constraints: false });
Owner.hasMany(Pet, { foreignKey: 'ownerId', constraints: false });

Data types

ENUM: Use DataTypes.STRING with validation in Model.init():

status: {
  type: DataTypes.STRING,
  validate: { isIn: [['pending', 'active', 'completed']] }
}

Methods

findOrCreate: Model.findOrCreate() internally uses PL/pgSQL. Use upsert or manual findOne + create instead.

// Option 1: upsert (overwrites name if record exists)
const [user] = await User.upsert({ email, name });

// Option 2: manual approach (preserves existing name)
let user = await User.findOne({ where: { email } });
if (!user) user = await User.create({ email, name });

truncate: Use destroy with empty where clause instead of Model.truncate().

// Instead of: await Model.truncate();
await Model.destroy({ where: {} });

For the full list of Aurora DSQL SQL compatibility details, see the PostgreSQL compatibility reference.

Locking

Aurora DSQL uses optimistic concurrency control (OCC), meaning transactions proceed without locks and conflicts are detected at commit time. The SELECT FOR UPDATE clause modifies this behavior by flagging read rows for concurrency checks, which is useful for managing write skew scenarios.

In Sequelize, only Transaction.LOCK.UPDATE is supported. The query must include an equality predicate on the primary key. Queries that lock by non-key columns will fail.

// Works: lock by primary key
await Model.findByPk(id, { lock: Transaction.LOCK.UPDATE, transaction });

// Does not work: lock by non-key column
await Model.findOne({ where: { status: 'pending' }, lock: Transaction.LOCK.UPDATE, transaction });

For more details on concurrency control in Aurora DSQL, see Concurrency control in Amazon Aurora DSQL.

Additional resources

• Amazon Aurora DSQL Documentation
• Aurora DSQL Connector for node-postgres
• Sequelize Documentation
• AWS SDK for JavaScript Documentation

---

Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.

SPDX-License-Identifier: MIT-0