migrations & drizzle-kit

47 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Migrations generated from TypeScript schema

Drizzle v0.11.0 includes a CLI tool for automatic migration generation from TypeScript table schemas. The tool generates SQL migration files that handle table creation, indexes, and foreign key constraints. The tool can prompt to resolve ambiguities like renames and deletes.

Generated SQL migration example for v0.11.0

For a schema with UsersTable and AuthOtpTable, the generated SQL migration creates tables with CREATE TABLE IF NOT EXISTS, declares foreign keys using ALTER TABLE with ADD CONSTRAINT and FOREIGN KEY syntax, and creates indexes with CREATE INDEX IF NOT EXISTS. The migration uses DO $$ blocks for conditional constraint creation to handle idempotency.

Generate PostgreSQL schema migrations

Use drizzle-kit with the generate:pg command to automatically generate SQL migrations for PostgreSQL schemas. The command syntax is: drizzle-kit generate:pg --schema=src/schema.ts --out=migrations/

Generate MySQL schema migrations

Use drizzle-kit with the generate:mysql command to automatically generate SQL migrations for MySQL schemas. The command syntax is: drizzle-kit generate:mysql --schema=src/schema.ts --out=migrations/

PostgreSQL introspection with drizzle-kit

Use drizzle-kit introspect:pg command to pull database schema from an existing PostgreSQL database. The command syntax is: drizzle-kit introspect:pg --out=migrations/ --connectionString=postgresql://user:••••@host:port/db_name. This generates a schema.ts file and supports enums, tables with native and non-native columns, indexes, foreign keys (including self-references and cyclic foreign keys), and schemas.

PostgreSQL introspection supports self-referencing and cyclic foreign keys

When using drizzle-kit introspect:pg to pull an existing PostgreSQL database schema, it automatically handles self-referencing tables and cyclic foreign key relationships. Example: a cyclic1 table can reference cyclic2 which references back to cyclic1, using syntax like ext2: integer("ext2").references(() => cyclic2.id) and ext1: integer("ext1").references((): AnyPgColumn => cyclic1.id) to avoid circular dependency issues.

Expo SQLite migrations - babel configuration

To use Drizzle migrations with Expo SQLite, install babel-plugin-inline-import. Update babel.config.js to add the plugin with configuration: plugins: [["inline-import", { "extensions": [".sql"] }]]

Expo SQLite migrations - metro configuration

To use Drizzle migrations with Expo SQLite, update metro.config.js to add '.sql' to the resolver's sourceExts by adding: config.resolver.sourceExts.push('sql')

Generate migrations for Expo SQLite

After creating the schema file and drizzle.config.ts, generate migrations for Expo SQLite using: npx drizzle-kit generate

Apply migrations at runtime in Expo SQLite

Import the migrations.js file from the ./drizzle folder and use the useMigrations hook in App.tsx. The hook returns an object with success and error properties. Call useMigrations(db, migrations) to run migrations. The hook provides success boolean that indicates when migrations are complete, and error object if migration fails.

Expo SQLite migration hook example

Use the useMigrations hook from 'drizzle-orm/expo-sqlite/migrator' by calling it with the database instance and migrations object. Check the success property to determine if migrations completed successfully, and check the error property for migration failures. The hook should be called in the App component before rendering the application.

Custom migrations table name with migrationsTable option

By default, migration information is stored in the __drizzle_migrations table (in the drizzle schema for PostgreSQL). You can specify a custom table name using the migrationsTable option in the migrate function: await migrate(db, { migrationsFolder: './drizzle', migrationsTable: 'my_migrations' });

Custom migrations schema with migrationsSchema option

For PostgreSQL databases, you can specify a custom schema name for migrations using the migrationsSchema option in the migrate function: await migrate(db, { migrationsFolder: './drizzle', migrationsSchema: 'custom' }); This option works only with PostgreSQL databases.

Migration hook fix for Expo driver in v0.30.1

Drizzle ORM v0.30.1 fixed the migration hook for the Expo driver.

LibSQL batch operations for migrations

LibSQL migrations have been updated to utilize batch execution instead of transactions. A batch consists of multiple SQL statements executed sequentially within an implicit transaction. The backend handles the transaction: success commits all changes, while any failure results in a full rollback with no modifications.

D1 migrate() function uses batch API

The migrate() function for Cloudflare D1 has been changed to use the batch API for executing migrations.

AWS Data API migrator fix in v0.30.9

Version 0.30.9 fixed the migrator when using AWS Data API, resolving issues that occurred during migration execution with AWS Data API.

MySQL generated columns limitation in drizzle-kit push

In drizzle-kit push command, you cannot change the generated constraint expression and type for MySQL generated columns. To make changes, you must drop the column, push, and then add the column with the new expression. Since these are generated columns, all data will be restored automatically.

SQLite generated columns limitation: cannot change stored type

SQLite does not allow changing the generated constraint expression with stored type in an existing table. The table must be deleted and recreated. This is due to SQLite limitations for such actions.

SQLite generated columns limitation: cannot add stored expression to existing column

SQLite does not allow adding a stored generated expression to an existing column, but virtual expressions can be added to existing columns.

SQLite generated columns limitation: cannot change stored expression

SQLite does not allow changing a stored generated expression in an existing column, but virtual expressions can be changed.

SQLite generated columns limitation: cannot change from virtual to stored

SQLite does not allow changing a generated column type from virtual to stored, but changing from stored to virtual is allowed.

drizzle-kit push --force flag

The --force flag for drizzle-kit push command allows auto-accepting all data-loss statements. It is only available as a CLI parameter. Use it when you are fine with running data-loss statements on your database.

