Stack conventions

SQL databases (migrations, procedures, views)

Relational database code for PostgreSQL, SQL Server (T-SQL), MySQL and Oracle: versioned schema migrations (Flyway V__ files, Liquibase changelogs, plain numbered scripts), stored procedures, functions, views and grants.

Relational database code for PostgreSQL, SQL Server (T-SQL), MySQL and Oracle: versioned schema migrations (Flyway V__ files, Liquibase changelogs, plain numbered scripts), stored procedures, functions, views and grants.

Used for: sql.

Principles

• Change a schema only through a new versioned migration (V__.sql, a new Liquibase changeSet); never edit or rename a migration that is committed, because every database that ran it fails checksum validation.
• Write every migration for the live database: expand, migrate, contract - add nullable columns or new tables first, backfill in batches, switch readers, and drop the old shape in a later release.
• Keep the repository's dialect and tool (Flyway, Liquibase, golang-migrate, sqlpackage, plain scripts) and its sqlfluff configuration; the dialect is set once in .sqlfluff.
• Never build SQL by concatenating input: bind values (prepared statements, sp_executesql parameters, EXECUTE ... USING, :bind variables) and quote identifiers that must be dynamic (quote_ident/format %I, QUOTENAME, DBMS_ASSERT).
• Grant least privilege: named roles get only the objects and actions they use, applications never connect as the owner or an admin, and nothing is granted TO PUBLIC.
• Lock-aware DDL: on large or busy tables use the dialect's online form (CREATE INDEX CONCURRENTLY, ONLINE = ON, ALGORITHM=INPLACE, Oracle ONLINE) and set lock and statement timeouts in migrations.
• Every change ships with a test where the database allows one (pgTAP, tSQLt, utPLSQL), run in CI against a disposable database built from the migrations.
• Keep credentials out of SQL, migration configuration and logs: connection strings and passwords come from the CI system's secret store or environment.
• Make repeatable objects (views, functions, procedures) idempotent - CREATE OR REPLACE / CREATE OR ALTER in R__ (repeatable) scripts - and versioned changes run once.
• Prefer set-based statements to row-by-row loops and cursors; batch large data changes to bound locks and transaction log growth.

Conventions

Accessibility

Concurrency

