Database Migrations
Relevant source files
The following files were used as context for generating this wiki page:
The prpm registry is undergoing a strategic, incremental migration from raw SQL queries to Drizzle ORM. This initiative aims to improve type safety, maintainability, and developer experience when interacting with the database. The process is designed to be careful and phased to ensure system stability, especially since it's a production system.
Existing raw SQL queries executed via @fastify/postgres will continue to function alongside new Drizzle queries during the transition. The migration is broken down into distinct phases, starting with read-only operations and progressing to more critical write operations.
Sources: packages/registry/DRIZZLE_MIGRATION.md:3-13
Migration Strategy
The migration from raw SQL to Drizzle ORM is executed in three main phases to minimize risk and ensure a smooth transition for the production system.
This diagram shows the three-phase migration strategy from raw SQL to Drizzle ORM.
Sources: packages/registry/DRIZZLE_MIGRATION.md:87-152
Phase 1: Schema Setup (Complete)
This foundational phase involved installing and configuring Drizzle ORM and Drizzle Kit. The database schema, consisting of 56 tables and 647 columns, was introspected and saved to src/db/schema/. Key tasks included:
- Installing
drizzle-ormanddrizzle-kit. - Configuring
drizzle.config.ts. - Introspecting the schema from the production database.
- Fixing known introspection issues (e.g.,
tsvectorcolumns, array defaults). - Adding SSL configuration to the Drizzle pool to match the existing Fastify setup.
Sources: packages/registry/DRIZZLE_MIGRATION.md:87-94, packages/registry/DRIZZLE_MIGRATION.md:5-6
Phase 2: Gradual Route Migration (In Progress)
The current phase focuses on replacing raw SQL queries with Drizzle ORM on a route-by-route basis, starting with the simplest, lowest-risk endpoints.
The process for migrating each route is as follows:
This diagram illustrates the iterative process for migrating a single route to Drizzle.
Sources: packages/registry/DRIZZLE_MIGRATION.md:98-107
The migration is ordered by risk level, as detailed in the table below.
| Risk Level | Migration Order | Examples |
|---|---|---|
| Low | 1 | Simple SELECT by ID (e.g., GET /packages/:id) |
| Medium | 2 | List queries with pagination and sorting |
| Medium-High | 3 | Search queries using full-text search |
| High | 4 | Aggregate queries (e.g., counts, stats) |
Sources: packages/registry/DRIZZLE_MIGRATION.md:109-115
Phase 3: Write Operations (Planned)
This final phase will tackle INSERT, UPDATE, and DELETE operations. It will only begin after all read queries are stable. The approach will remain cautious, migrating one table at a time, starting with the lowest-risk write operations.
Order of Write Migration:
- Analytics (Low Risk):
download_events,package_views. - User Actions (Medium Risk): Starring/rating packages, user profile updates.
- Core Operations (High Risk): Publishing, updating, or deleting packages.
Sources: packages/registry/DRIZZLE_MIGRATION.md:133-152
Schema Management
Schema management is primarily driven by introspection from the database, ensuring the code representation in src/db/schema/ is a source of truth. Manual edits to the schema files are strongly discouraged.
Sources: packages/registry/DRIZZLE_MIGRATION.md:41-43, packages/registry/DRIZZLE_MIGRATION.md:158-159
Introspection Workflow
When the database schema changes, developers must re-introspect it to update the Drizzle schema files.
This diagram shows the developer workflow for updating the Drizzle schema after a database change.
Sources: packages/registry/DRIZZLE_MIGRATION.md:46-59
Known Introspection Issues
The drizzle-kit introspect command has known issues that require manual correction after generation:
tsvectorcolumns: These are generated asunknown("col")and must be manually changed to a customtsvector("col")type.- Array defaults: Malformed arrays like
[RAY[50, 75, 9]may be generated and need to be fixed. - Materialized view options: Unsupported
.with({...})clauses must be removed.
Sources: packages/registry/DRIZZLE_MIGRATION.md:180-185
Tooling and Configuration
Drizzle Kit Configuration
The configuration for Drizzle Kit is located in drizzle.config.ts. It specifies the schema location, output directory for generated files, and database connection details.
| Option | Value | Description |
|---|---|---|
schema | ./src/db/schema/schema.ts | Path to the main schema file used by Drizzle. |
out | ./drizzle | Output directory for introspected schema files. |
driver | pg | Specifies the PostgreSQL driver. |
dbCredentials | (from process.env) | Credentials are loaded from environment variables. |
Sources: packages/registry/drizzle.config.ts
// packages/registry/drizzle.config.ts
import type { Config } from 'drizzle-kit';
import * as dotenv from 'dotenv';
import { resolve } from 'path';
// Load .env from project root
dotenv.config({ path: resolve(__dirname, '../../.env') });
export default {
schema: './src/db/schema/schema.ts',
out: './drizzle',
driver: 'pg',
dbCredentials: {
connectionString: process.env.DATABASE_URL!,
},
} satisfies Config;
Sources: packages/registry/drizzle.config.ts
Available Commands
Several npm scripts are available to aid in database management.
| Command | Description |
|---|---|
npm run db:introspect | Generates the Drizzle schema from the database into the drizzle/ folder. |
npm run db:studio | Opens Drizzle Studio, a visual browser for the database. |
npm run db:migrate:create | Command mentioned for creating new migration files. |
Sources: packages/registry/DRIZZLE_MIGRATION.md:34-40, packages/webapp/src/app/blog/agents-md-deep-dive/page.tsx:180-180
Automated Taxonomy Migration
A notable use case for migrations is the automated generation of the categories and use_cases taxonomy. The generate-taxonomy.ts script performs this task.
This diagram outlines the process of automatically generating a SQL migration file for the taxonomy.
Sources: packages/registry/scripts/generate-taxonomy.ts
The script connects to the database, analyzes package data using the OpenAI API, and constructs a new SQL migration file (e.g., 001_generated_taxonomy.sql) with INSERT statements to populate the categories and use_cases tables. This demonstrates a powerful pattern of using automation to create and manage database schema and data changes.
Sources: packages/registry/scripts/generate-taxonomy.ts:223-333, packages/registry/migrations/
Best Practices and Rules
To ensure consistency and safety, developers are expected to follow specific guidelines when working with database migrations.
What to Do
- Create new migrations: Use commands like
npm run db:migrate:create. - Test migrations: Always test migrations on a development database first.
- Use comparison tests: When migrating a query, write a test to ensure the Drizzle output exactly matches the raw SQL output.
- Follow the incremental approach: Migrate one route at a time.
- Keep a rollback plan: Keep the old raw SQL implementation available for a quick revert if issues arise.
Sources: packages/webapp/src/app/blog/agents-md-deep-dive/page.tsx:178-182, packages/registry/DRIZZLE_MIGRATION.md:100-101, packages/registry/DRIZZLE_MIGRATION.md:187-193
What NOT to Do
- Do not run
drizzle-kit pushordrizzle-kit migrateagainst production without extensive testing. - Do not edit
schema.tsmanually; always regenerate it from the database. - Do not migrate all routes at once.
- Do not remove the raw SQL implementation immediately after switching to Drizzle.
Sources: packages/registry/DRIZZLE_MIGRATION.md:154-162, packages/webapp/src/app/blog/agents-md-deep-dive/page.tsx:178-178
Example: Migrating a Query
The following shows a practical example of converting a raw SQL query to its Drizzle ORM equivalent.
Before (Raw SQL):
const result = await server.pg.query(
'SELECT id, name, description FROM packages WHERE id = $1',
[packageId]
);
return result.rows[0];
After (Drizzle ORM):
import { db } from '../db/db.js';
import { packages } from '../db/schema/index.js';
import { eq } from 'drizzle-orm';
const result = await db
.select({
id: packages.id,
name: packages.name,
description: packages.description,
})
.from(packages)
.where(eq(packages.id, packageId))
.limit(1);
return result[0];
Sources: packages/registry/DRIZZLE_MIGRATION.md:167-177
Conclusion
The database migration to Drizzle ORM is a critical, ongoing effort within the prpm project. By adopting a careful, phased, and test-driven strategy, the team ensures production stability while progressively enhancing the codebase with a modern, type-safe ORM. The combination of schema introspection, automated migration generation, and strict developer guidelines provides a robust framework for managing the evolution of the database.