drizzle-kit migrations prefix options

The migrations flag prefix property customizes migration file prefixes. Options are: 'index' (default, results in 0001_name.sql), 'supabase' and 'timestamp' (equal, result in 20240627123900_name.sql), 'unix' (results in 1719481298_name.sql), 'none' (omits prefix completely).

Folders v3 migrations structure changes

Drizzle v1.0.0-beta.2 introduces Folders v3 migration format with the following changes: removal of journal.json, grouping of SQL files and snapshots into separate migration folders, and removal of the `drizzle-kit drop` command. These changes eliminate potential Git conflicts with the journal file and simplify the process of dropping or fixing conflicted migrations. To migrate previous folders to the new format, run `drizzle-kit up`.

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 performs the following sequence: (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) based on previously applied migrations, decides which new migrations to run, and (4) runs SQL migrations and logs applied migrations to the drizzle migrations table.

drizzle-kit migrate requires dialect and database credentials

The drizzle-kit migrate command requires you to specify both dialect and database connection credentials. These can be provided either via the drizzle.config.ts config file or via CLI options.

drizzle-kit migrate with config file example

Example configuration for drizzle-kit migrate using drizzle.config.ts: ```ts // drizzle.config.ts import { defineConfig } from "drizzle-kit"; export default defineConfig({ dialect: "mysql", schema: "./src/schema.ts", dbCredentials: { url: "mysql://user:password@host:port/dbname" }, }); ``` Then run: `npx drizzle-kit migrate`

drizzle-kit migrate with CLI options example

You can run drizzle-kit migrate directly with CLI options without a config file: ```shell npx drizzle-kit migrate --dialect=mysql --url=mysql://user:password@host:port/dbname ```

drizzle migrations log table default name

Upon running migrations, Drizzle Kit persists records of successfully applied migrations in a table named __drizzle_migrations by default.

customize drizzle migrations log table name

You can customize the table name of the migrations log table via the drizzle config file using the migrations.table property. Example: ```ts export default defineConfig({ dialect: "mysql", schema: "./src/schema.ts", dbCredentials: { url: "mysql://user:password@host:3306/dbname" }, migrations: { table: 'my-migrations-table', // __drizzle_migrations by default }, }); ```

multiple config files for different database stages

You can have multiple config files in a project for different database stages or multiple databases. Use the --config option to specify which config file to use: `npx drizzle-kit migrate --config=drizzle-dev.config.ts` or `npx drizzle-kit migrate --config=drizzle-prod.config.ts`

end-to-end migration workflow with generate and migrate

Complete workflow example using drizzle-kit generate and drizzle-kit migrate: 1. Define schema in 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 }), }) ``` 2. Configure drizzle.config.ts with dialect, schema path, and database credentials. 3. Run `npx drizzle-kit generate --name=init` to generate SQL migration files. 4. Run `npx drizzle-kit migrate` to apply the migrations to the database.

drizzle-kit up command purpose

The drizzle-kit up command lets you upgrade drizzle schema snapshots to a newer version. It is required whenever breaking changes are introduced to the json snapshots of the schema and the internal version is upgraded.

drizzle-kit up dialect requirement

The drizzle-kit up command requires you to specify the dialect parameter. You can provide it either via the drizzle.config.ts config file or via CLI options.

drizzle-kit up with config file

To use drizzle-kit up with a config file, define dialect in drizzle.config.ts and run npx drizzle-kit up. Example: export default defineConfig({ dialect: "mysql" });

drizzle-kit up with CLI options

You can pass the dialect directly to drizzle-kit up via CLI options using the --dialect flag. Example: npx drizzle-kit up --dialect=mysql

drizzle-kit up multiple config files

You can have multiple config files in one project using the --config flag. Example: npx drizzle-kit migrate --config=drizzle-dev.config.ts and npx drizzle-kit migrate --config=drizzle-prod.config.ts. This is useful when you have multiple database stages or multiple databases in the same project.

drizzle-kit up CLI parameters

The drizzle-kit up command accepts the following CLI parameters: dialect (required, the database dialect being used), out (optional, migrations folder path, default './drizzle'), and config (optional, configuration file path, default 'drizzle.config.ts').

drizzle-kit up example with out parameter

You can specify a custom migrations folder with drizzle-kit up using the --out flag. Example: npx drizzle-kit up --dialect=mysql --out=./migrations-folder

Use single client connection for migrations

For the built-in migrate function with DDL migrations, use a single client connection rather than a pool connection. Drivers strongly encourage this approach for migrations.

Generate custom migration files with drizzle-kit

You can generate empty custom migration files to write your own SQL migrations for DDL alterations not supported by Drizzle Kit or for data seeding. Use the command: drizzle-kit generate --custom --name=<migration-name>

Custom migration files are stored in drizzle directory

Custom migration files generated with --custom flag are stored in the drizzle directory with a timestamp prefix followed by the migration name, for example: 20242409135510_seed-users.sql

Run custom migrations with drizzle-kit migrate

Custom SQL migration files can be executed using the drizzle-kit migrate command, which runs the migrations against the database.

Example custom SQL migration for data seeding

Custom migrations can contain SQL for data seeding. Example: INSERT INTO "users" ("name") VALUES('Dan'); INSERT INTO "users" ("name") VALUES('Andrew'); INSERT INTO "users" ("name") VALUES('Dandrew');

JavaScript and TypeScript migrations not yet supported

The ability to run custom JavaScript and TypeScript migration and seeding scripts is planned for an upcoming release but not currently available. Follow the GitHub discussion at https://github.com/drizzle-team/drizzle-orm/discussions/2832 for updates.

Give your agent this brain