Moving Four Apps to Drizzle: Queries, Permissions, Migrations, and Performance

My portfolio, Studio, Admin, and Auth started with Supabase-backed data access. As the products grew, application behavior spread across route handlers, RPC calls, database functions, triggers, and a separate SQL migration history. A change to a workflow could require following several of those layers before I could explain what it did or who could run it.

I moved application queries and business transactions to Drizzle, then made it the owner of new migrations. Supabase still hosts PostgreSQL and provides Auth, Storage, and Realtime. The migration changed how the applications reach and evolve their data, while preserving their public API contracts and permissions.

Why I wanted one place to follow the logic

The four applications have different responsibilities. Portfolio reads published editorial content. Admin edits it and manages access. Auth owns account entry and security. Studio includes file management, games, and private workspaces.

The difficult part was not writing a simple SELECT. It was understanding a complete operation: which identity was trusted, which permission was checked, which records changed together, and which database routine had to be deployed before the application could work.

I wanted those business operations beside the product code, with a shared, typed schema and a migration path I could test. Drizzle gave me a TypeScript SQL layer without requiring me to hide PostgreSQL. I still use explicit SQL where locking, policy expressions, or a precise query shape make that clearer.

What Drizzle owns now

• Application-table queries across all four apps.
• Product-owned transactions for operations such as file metadata changes, game actions, CMS writes, and account provisioning.
• Maintained definitions for application tables, constraints, indexes, and row-level security policies.
• Generation and application of new migrations, including reviewed SQL for grants and platform policies.

Supabase still owns the hosted database, identity-provider operations, file objects, and Realtime subscriptions. A file upload illustrates the boundary: Drizzle handles application metadata and permissions; Supabase Storage handles the object. Those systems do not share one PostgreSQL transaction, so the file API still needs failure handling and compensation.

I removed custom application database functions and triggers. That does not mean removing Supabase-managed routines or replacing PostgreSQL's built-in functions. It also does not mean removing database authorization: RLS, native constraints, and caller-filtered permission views remain in the database.

The browser contract stayed the same

Browsers still use the existing API routes and Server Actions. Product modules own validation, response shapes, cache behavior, and authorization. A shared server-only database package owns connections, schema definitions, and transaction mechanics.

The migration did not turn Drizzle into a browser database client. Each app has its own restricted server credential. None of those application logins owns the tables or has BYPASSRLS privileges. The operator credential used for migrations is separate from runtime credentials.

For a private request, the server verifies the session token before opening the product transaction. Identity claims and the effective database role are scoped to that transaction. Ordinary user queries run under the authenticated role; selected backend commands use narrowly granted application roles with their own policies. Public Portfolio content reads run as anonymous even when an administrator visits the site.

That distinction matters: a successful login is not permission to edit content, manage another user's access, or change another player's game.

Moving a game action out of an RPC

A game action is more than an insert. The server needs to check that the actor belongs to an active session, that the move sequence is valid, and that any next-turn or winner references belong to the same game.

The Drizzle transaction locks the session row before reading and changing that state. It checks the actor and live capability, validates participation and sequence, writes the move, and updates the session and any result together. A failure rolls back the operation.

The important replacement for the RPC was therefore a transaction with the same invariants, not a series of independent queries. Moving code into TypeScript would have been a regression if two concurrent moves could both pass validation against the same old state.

Removing triggers required the same care. Validation and related writes moved into product transactions, while native database constraints retained the invariants they can enforce. Direct browser mutation grants were revoked where they would bypass the new transaction path.

Provisioning without a signup trigger

The old account-creation trigger also went away. A first verified application request now provisions a missing profile and Auth membership using conflict-safe inserts. It does not overwrite an existing profile or reactivate revoked access.

This is a deliberate behavior change at the provisioning boundary: an account created only in the Supabase dashboard remains unprovisioned until its first verified app request. Authentication still belongs to Supabase; application membership belongs to the application's data model.

Performance: the ORM was only one part

The migration happened alongside performance work, but I cannot honestly assign all of the improvement to Drizzle. I did not run a controlled benchmark that isolated the ORM from the other changes.

One concrete problem was geography. Admin functions were running in Vercel's US region while the database was in Mumbai. Repeated auth checks and database round trips made a single page wait on several cross-region requests. All four applications now target Vercel's Mumbai region, close to the database.

I also reduced repeated auth work and consolidated related database reads. For example, Portfolio's editorial loader assembles its public content through a single SQL statement with explicit selected fields. Request-scoped reuse avoids repeating work within a request without turning live permissions into an indefinitely cached decision.

The database adapter uses lazy, process-scoped pools bounded to one connection by default, with prepared statements disabled for transaction-pooler compatibility. TLS certificate and hostname verification stay enabled. Pooling limits connection pressure; it does not make an inefficient query fast.

The apps felt noticeably faster after the combined changes. I am not publishing a percentage because the evidence does not isolate each cause. The lesson I took from this work is to inspect the whole request path: authentication, query count, connection setup, network distance, and rendering. Changing an ORM alone does not remove those costs.

One migration queue, with the old history preserved

Drizzle now owns new migrations. I kept the old Supabase migration files as an immutable historical baseline instead of deleting them or replaying them through a second runner.

The workflow in this repository is:

# Edit the maintained schema, then generate and review SQL.
pnpm db:generate descriptive_change_name
pnpm db:schema:check

# Inspect local/remote history before applying the reviewed queue.
pnpm db:migrations:check
pnpm db:migrations:apply

These are repository scripts, not universal Drizzle commands. SQL-only changes use pnpm db:generate --custom descriptive_change_name and the same review and apply path.

The runner checks immutable migration hashes and ordering, takes a transaction advisory lock, and runs the Drizzle migrator inside the transaction. Failed batches roll back; concurrent applies serialize. Vercel builds do not apply database migrations, and application runtime credentials cannot run them.

Deployment order mattered during retirement: add the required permissions, deploy the replacement application paths, then remove the old routines. Rolling application code back after retirement would require a reviewed forward migration restoring its dependencies first.

What I tested

The repository has disposable PostgreSQL checks for migration replay, atomic failure, concurrent applies, immutable history, and schema reconstruction. Workflow tests exercise permissions and transaction behavior with the custom application functions removed. Separate checks cover first-request provisioning, revocation, and native constraints.

A database-transport check prevents new application Data API calls or custom function calls from quietly reintroducing the previous architecture. Type checks, application tests, builds, and deployed workflow checks cover the surrounding contracts.

These checks serve different purposes. A successful build does not prove isolation between users. A fast local query does not prove a fast deployed sign-in. A migration that succeeds on an empty database does not prove it preserves an existing installation.

What became easier—and what I now maintain

I can follow a business operation from its API handler into a typed product transaction, and review schema and policy changes in one migration queue. The apps retain their boundaries instead of sharing one unrestricted database client.

The cost is explicit responsibility. Application transactions must preserve locking, validation, and authorization. Storage operations still need compensation. Migrations still need review. Drizzle makes the code easier for me to inspect; it does not choose those guarantees for me.

For this project, that was the reason to migrate: clearer ownership of data access and business behavior, with performance improvements measured across the request path rather than credited to a library name.