# DSQL Examples: Application Patterns

Part of [Aurora DSQL Implementation Examples](../dsql-examples.md).

> **`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](../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.

```javascript
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).

```javascript
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.

```sql
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:

```sql
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:

```sql
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](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-supported-data-types.html), cast to JSON at query time.

```javascript
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:

```sql
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;
```