• Know the lock each DDL statement takes (ACCESS EXCLUSIVE in PostgreSQL for most ALTER TABLE) and keep it short: no long-running data change in the same transaction as DDL.
• Use the isolation level the logic needs and handle deadlocks and serialization failures with a bounded retry in the caller; take locks in a consistent order.
• Run migrations from one place at a time (the migration tool's lock or one pipeline concurrency group per database).

Dependencies

• Pin the migration tool, database engine image and test framework versions (Flyway, Liquibase, pgTAP, tSQLt) used in CI to match production's major version.
• Keep extensions explicit (CREATE EXTENSION IF NOT EXISTS ... in a migration, with the version) and check that the managed database service supports them.

Documentation

• Every migration starts with a comment saying why the change is made and whether it is safe to run on a live database (expected locks, duration, backfill steps).
• Document tables and columns in the database (COMMENT ON, extended properties) and keep the schema diagram or data dictionary in the repository up to date.
• Record the dialect, migration tool, how to build a local database and how to run the database tests in the README.

Errors

• Make migrations transactional where the dialect allows DDL in a transaction (PostgreSQL, SQL Server); where it does not (MySQL, Oracle DDL auto-commits) keep one DDL statement per migration so a failure leaves a known state.
• Raise errors with context and the dialect's mechanism (RAISE EXCEPTION ... USING ERRCODE, THROW 50001, RAISE_APPLICATION_ERROR(-20001, ...)); never swallow an error in an exception handler that only logs.
• In T-SQL procedures SET XACT_ABORT ON and use TRY...CATCH with ROLLBACK and THROW, so a failed statement never leaves an open transaction.
• Set lock_timeout / statement_timeout (PostgreSQL), LOCK_TIMEOUT (SQL Server) or DDL_LOCK_TIMEOUT (Oracle) in migrations that touch busy tables, so a blocked migration fails fast instead of queueing every query behind it.

Logging

• Record what a long-running procedure or backfill did (rows affected, duration, batch) in an audit or job-log table or with RAISE NOTICE / PRINT for the operator; never log row data that holds personal data.
• Rely on the migration tool's history table (flyway_schema_history, DATABASECHANGELOG) as the record of what ran; never edit it by hand outside a documented repair.
• Monitor slow queries and lock waits after a migration ships (pg_stat_statements, Query Store, Performance Schema, AWR) and keep the plan of a query the change affects.

Naming

• Use snake_case for tables, columns, views and functions in PostgreSQL, MySQL and Oracle (unquoted identifiers); follow the repository's existing case (PascalCase is common in SQL Server). Never rely on quoted mixed-case identifiers.
• Name tables for what one row is, consistently singular or plural as the schema already does; name keys and constraints explicitly (pk_orders, fk_order_lines_orders, ix_orders_customer_id, uq_..., ck_...) so migrations can refer to them.
• Name migrations V__.sql with a description in the imperative (V12__add_status_to_orders.sql); Flyway needs the capital V and two underscores, or the file is silently skipped.
• Keep keywords in one case throughout the repository (the sqlfluff capitalisation rules enforce the configured one).

Performance

• Index for the queries the change adds (foreign keys, WHERE and JOIN columns), check the plan (EXPLAIN, actual execution plans), and drop indexes no query uses.
• Create indexes on large live tables online (CREATE INDEX CONCURRENTLY outside a transaction, WITH (ONLINE = ON), ALGORITHM=INPLACE, LOCK=NONE) and add NOT NULL or foreign keys in two steps (NOT VALID then VALIDATE) where the dialect supports it.
• Avoid row-by-row cursors and scalar functions in hot queries; write set-based statements and keep SARGable predicates (no functions on indexed columns).
• Partition very large tables by time or tenant where the dialect supports it, and keep statistics current after large loads.

Security

• Parameterise every value: bind variables, EXECUTE ... USING, sp_executesql with a parameter list; dynamic identifiers only through quote_ident / format('%I') / QUOTENAME / DBMS_ASSERT. Concatenated input is SQL injection.
• Validate inputs at the procedure boundary (types, ranges, allow-lists for sort columns and identifiers) and declare constraints (NOT NULL, CHECK, FOREIGN KEY) so invalid data cannot be stored.
• Keep secrets out of SQL and migration configuration: no passwords in CREATE USER scripts, flyway.conf or liquibase.properties; they come from the secret store at deploy time (FLYWAY_PASSWORD, environment variables).
• Least privilege: no GRANT ... TO PUBLIC, no GRANT ALL, no application login as owner, sa, root or SYSTEM; SECURITY DEFINER functions set search_path and are owned by a role with only the rights they need.
• Protect personal data: encryption at rest is the platform's, but column-level masking, row-level security (PostgreSQL RLS, SQL Server RLS, Oracle VPD) and views that hide sensitive columns are the schema's job.
• Pin the migration tool and its JDBC driver versions in the build (Flyway image tag, Liquibase version, driver jars) and audit them like any dependency.

Structure

• Keep migrations in the tool's location (db/migration, src/main/resources/db/migration, migrations/, a Liquibase master changelog that includes the others) and repeatable object definitions in R__ scripts or one file per object.
• One logical change per migration file; DDL and large data changes in separate migrations so a failure is easy to locate and re-run.
• Keep test code (pgTAP, tSQLt classes, utPLSQL packages) in its own folder (tests/, db/tests) that is never deployed to production.
• Configure sqlfluff in .sqlfluff at the repository root: dialect, templater (placeholder for Flyway ${placeholders}), excluded rules with a reason, and .sqlfluffignore for vendored or generated SQL.

Testing

• Unit-test procedures, functions, views and constraints with the dialect's framework (pgTAP, tSQLt, utPLSQL) or the application's integration tests; each test sets up its own data in a transaction that is rolled back.
• Integration-test every migration in CI: build a disposable database from all migrations (a container, a throw-away schema), then run the database tests; also test the upgrade from the previous release's schema.
• Name tests after the behaviour they check (test_order_total_includes_tax, [OrderTests].[test refund cannot exceed payment]).
• Done means tested: new or changed behaviour ships with tests in the same change, a fixed bug gets a regression test, and the migration check (names, versions, order) passes.
• Database tests need a running database: where none is available they are reported as not run, with the reason, never as passed.

Review checklist

• Is the schema change a new versioned migration, with no committed migration edited or renamed?
• Is the migration safe on a live database (online DDL, lock timeout, no long transaction, expand and contract where readers exist)?
• Are all values bound as parameters and dynamic identifiers quoted, with no concatenated input?
• Are grants least privilege (named roles, nothing to PUBLIC, no GRANT ALL, no owner or admin login)?
• Are constraints (NOT NULL, CHECK, FOREIGN KEY, UNIQUE) declared for the rules the data must follow?
• Do new queries have supporting indexes, and was the plan checked for the hot ones?
• Is a large data change batched and resumable?
• Do procedures handle errors with the dialect's mechanism and never leave an open transaction?
• Are there database tests (pgTAP, tSQLt, utPLSQL) for new or changed procedures, functions and constraints?
• Does the migration check pass (names, unique versions, nothing older than the newest committed version)?
• Is sqlfluff clean for the configured dialect?
• Are credentials kept out of SQL and migration configuration?

Quality checks (run when your repository has the tool configured)