DSQL Examples: Schema Design
Part of Aurora DSQL Implementation Examples.
Schema Design: Table Creation
SHOULD use UUIDs with gen_random_uuid() for distributed write performance. Source: Adapted from the Liquibase migration sample listed at the Aurora DSQL connectivity tools page
CREATE TABLE IF NOT EXISTS owner (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(30) NOT NULL,
city VARCHAR(80) NOT NULL,
telephone VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS orders (
order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id VARCHAR(255) NOT NULL,
status VARCHAR(50) NOT NULL,
tags TEXT,
metadata TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Schema Design: Index Creation
MUST use CREATE INDEX ASYNC (defaults: max 24 indexes per table, 8 columns per index — verify via the AWS MCP Server's aws___search_documentation if available, or the DSQL documentation: aurora dsql index limits). Source: Adapted from the Liquibase migration sample listed at the Aurora DSQL connectivity tools page
CREATE INDEX ASYNC idx_owner_city ON owner(city);
CREATE INDEX ASYNC idx_orders_tenant ON orders(tenant_id);
CREATE INDEX ASYNC idx_orders_status ON orders(tenant_id, status);Schema Design: Column Modifications
MUST use two-step process: add column, then UPDATE for defaults (ALTER COLUMN not supported).
ALTER TABLE orders ADD COLUMN priority INTEGER;
UPDATE orders SET priority = 0 WHERE priority IS NULL;