with one click
with one click
[HINT] Download the complete skill directory including SKILL.md and all related files
| name | postgresql |
| description | Rules when working with PostgreSQL database in Gram |
Comprehensive guidelines when working with PostgreSQL database to build Gram which include rules for schema design, database migration and application logic. All rules are kept in a rules folder with names of each rule outlined below (e.g. rules/<rule-name>.md).
Reference these guidelines when:
Code Formatting and Comments:
pgformatter or similar.-- a comment); do not add a space for commented-out code (--raise notice).Naming Conventions:
snake_case for identifiers (e.g., user_id, customer_name).customers, products).Data Integrity and Data Types:
INTEGER, VARCHAR, TIMESTAMP).NOT NULL, UNIQUE, CHECK, FOREIGN KEY) to enforce data integrity.ON DELETE SET NULL clause.Indexing:
WHERE clauses and JOIN conditions.B-tree, Hash, GIN, GiST) based on the data and query requirements.Schema evolution:
mise db:diff <migration-name> after making schema changes to generate a migration file. Replace <migration-name> with a clear snake-case migration id such as users-add-email-column.mise run db:reset 2. mise run db:migrate to re-run all migrations from the beginning.mise run db:diff <name-of-migrations>: Create a database migrationmise run db:reset: Drop the database and re-create it. No migrations applied at this point.mise run db:migrate: Run all pending database migrations. If you have just reset the database, this will run all migrations from the beginning.When creating any tables, add a non-nullable column named project_id of type uuid with a foreign key constraint to the projects table. If appropriate to the nature and usage patterns of the table also include organization_id TEXT NOT NULL column.
All tables should have created_at and updated_at columns:
create table if not exists example (
-- ...
created_at timestamptz not null default clock_timestamp(),
updated_at timestamptz not null default clock_timestamp() on update clock_timestamp(),
-- ...
);
A nullable deleted_at column may be added to tables to perform soft deletes:
create table if not exists example (
-- ...
deleted_at timestamptz,
deleted boolean not null generated always as (deleted_at is not null) stored,
-- ...
);
Deleting rows with DELETE FROM table is not strongly discouraged. Instead,
use:
UPDATE example SET deleted_at = clock_timestamp() WHERE id = ?;
server/database/schema.sql is DDL only — no DO, ALTER, or other procedural blocks. Declare tables in dependency order so every FOREIGN KEY resolves inline; if a target is declared later, move it up.
All constraints should be named with this format:
{tablename}_{columnname(s)}_{suffix}
Where suffix is:
key for a unique constraintfkey for a foreign key constraintidx for any other kind of indexcheck for a check constraintexcl for an exclusion constraintseq for an sequencesEnsure that all schema changes are designed for backwards compatibility.
These are examples of terrible practices to avoid:
Instead, strongly consider these better alternatives:
All SQLc queries live in **/queries.sql files in the codebase. This is an important convention to maintain.
When writing SQLc queries, follow these guidelines:
project_id to explicitly limit the scope of writes.mise run infra:start: bring up the local Postgres/ClickHouse/etc containers — required before running sqlc, since sqlc connects to the database to type-check queries.mise run gen:sqlc-server: generates Go code from SQLc queries (requires the local database from mise run infra:start).