typed data layer

the SaaS spine: a typed schema, safe queries, and migrations, without losing tenant isolation. uses drizzle-orm to enforce type safety and database-level access control.

intent

take a builder from "i need a database" to a typed schema with migrations and a tenant-isolation pattern that actually holds. the failure mode this prevents: an orm-only setup that filters by tenant in app code, which one forgotten where-clause turns into a cross-tenant data leak. use this skill when setting up a new postgres database for a multi-tenant app, migrating to typed queries, or hardening an existing data layer against isolation bugs.

inputs

• postgres database running (version 12+)
• DATABASE_URL env var with postgres connection string (format: postgres://user:password@host:port/dbname)
• node.js runtime with drizzle-orm and drizzle-kit installed
• drizzle.config.ts at project root pointing to your schema files
• for RLS policies, postgres role with DDL permissions on the target schema

procedure

1. install drizzle and set up the config file

   • input: package.json, DATABASE_URL env var
   • run npm install drizzle-orm drizzle-kit pg
   • create drizzle.config.ts at the root
   • output: drizzle.config.ts pointing to ./src/db/schema.ts

2. define the schema with typed columns

   • input: business domain (orgs, users, permissions, etc.)
   • create src/db/schema.ts with pgTable calls, specify column types (uuid, text, timestamp, integer, etc.), mark constraints (primaryKey, notNull, unique, references)
   • output: schema.ts file with typed table definitions

3. set up the drizzle client

   • input: schema.ts, DATABASE_URL, postgres pool or client
   • create src/db/client.ts that imports drizzle and instantiates it with your pool
   • export the db instance for queries elsewhere
   • output: client.ts with export const db = drizzle(pool)

4. generate and review the initial migration

   • input: schema.ts, DATABASE_URL
   • run drizzle-kit generate:pg
   • output: drizzle/migrations/{timestamp}_init.sql file showing all CREATE TABLE statements
   • review the SQL for correctness before applying

5. apply migrations to postgres

   • input: drizzle/migrations folder, DATABASE_URL
   • run drizzle-kit migrate or execute migration files manually via psql
   • output: tables created in postgres, drizzle_migrations table tracking applied migrations

6. write typed queries using the drizzle API

   • input: db client, schema definitions
   • use db.select().from(table).where(...) syntax, types are inferred from schema
   • output: type-safe query results with autocomplete and compile-time validation

7. enable postgres RLS (for multi-tenant)

   • input: schema, tenant identifier column (e.g., org_id), postgres role
   • for each sensitive table, run ALTER TABLE table_name ENABLE ROW LEVEL SECURITY
   • create a policy that filters by current_setting('app.current_tenant_id')
   • output: RLS policies enforced at the database layer, isolation independent of app code

8. set the tenant context before queries (for multi-tenant)

   • input: authenticated user, their org_id, db client
   • before executing queries, run db.execute(sqlSET app.current_tenant_id = ${org_id}`)
   • output: postgres session variable set, subsequent queries respect the RLS policy

decision points

• migrations: generated or hand-written? always generate migrations from schema.ts using drizzle-kit, commit the SQL to the repo, never hand-edit the database directly. if you need a custom migration (e.g., data transformation), create a separate .sql file in drizzle/migrations and mark it as manual.

• RLS enforcement: mandatory or optional? for single-tenant apps, RLS is optional. for multi-tenant, RLS is mandatory, the app-layer tenant filter is a second line of defense not the first. if you skip RLS, a bug in app code becomes a data leak.

• connection pooling: local dev or production? in dev you can use a direct postgres connection. in production or serverless, use a connection pooler (pgBouncer, neon, supabase) or you exhaust connections and queries hang or fail.

• schema changes during development: how to iterate? use drizzle-kit drop to nuke migrations and the schema, then regenerate from your schema.ts. do this only in dev, never in production. for production, always generate a migration, review it, and apply it in order.

• empty result sets: how to handle? drizzle returns an empty array, not null, so check rows.length === 0 or use optional chaining. if you expect a single row, use db.query.table.findFirst() with proper error handling for not-found cases.

• network timeouts or auth failures: catch db query errors in your application layer, log them, and fail gracefully. if DATABASE_URL is missing or invalid, the drizzle client will error at runtime, not at schema definition time. test your connection on startup.

output contract

success means:

• schema.ts with typed table definitions, all columns have explicit types
• drizzle/migrations folder with generated SQL, one migration file per schema change
• db client instantiated and exported, queries use the drizzle API and have inferred types
• for multi-tenant apps, postgres RLS policies on sensitive tables, one policy per table enforcing tenant isolation
• migrations tracked in postgres drizzle_migrations table, no manual DDL outside migrations

outcome signal

you know it worked when:

• a query with no explicit where-clause on a multi-tenant table returns zero rows for a non-owning tenant, because RLS blocks it at the database
• running drizzle-kit generate:pg shows "no changes" because schema and database are in sync
• typescript compilation catches a typo in a column name before runtime
• a new developer can run drizzle-kit migrate and get a production-identical database in seconds
• you can roll back a schema change by reverting a migration file and re-running drizzle-kit migrate

original author: implexa