drizzle-kit migrate with config file
Configuration example using drizzle.config.ts: Set `dialect: "mysql"`, `schema: "./src/schema.ts"`, and `dbCredentials.url: "mysql://user:password@host:port/dbname"`. Then run `npx drizzle-kit migrate`.
Drizzle · MySQL · all subjects
migrations
52 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
customize migrations log table name
You can customize the table name used to store the migrations log via the drizzle config file. Set `migrations.table` to a custom table name, for example `migrations.table: 'my-migrations-table'`. The default is `__drizzle_migrations`.
drizzle-kit generate and migrate workflow example
Example workflow: (1) Define schema in src/schema.ts with a users table containing id (int auto-increment primary key) and name (varchar 255), (2) Configure drizzle.config.ts with MySQL dialect, schema path, and database credentials, (3) Run `npx drizzle-kit generate --name=init` to create migration files, (4) Run `npx drizzle-kit migrate` to apply the migrations to the database. This generates a migration folder with timestamped subdirectories containing .sql files that define the schema changes.
drizzle-kit pull with CLI options
You can run `drizzle-kit pull` with CLI options: `npx drizzle-kit pull --dialect=mysql --url=mysql://user:password@host:3306/dbname`
drizzle-kit pull default table introspection
`drizzle-kit pull` will introspect all MySQL tables by default.
drizzle-kit pull generated schema example
Example of schema generated by `drizzle-kit pull` for a MySQL table with id, name, and email columns: ```typescript import * as p from "drizzle-orm/mysql-core"; export const users = p.mysqlTable( "users", { id: p.int().autoincrement().primaryKey(), name: p.varchar({ length: 255 }), email: p.varchar({ length: 255 }), }, (table) => [p.uniqueIndex("email").on(table.email)] ); ```
drizzle-kit push tablesFilter option
The tablesFilter option is a glob-based table names filter that controls which tables drizzle-kit push will manage. For example, you can specify ["users", "user_info"] or "user*". The default is "*" which manages all MySQL tables.
drizzle-kit push with config file example
Example drizzle.config.ts configuration: ```ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ dialect: "mysql", schema: "./src/schema.ts", dbCredentials: { url: "mysql://user:password@host:3306/dbname", }, }); ``` Then run `npx drizzle-kit push`.
drizzle-kit push with CLI options example
You can provide all configuration options through CLI instead of a config file: `npx drizzle-kit push --dialect=mysql --schema=./src/schema.ts --url=mysql://user:password@host:3306/dbname`
drizzle-kit push tablesFilter example
Example drizzle.config.ts with tablesFilter set to manage all tables: ```ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ dialect: "mysql", schema: "./src/schema.ts", dbCredentials: { url: "mysql://user:password@host:3306/dbname", }, tablesFilter: ["*"], }); ``` Then run `npx drizzle-kit push`.
drizzle-kit push CLI options flags example
You can combine CLI options when running drizzle-kit push: `npx drizzle-kit push --explain --verbose --force`
drizzle-kit push extended example with schema
Complete example showing drizzle.config.ts and schema.ts files and the resulting generated SQL: drizzle.config.ts: ```ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ dialect: "mysql", schema: "./src/schema.ts", dbCredentials: { url: "mysql://user:password@host:3306/dbname" }, }); ``` src/schema.ts: ```ts import * as p from "drizzle-orm/mysql-core"; export const users = p.mysqlTable("users", { id: p.int().primaryKey().autoincrement(), name: p.varchar({ length: 255 }), }) ``` Running `npx drizzle-kit push` generates and applies this SQL: ```sql CREATE TABLE `users` ( `id` int AUTO_INCREMENT PRIMARY KEY, `name` varchar(255) ); ```
Run custom migrations with drizzle-kit migrate
Custom migration files generated with the --custom flag can be executed using the drizzle-kit migrate command to apply DDL alterations or data seeding operations not currently supported by Drizzle Kit's automatic migration generation.
Drizzle migrations fundamentals overview
SQL databases require a strict schema defined upfront. When changes are needed, schema migrations must be applied. Drizzle supports both database-first and codebase-first approaches to managing migrations.
Database first approach definition
Database first is when the database schema is the source of truth. You manage the database schema directly on the database or via database migration tools, then pull the database schema into your codebase application level entities. Drizzle Kit's `drizzle-kit pull` command is used for this approach.
Codebase first approach definition
Codebase first is when the database schema in your codebase is the source of truth and is under version control. You declare and manage the database schema in JavaScript/TypeScript and then apply that schema to the database either with Drizzle, directly, or via external migration tools.
Option 1: Database first with drizzle-kit pull
For database first approach where you manage schema externally and need to get the current state from your database: use `drizzle-kit pull` to pull the database schema to TypeScript. This approach treats the database schema as the source of truth.
Option 2: Codebase first with drizzle-kit push
For codebase first approach where you want database schema in TypeScript codebase without dealing with SQL migration files: use `drizzle-kit push` to push your schema directly to the database. This approach treats the TypeScript Drizzle schema as the source of truth and is recommended for rapid prototyping and production applications.
Option 3: Generate SQL migrations with drizzle-kit generate and drizzle-kit migrate
For codebase first approach where you want Drizzle to generate SQL migration files and apply them via CLI: use `drizzle-kit generate` to create SQL migration files based on schema changes, then use `drizzle-kit migrate` to apply them to the database. This generates migration folders containing snapshot.json and migration.sql files.
Option 4: Generate migrations and apply at runtime
For codebase first approach where you want to apply migrations during runtime of your application: use `drizzle-kit generate` to generate SQL migration files, then use the migrate function imported from 'drizzle-orm/mysql2/migrator' in your application code. This approach is used for monolithic applications during zero downtime deployment and serverless deployments with migrations running in custom resources.
Runtime migration example for MySQL
To apply migrations at runtime in a MySQL application, import drizzle and the migrate function, then call await migrate(db) where db is your drizzle database instance. The migrate function reads migration.sql files, fetches migration history from the database, identifies previously unapplied migrations, and applies them.
Option 5: Generate migrations for external tools
For codebase first approach where you want Drizzle to generate SQL migration files but apply them yourself or via external tools: use `drizzle-kit generate` to create SQL migration files. You can then apply them directly to the database or via external migration tools like Bytebase, Liquibase, or Atlas.
New migration folder structure v3
Migration folder structure v3 removes journal.json, groups SQL files and snapshots into separate migration folders, and removes the drizzle-kit drop command. These changes eliminate potential Git conflicts and simplify the process of managing migrations.
Migration table structure updates in v1
The migration table is automatically upgraded when running drizzle-kit up. Two new columns are added: name (the full migration folder name e.g. 20250220153045_brave_wolverine) and applied_at (timestamp of when the migration was executed, pre-existing rows backfilled with NULL). Migration table now contains: id (serial), hash (text), created_at (bigint, legacy), name (text, new), applied_at (timestamp, new).
Migration matching by folder name instead of timestamp
Migrations are now matched by their full folder name instead of timestamps. The name suffix guarantees uniqueness even if two migrations are generated within the same second. During upgrade, existing rows are backfilled using: millis match (truncate stored millis to seconds), hash tiebreaker (disambiguate via SQL hash if multiple migrations share same second), and hash-only fallback (match by hash alone if millis matching fails).
Migrator applies all missing migrations
The migrator now detects and applies every missing migration regardless of timestamp ordering. Previously it only looked for local migrations with creation date later than the last applied one. All migrations are matched against the full folder name (14-digit UTC timestamp plus name suffix).
drizzle-kit generate applies migrations via multiple methods
Generated migrations can be applied using: drizzle-kit migrate command, drizzle-orm's migrate() function, external migration tools like bytebase, or by running migrations directly on the database.
drizzle-kit migrate configuration
The drizzle-kit migrate command requires both a dialect and database connection credentials. These can be provided either via a drizzle.config.ts config file or as CLI options. The config file example shows: dialect set to 'cockroach', schema pointing to './src/schema.ts', and dbCredentials with a PostgreSQL URL in the format 'postgresql://user:password@host:port/dbname'.
drizzle-kit migrate via CLI options
To run migrations with CLI options instead of a config file, use: npx drizzle-kit migrate --dialect=cockroach --url=postgresql://user:password@host:port/dbname
migrations log table default name and schema
Upon running migrations, Drizzle Kit persists records about successfully applied migrations in a table named __drizzle_migrations in the drizzle schema by default.
migrations apply sequence example
To apply migrations: first run npx drizzle-kit generate --name=init to create a migration file, then run npx drizzle-kit migrate to apply the generated SQL migration to the database.
migrations config option
The migrations option configures logging of successfully applied migrations. Type is object with properties: table (string, default '__drizzle_migrations') and schema (string, default 'drizzle', used in PostgreSQL only). Used in commands: migrate, push, pull.
migrations folder structure
The migrations folder defined by the 'out' parameter contains subdirectories with .sql migration files generated by drizzle-kit. The default folder is named 'drizzle'. Each migration is in a timestamped folder containing the SQL alterations.
Automatic migration generation from schema
Drizzle implements a CLI tool for automatic migration generation that handles renames and deletes by prompting the user to resolve. It generates SQL migrations from TypeScript schema definitions, including CREATE TABLE, indexes, and foreign key constraints with proper safeguards like IF NOT EXISTS and exception handling.
Expo SQLite migrations setup with babel
To use Drizzle migrations with Expo SQLite, install babel-plugin-inline-import. In babel.config.js, add the plugin: plugins: [["inline-import", { "extensions": [".sql"] }]]. This allows inline importing of SQL migration files.
Expo SQLite migrations setup with metro
To use Drizzle migrations with Expo SQLite, update metro.config.js to include .sql files in source extensions: config.resolver.sourceExts.push('sql');. This allows the Metro bundler to process SQL migration files.
Expo SQLite migrations generation
After creating the schema file and drizzle.config.ts, generate migrations by running: npx drizzle-kit generate. This creates migration files in the specified output directory.
Expo SQLite migrations in App component
Import the migrations.js file from the drizzle output folder and use the useMigrations hook in your App.tsx component. The hook returns success and error states. Show an error message if error exists, show a loading message if success is false, otherwise render the application component.
Expo SQLite useMigrations hook example
import { drizzle } from "drizzle-orm/expo-sqlite"; import { openDatabaseSync } from "expo-sqlite"; import { useMigrations } from 'drizzle-orm/expo-sqlite/migrator'; import migrations from './drizzle/migrations'; const expoDb = openDatabaseSync("db.db"); const db = drizzle(expoDb); export default function App() { const { success, error } = useMigrations(db, migrations); if (error) { return ( <View> <Text>Migration error: {error.message}</Text> </View> ); } if (!success) { return ( <View> <Text>Migration is in progress...</Text> </View> ); } return ...your application component; }
Custom migrations table name via migrationsTable option
To store migration records in a custom table instead of the default __drizzle_migrations table, pass the migrationsTable option to the migrate() function: migrate(db, { migrationsFolder: './drizzle', migrationsTable: 'my_migrations' }). This works with all supported databases.
Custom migrations schema via migrationsSchema option
To store migration records in a custom schema instead of the default drizzle schema, pass the migrationsSchema option to the migrate() function: migrate(db, { migrationsFolder: './drizzle', migrationsSchema: 'custom' }). This option works only with PostgreSQL databases.
Custom migrations use case
Drizzle Kit custom migrations are used for DDL alterations not currently supported by Drizzle Kit and for data seeding. These custom SQL migrations can then be executed with the `drizzle-kit migrate` command.
Custom SQL migration example
Example custom migration for seeding users: ```sql -- ./drizzle/20242409135510_seed-users.sql INSERT INTO "users" ("name") VALUES('Dan'); INSERT INTO "users" ("name") VALUES('Andrew'); INSERT INTO "users" ("name") VALUES('Dandrew'); ``` This shows how to write INSERT statements in a custom migration file.
JavaScript and TypeScript migrations planned
Support for running custom JavaScript and TypeScript migration and seeding scripts is planned for an upcoming release. This functionality is currently under discussion in the Drizzle team's GitHub discussions.
drizzle-kit migrate command purpose
The drizzle-kit migrate command applies SQL migrations generated by drizzle-kit generate. It is designed to cover the code-first approach of managing Drizzle migrations.
drizzle-kit migrate workflow steps
The drizzle-kit migrate command executes the following steps: 1) reads all .sql migration files from the migration folder, 2) connects to the database and fetches entries from the drizzle migrations log table, 3) determines which new migrations have not yet been applied, 4) runs the SQL migrations and logs applied migrations to the drizzle migrations table.
drizzle-kit migrate requires dialect and credentials
The drizzle-kit migrate command requires you to specify both dialect and database connection credentials. These can be provided either via a drizzle.config.ts config file or via CLI options.
drizzle-kit migrate with config file example
To run migrations with a config file, create a drizzle.config.ts file with dialect set to 'mssql', schema path, and dbCredentials containing the URL in format mssql://user:password@host:port/dbname. Then run: npx drizzle-kit migrate
drizzle-kit migrate with CLI options example
Migrations can be applied via CLI options without a config file using: npx drizzle-kit migrate --dialect=mssql --url=mssql://user:password@host:port/dbname
drizzle-kit migrations log table location
Applied migrations are stored in a table named __drizzle_migrations (by default) in the drizzle schema (by default).
customize migrations log table and schema
The migrations log table name and schema can be customized in the drizzle.config.ts file using the migrations.table and migrations.schema configuration options. Example: migrations: { table: 'my-migrations-table', schema: 'drizzle' }
multiple configuration files in one project
You can have multiple drizzle config files in the same project to manage different database stages or multiple databases. Specify which config file to use with the --config flag: npx drizzle-kit migrate --config=drizzle-dev.config.ts or npx drizzle-kit migrate --config=drizzle-prod.config.ts