Relevant source files

The following files were used as context for generating this wiki page: - [platform/flowglad-next/drizzle.config.ts](https://github.com/flowglad/flowglad/blob/main/platform/flowglad-next/drizzle.config.ts) - [platform/flowglad-next/src/scripts/migrate.ts](https://github.com/flowglad/flowglad/blob/main/platform/flowglad-next/src/scripts/migrate.ts) - [platform/flowglad-next/README.md](https://github.com/flowglad/flowglad/blob/main/platform/flowglad-next/README.md) - [CONTRIBUTING.md](https://github.com/flowglad/flowglad/blob/main/CONTRIBUTING.md) - [playground/generation-based-subscription/README.md](https://github.com/flowglad/flowglad/blob/main/playground/generation-based-subscription/README.md) - [platform/flowglad-next/llm-prompts/new-db-table.md](https://github.com/flowglad/flowglad/blob/main/platform/flowglad-next/llm-prompts/new-db-table.md) - [platform/flowglad-next/src/db/claude.md](https://github.com/flowglad/flowglad/blob/main/platform/flowglad-next/src/db/claude.md)

# Database Migrations Database migrations in the Flowglad project are managed using Drizzle ORM to handle schema changes for the PostgreSQL database. This system provides a structured and version-controlled way to evolve the database schema as the application's data model changes. The process involves defining the schema in TypeScript, generating SQL migration files with Drizzle Kit, and applying these migrations to the database via dedicated scripts. This ensures that database schema updates are consistent across all development and production environments. The workflow is integrated into the project's `bun` scripts, providing developers with simple commands to generate and apply migrations. For more information on the overall database structure, see the [Database Layer Documentation](#). Sources: [platform/flowglad-next/README.md:23-26](), [platform/flowglad-next/src/db/claude.md:3-5]() ## Core Technology Stack The migration system is built upon two primary technologies: * **Drizzle ORM**: Used for defining the database schema as TypeScript objects and for its `drizzle-kit` utility, which compares the defined schema against the database state to generate SQL migration files. * **PostgreSQL**: The relational database used by the application. Migrations are generated as standard SQL files compatible with PostgreSQL. Sources: [platform/flowglad-next/drizzle.config.ts:2](), [platform/flowglad-next/src/db/claude.md:3-5]() ## Configuration The configuration for Drizzle Kit is located in `drizzle.config.ts`. This file specifies where the schema definitions are, where to output the generated migration files, and the database connection details. | Option | Description | Value | | --------------- | ------------------------------------------------------------------------------------------------------- | ------------------------------------------- | | `schema` | Path to the TypeScript file(s) containing the Drizzle schema definitions. | `./src/db/schema/*` | | `out` | The directory where generated migration files will be stored. | `./drizzle-migrations` | | `dbCredentials` | An object containing the `connectionString` for the database, loaded from the `DATABASE_URL` environment variable. | `{ connectionString: process.env.DATABASE_URL! }` | Sources: [platform/flowglad-next/drizzle.config.ts:1-11]() ## Migration Workflow The process of creating and applying a database migration follows a clear, multi-step workflow. It begins with a developer modifying the schema and ends with the changes being applied to the database. This diagram illustrates the developer workflow for database migrations. ```mermaid flowchart TD A[1. Modify Schema
in `src/db/schema/*.ts`] --> B{2. Run Migration
Generation Command}; B -- `bun db:generate` --> C[3. Drizzle Kit Generates
SQL Migration File]; C --> D[4. New file created in
`drizzle-migrations/`]; D --> E{5. Run Migration
Apply Command}; E -- `bun db:migrate` or `migrations:push` --> F[6. `src/scripts/migrate.ts`
is executed]; F --> G[7. SQL Migration is
applied to PostgreSQL DB]; ``` Sources: [platform/flowglad-next/README.md:23-24](), [playground/generation-based-subscription/README.md:73-74](), [platform/flowglad-next/llm-prompts/new-db-table.md]() ## Key Scripts and Commands Several `bun` scripts are available to manage the migration lifecycle. These are defined in the project's `package.json` and documented in various README files. | Command | Description | Source File - | | `bun run migrations:push` | A command to apply migrations, likely used for initial setup or in CI/CD environments. | [platform/flowglad-next/README.md:23]() - | | `bun run test:setup` | Sets up the test database, which includes running migrations to ensure the schema is up-to-date before tests are executed. | [CONTRIBUTING.md:143]() - | | `bun db:generate` | Generates a new migration file based on changes detected in the schema definition files. This is equivalent to `drizzle-kit generate:pg`. | [playground/generation-based-subscription/README.md:73]() - | | `bun db:migrate` | Applies all pending migrations to the database. This script executes `src/scripts/migrate.ts`. | [playground/generation-based-subscription/README.md:74]() - | ## Migration Script Internals The script responsible for applying migrations is `platform/flowglad-next/src/scripts/migrate.ts`. This script performs the following actions: 1. Loads environment variables using `dotenv`. 2. Establishes a connection to the PostgreSQL database using the `postgres` library. 3. Initializes Drizzle with the database connection. 4. Calls the `migrate` function from `drizzle-orm/postgres-js/migrator`, pointing it to the `drizzle-migrations` directory. 5. The `migrate` function reads the SQL files in the migrations directory and applies any that have not yet been run. 6. Logs a success message upon completion or an error message if the process fails. ```typescript // platform/flowglad-next/src/scripts/migrate.ts import { drizzle } from 'drizzle-orm/postgres-js' import { migrate } from 'drizzle-orm/postgres-js/migrator' import postgres from 'postgres' import 'dotenv/config' const runMigrations = async () => { if (!process.env.DATABASE_URL) { throw new Error('DATABASE_URL is not set') } const db = drizzle(postgres(process.env.DATABASE_URL, { max: 1 })) console.log('Running migrations...') await migrate(db, { migrationsFolder: 'drizzle-migrations' }) console.log('Migrations ran successfully') process.exit(0) } runMigrations().catch((err) => { console.error(err) process.exit(1) }) ``` Sources: [platform/flowglad-next/src/scripts/migrate.ts:1-26]() ## Developer Process for Schema Changes When a developer needs to add or modify a database table, they must follow a specific process to ensure the changes are correctly captured in a migration. This process is centered around updating the schema definition files. This diagram shows the detailed steps for a developer making a schema change. ```mermaid graph TD subgraph "Developer Actions" A[1. Create/Edit file in
`src/db/schema/*.ts`] --> B[2. Define table with `pgTable`
and `tableBase` helper]; B --> C[3. Add columns, foreign keys,
and indexes]; C --> D[4. Define Zod schemas for
Insert, Select, Update]; D --> E[5. Create `*Methods.ts` file
with CRUD functions]; E --> F[6. Run `bun db:generate`]; end subgraph "System Actions" F --> G[Drizzle Kit generates
a new SQL migration]; end G --> H[7. Developer commits new
schema, methods, and migration files]; ``` This structured approach ensures that schema definitions, data access methods, and the database state remain synchronized. Sources: [platform/flowglad-next/llm-prompts/new-db-table.md](), [platform/flowglad-next/src/db/claude.md:111-113]()