rust-database

name	rust-database
description	Use when designing PostgreSQL schemas, writing SQLx migrations, choosing data types, naming tables/columns/indexes, or writing compile-time checked queries.

Database Standard

Tech Stack

• Database: PostgreSQL 15+
• Queries: SQLx (compile-time verification)
• Migrations: SQLx CLI

Naming Conventions

Object	Format	Example
Table name	plural, snake_case	orders, api_keys
Column name	snake_case	user_id, created_at
Index	idx_{table}_{columns}	idx_orders_user_id_status
Unique index	uniq_{table}_{columns}	uniq_api_keys_token_hash
Primary key constraint	pk_{table}	pk_orders
Check constraint	chk_{table}_{desc}	chk_orders_status

Design Principles

Foreign Key Constraints: An Architectural Decision

Foreign key constraints are not universally good or bad — the right choice depends on your architecture.

When to use FK constraints (recommended for single-database monoliths):

-- Enforces referential integrity at the database level
user_id UUID NOT NULL REFERENCES users(id)

Benefits:

• The database guarantees referential integrity — no orphaned rows
• Catches application bugs that would otherwise create inconsistent data
• Self-documents relationships in the schema
• Works well when all tables live in a single database

When to skip FK constraints (common in distributed / microservice architectures):

-- Application layer enforces referential integrity
user_id UUID NOT NULL

Benefits:

• No cross-service database coupling
• Avoids cascading lock contention on high-throughput writes
• Simplifies bulk data operations (imports, migrations, backfills)
• Easier to shard or split tables into separate databases later

Whichever approach you choose, be consistent across the project and document the decision.

Required Columns

Every table must include:

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Data Types

Purpose	Type
Primary key	UUID
Money / amounts	BIGINT (in cents)
Enums	VARCHAR(50)
Timestamps	TIMESTAMPTZ
Structured data	JSONB

Avoid: SERIAL, TIMESTAMP (without time zone), JSON (use JSONB), CHAR(n)

Migrations

sqlx migrate add <description>
sqlx migrate run

File naming: {timestamp}_{description}.sql

migrations/
├── 20240101000000_create_users.sql
├── 20240101000001_create_orders.sql
└── 20240102000000_create_order_items.sql

Principles:

• Forward-compatible (never break existing queries)
• Each migration is an atomic transaction
• Migration names are descriptive and clear

SQLx Usage

// Compile-time verified query
let order = sqlx::query_as!(
    Order,
    "SELECT id, user_id, status FROM orders WHERE id = $1",
    order_id
)
.fetch_optional(&pool)
.await?;

// Transaction
let mut tx = pool.begin().await?;
sqlx::query!("INSERT INTO orders ...").execute(&mut *tx).await?;
tx.commit().await?;

Checklist

• Table/column names use snake_case
• Every table has created_at / updated_at
• FK constraint decision is documented and applied consistently
• Money amounts use BIGINT (in cents)
• Timestamps use TIMESTAMPTZ