# DSQL Examples: Application Patterns

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

---

## 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:** Arrays must be serialized into a single-column representation. PREFER `JSONB` for queryable arrays; MAY use `TEXT` when opaque to the database. For document columns, choose `JSONB` (queryable with `@>`/`?`/indexed paths) or `JSON` (write-heavy or byte-exact). Per [DSQL docs](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-supported-data-types.html).

**JSONB (write + query with operators):**

```javascript
const categories = ['backend', 'api', 'database'];
await pool.query(
  'INSERT INTO projects (project_id, categories) VALUES ($1, $2::jsonb)',
  [projectId, JSON.stringify(categories)]
);

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

```sql
-- JSONB-only operators (containment, key existence, indexed paths):
SELECT user_id FROM user_settings WHERE preferences @> '{"theme":"dark"}';
SELECT project_id, jsonb_array_elements_text(categories) AS category FROM projects;

-- ->/->> work on both JSON and JSONB:
SELECT user_id, preferences->>'theme' AS theme
FROM user_settings WHERE preferences->>'notifications' = 'true';
```

**JSON (write-heavy, byte-exact, key-extraction only):**

```javascript
const auditPayload = { event: 'login', ts: 1717890000, user_id: '...' };
await pool.query(
  'INSERT INTO audit_log (id, payload) VALUES ($1, $2)',  // no cast: column is JSON
  [eventId, JSON.stringify(auditPayload)]
);
```

```sql
SELECT id, payload->>'event' AS event FROM audit_log WHERE payload->>'user_id' = $1;
```

**TEXT (opaque to the database):**

```javascript
const tagsCsv = ['backend', 'api', 'database'].join(',');
await pool.query(
  'INSERT INTO projects (project_id, tags_csv) VALUES ($1, $2)',
  [projectId, tagsCsv]
);
// Application parses tags_csv.split(',') on read; the database never inspects it.
```
