/aurora-dsql

DSQL Examples: Application Patterns

Part of Aurora DSQL Implementation Examples.

pool in every example MUST be a DSQL Connector pool, not a bare driver pool. Construct it via new AuroraDSQLPool(...) from @aws/aurora-dsql-node-postgres-connector (or the equivalent for your language — see language.md). Bare pg.Pool / psycopg.connection / pgx.Pool works until the first 15-minute token expiry and then starts returning auth errors on every new connection — DSQL users who try the bare form report this as a DSQL bug. Workflow 0b in SKILL.md covers Connector verification.

Multi-Tenant Isolation

ALWAYS include tenant_id in WHERE clauses; tenant_id is always first parameter.

async function getOrders(pool, tenantId, status) {
  const result = await pool.query(
    'SELECT * FROM orders WHERE tenant_id = $1 AND status = $2',
    [tenantId, status]
  );
  return result.rows;
}

async function deleteOrder(pool, tenantId, orderId) {
  const check = await pool.query(
    'SELECT order_id FROM orders WHERE tenant_id = $1 AND order_id = $2',
    [tenantId, orderId]
  );

  if (check.rows.length === 0) {
    throw new Error('Order not found or access denied');
  }

  await pool.query(
    'DELETE FROM orders WHERE tenant_id = $1 AND order_id = $2',
    [tenantId, orderId]
  );
}

Application-Layer Referential Integrity

SHOULD validate references for custom business rules (DSQL provides database-level integrity).

async function createLineItem(pool, tenantId, lineItemData) {
  const orderCheck = await pool.query(
    'SELECT order_id FROM orders WHERE tenant_id = $1 AND order_id = $2',
    [tenantId, lineItemData.order_id]
  );

  if (orderCheck.rows.length === 0) {
    throw new Error('Order does not exist');
  }

  await pool.query(
    'INSERT INTO line_items (tenant_id, order_id, product_id, quantity) VALUES ($1, $2, $3, $4)',
    [tenantId, lineItemData.order_id, lineItemData.product_id, lineItemData.quantity]
  );
}

async function deleteProduct(pool, tenantId, productId) {
  const check = await pool.query(
    'SELECT COUNT(*) as count FROM line_items WHERE tenant_id = $1 AND product_id = $2',
    [tenantId, productId]
  );

  if (parseInt(check.rows[0].count) > 0) {
    throw new Error('Product has existing orders');
  }

  await pool.query(
    'DELETE FROM products WHERE tenant_id = $1 AND product_id = $2',
    [tenantId, productId]
  );
}

Sequences and Identity Columns

Sequences and IDENTITY columns generate integer values and are useful when compact or human-readable identifiers are needed.

Identity Columns

An identity column is a special column generated automatically from an implicit sequence. Use the GENERATED ... AS IDENTITY clause in CREATE TABLE. CACHE must be specified explicitly as either 1 or >= 65536.

CREATE TABLE people (
    id BIGINT GENERATED ALWAYS AS IDENTITY (CACHE 70000) PRIMARY KEY,
    name VARCHAR(255),
    address TEXT
);

-- Or with BY DEFAULT, which allows explicit value overrides
CREATE TABLE orders (
    order_number BIGINT GENERATED BY DEFAULT AS IDENTITY (CACHE 70000) PRIMARY KEY,
    tenant_id VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL
);

Inserting rows without specifying the identity column generates values automatically:

INSERT INTO people (name, address) VALUES ('A', 'foo');
INSERT INTO people (name, address) VALUES ('B', 'bar');

-- Use DEFAULT to explicitly request the generated value
INSERT INTO people (id, name, address) VALUES (DEFAULT, 'C', 'baz');

Standalone Sequences

Use CREATE SEQUENCE when you need a sequence independent of a specific table column:

CREATE SEQUENCE order_seq CACHE 1 START 101;

SELECT nextval('order_seq');
-- Returns: 101

INSERT INTO distributors VALUES (nextval('order_seq'), 'nothing');

Choosing a CACHE Size

• CACHE >= 65536 — high-frequency identifier generation, many concurrent sessions, tolerates gaps (e.g., IoT ingestion, job run IDs)
• CACHE = 1 — low allocation rates, identifiers should follow allocation order more closely, minimizing gaps matters (e.g., account numbers, reference numbers)

Data Serialization

Pattern: MUST store arrays and JSON as TEXT (runtime-only types). Per DSQL docs, cast to JSON at query time.

function toTextArray(values) {
  return values.join(',');
}

function fromTextArray(textValue) {
  return textValue ? textValue.split(',').map(v => v.trim()) : [];
}

function toTextJSON(object) {
  return JSON.stringify(object);
}

function fromTextJSON(textValue) {
  if (!textValue) return null;
  try {
    return JSON.parse(textValue);
  } catch (err) {
    console.warn('Invalid JSON in column:', err.message);
    return null;
  }
}

const categoriesText = toTextArray(['backend', 'api', 'database']);
await pool.query('INSERT INTO projects (project_id, categories) VALUES ($1, $2)', [projectId, categoriesText]);

const configText = toTextJSON({ theme: 'dark', notifications: true });
await pool.query('INSERT INTO user_settings (user_id, preferences) VALUES ($1, $2)', [userId, configText]);

Query-time operations:

SELECT user_id, preferences::jsonb->>'theme' as theme
FROM user_settings WHERE preferences::jsonb->>'notifications' = 'true';

SELECT project_id, string_to_array(categories, ',') as category_array FROM projects;