Drizzle and Effect 4

backend/src/db/schemas/person.ts declares demo.person: a UUID primary key, name, and unique email. schemas/namespace.ts owns the namespace and schemas/index.ts exports both definitions for Drizzle Kit. The separate demo namespace keeps the example apart from former application tables in public.

Automatic schema push

Every backend start runs drizzle-kit push before listening. Edit the Drizzle schema and restart the backend; there are no generated SQL files or migration folders to manage. Startup fails if schema push fails. Destructive changes are not silently approved with --force.

backend/src/db/seed.ts contains idempotent demo seeding after schema push. Three fictional people are inserted with stable IDs; conflicts are ignored so existing edits survive. Production seeding is off by default.

Native Effect queries

Database uses drizzle-orm/effect-postgres and @effect/sql-pg. Queries are Effects, not Promise queries wrapped in Effect. One scoped layer owns the pool and releases it on shutdown. GraphQL propagates request cancellation.

const people = Effect.gen(function* () {
  const db = yield* Database;
  return yield* db.select().from(person).orderBy(person.name, person.id).limit(100);
});

Ordinary statements use PostgreSQL autocommit. Explicit db.transaction(...) remains available when multiple statements must succeed or fail together.

Configuration and secrets

make setup, make dev, and first worktree startup generate a cryptographically random database password if necessary. secrets/ is private and excluded from Git and Docker builds. Subsequent starts preserve it; missing or changed existing credentials are rejected rather than silently rotating access to persisted data.

Compose passes the generated file to PostgreSQL via POSTGRES_PASSWORD_FILE and to the backend via PGPASSWORD_FILE. Host, port, username, and database are wired automatically. You never construct a database URL or put a password in .env. Compose loads optional external API keys from .env only into the backend.

Terraform generates the production password with random_password, supplies the private database endpoint and trusted CA, and injects the password through Scaleway secret environment variables. Production needs no local .env file. Review a Terraform plan before applying changes to existing infrastructure; adopting the generated password rotates the previous database credential.

Versions

Drizzle ORM/Kit are pinned to 1.0.0-rc.5-5935859 and Effect packages to 4.0.0-rc.112. This Drizzle candidate supports Effect's current Schema.TaggedError API without a compatibility patch or Effect 3 adapter.