Updated Migration Process

[AndriiSherman]

Migration process urgrade

After a year of gathering feedback, we have collected enough information and identified cases that were not handled properly or need improvement. In the 0.34.0 drizzle ORM, we plan to implement these changes.

In this post, I'll outline everything we have decided to change, along with the motivations behind these changes. If you have any suggestions for additions to this release regarding the migration process or believe something should be modified, please provide a detailed explanation so we can update this discussion post.

This discussion post will serve as a reference for us while adding test cases, necessary logic, etc.

Overview of Changes to the Migration Process:

Migrations Table Status Column

We will add a status column to the migrations table to handle failed, applied, or rollbacked statuses for migrations.

New shape of the __drizzle_migrations table will be:

PostgreSQL

CREATE TABLE __drizzle_migrations (
 id int generated always as identity,
 hash text not null,
 created_at bigint,
 status varchar
)

MySQL

CREATE TABLE __drizzle_migrations (
    id serial,
    hash text not null,
    created_at bigint
    status varchar
)

SQLite

CREATE TABLE __drizzle_migrations (
    id integer primary key,
    hash text not null,
    created_at numeric,
    status text
)

Strict Migrations by Default

All migrations will be strict by default, meaning there will be no IF NOT EXISTS, DO, or similar statements. We are considering adding a flag to re-enable these, but ideally, all migrations should be strict.

The reason it was done with IF NOT EXISTS is that initially, drizzle-orm and drizzle-kit were built around our own use cases for our own products, even before going public with it, and this legacy remained until now.

Without IF NOT EXISTS statements, if you have the same table in the database for some reason and create a table in migration, the IF NOT EXISTS case will just let your migration succeed without indicating that you have the same table. This table could potentially have a different structure and might break your backend code. The migration flow should ensure that it will fail in such cases. If you are applying migrations to the database, you won't encounter an issue from not using IF NOT EXISTS if everything is done correctly. If there is a shift in this process, you will know it from errors after running DDL statements in migrations.

You can always add IF NOT EXISTS if needed, but we don't recommend it unless it is absolutely necessary.

Database Introspection and Initial Migration

When you introspect the database, you will receive an initial migration without comments. Instead of commenting it out, we will add a flag to journal entity with the init flag, indicating that this migration was generated by introspect action

Currently, you see this message during the initial migration after introspecting the database

-- Current sql file was generated after introspecting the database
-- If you want to run this migration please uncomment this code before executing migrations

Everything that was generated was commented out, but we will remove it now. Here are a few flows that can be used with introspect. May have forget something or don't know about a specific flow, so would be happy to update it after your comments

1. You don't have any migrations and just introspecting the database the first time

After running drizzle-kit introspect, you will get a schema.ts file and a migration folder with the initial migration. Along with that, drizzle-kit will add a first entry to journal with the status init indicating that the migration generated from introspect command

Then, you can proceed with a typical generate-and-migrate flow

When you run migrate on a database that already has all the tables from your schema, you need to run it with the drizzle-kit migrate --no-init flag, which will skip the init step. If you run it without this flag and get an error that such tables already exist, drizzle-kit will detect it and suggest you add this flag.

2. You are using introspect for just schema generation and don't need to handle migrations.

For such a case, we can add a flag that will instruct drizzle-kit to just create schema.ts without creating any migration folders or adding entries to the database.

It can be named something like this, but it's not a final name

drizzle-kit introspect --schema

3. You have an existing database in differents envs, but don't have any drizzle migrations yet

For example, you have production and development databases with the same set of data. The basic process would be for you to:

• Introspect from the development database. drizzle-kit will generate schema.ts and a migrations folder. The journal entity will have a type of migration: init. The kit will use this in the next steps.
• When you run migrate on a database that already has all the tables from your schema, you need to run it with the drizzle-kit migrate --no-init flag, which will skip the init step. If you run it without this flag and get an error that such tables already exist, drizzle-kit will detect it and suggest you add this flag.
• When you merge to prod, which already has all the tables, the same as dev, you will need to use the --no-init flag as well.
• For any new environments and databases, you can use just drizzle-kit migrate, and all the migrations together with init will be applied

Implicit Transactions Removal

We will remove implicit transactions for migration files, as there are cases where they are unnecessary or even impossible, such as with index concurrently. Instead, we will add BEGIN and COMMIT statements inside the generated SQL files, which you can remove if needed. We also won’t add BEGIN and COMMIT statements for operations that are not supported by the database. We are considering adding a flag to exclude transactions by default.

Handling Failed Migrations

If a migration fails and no transaction was applied, we will mark this migration as failed in the database. You will need to manually fix your schema before proceeding.

Switch to Identity Columns in Postgres

For the Postgres database, we will migrate from the serial type in the __drizzle_migrations table to identity columns, as recommended by the Postgres team. This change will also make it fully compatible with Xata and CockroachDB.

Proper Lock Mechanism

We will implement a proper lock mechanism to handle cases where several identical migrate() functions are executed simultaneously.

We will implement suggestions from this issue: #874

Summary

If there is anything you would like to add, change, or suggest, feel free to add a comment here. I am open to discussing everything before implementing this in the next release.

[teemingc]

Is there an official pattern for implementing multi-tenancy at the database level?

At work, we use TypeORM to create a multi-tenancy repository and entity. This involves extending the base repository and intercepting relational query methods to always add a where clause for the tenantId. This helps us scope requests to only operate for the tenant’s data. Similarly, we’re able to add permission checks at this level by adding a where clause for the createdBy field. We have a base entity that always includes these properties and we simply extend from it when creating new entities. If there’s something similar in drizzle, we’re hoping to be able to migrate for the improved performance and dx

[L-Mario564]

About strict migrations, will the next update provide some way to rewrite existing migrations so they're strict?

Also, not sure if it's within this discussion's scope but could be useful to provide docs on how to migrate existing serial columns to make them identity columns instead in Postgres. I assume some developers will look into this and Drizzle helping out in some way, even if just pointing in the right direction via documentation, could be useful.

[AndriiSherman]

Why would you need to make previous migrations strict?

[khuezy]

How about the drizzle-orm/libsql/migrator, I don't see a noInit flag in the options.

[AndriiSherman]

This post is about plans for the next release. It's not actually implemented yet

[killjoy2013]

@AndriiSherman
I personally would not want to see status in migrations table. How we use this table is to display the current migration level of a db. For instance, if you see MigrationA as the latest record, you know the exact migration level of the db. If in migration there's an error, that migration will not me applied & will not be in the migration table already. Moreover, a revert process should delete the migration from migration table. So, migration table should be kept simple. Adding status to it would be an overuse and creates unnecessary complications IMHO

[AndriiSherman]

That makes sense, but let's check this case:

1. You have 3 new migrations to be applied to your database.
2. 2 migrations will succeed, and 1 will fail in the middle of the 3rd migration.
3. We will create 2 entities in the database and mark the third as the one that failed/errored, so you can see from the database that you need to address it before moving forward. You will decide whether to mark it as applied, if it's fine, or to roll back manually and remove this entity from the database.

Without a status, you would see only 2 rows in the database and not the 3rd. But that's not accurate because a few statements from the 3rd migration were applied to the database before it failed, and you don't have this information reflected in your database. Consequently, you wouldn't know why there is a schema shift and what actually happened.

[jwatte]

it would be helpful to also have tools for:

• finding which filename generated a hash when applied (add filename as a column in __drizzle_migrations)
• finding the hash for a given filename (some drizzle or drizzle-kit option to calculate and print the hash from the CLI)

[pppdns]

Without a status, you would see only 2 rows in the database and not the 3rd

I believe a better approach is what Eloquent (Laravel) is doing with migrations.

Migrations are applied in batches. E.g. if there are 3 migrations waiting to be applied, they will be applied together in a "batch".
If any of the migrations fails in the batch, everything will be reverted automatically (even the successful migrations in the batch) because migrations run in a transaction.

I can still apply pending migrations one by one by specifying their id/name in a command line flag, so that only that one migration runs in the batch.

Batch id-s (which are auto-incrementing numbers) are included in the migrations table next to migration names, so we can see which migrations had been applied together in the same batch (e.g. 3 migrations have batchId 30 next to them).
This also makes it easy to roll back a complete batch together.

In Laravel, you can roll back a whole batch of migrations together, e.g. something like migrations rollback --batch=30.

In Laravel migrations every migration file has a down part, which is for rolling back the migration, so it's very easy to run up and down commands sequentially any number of times and get a reliable outcome. down migrations make it easy to test new migrations locally, rolling them back, applying them again, and only push changes to source control once we verify that everything works properly. I know there's a separate thread about rolling back migrations and that it's already in progress.

I know there are some limitations, e.g. truncate is not compatible with transactions in MySQL, but it's worth the compromise on edge cases in Laravel and works well

[killjoy2013]

@AndriiSherman
As far as I've seen other db migration systems, migration execution is a transactional process. If any one of them will throw, transaction will rollback. So, all those 3 migrations should be in the same transaction. This definitely make sense I believe.
In your scenario, for instance, would you like your CI/CD pipeline make any change of some of the migrations in a production db, while some others throwing? This would be terrible for a prod db.
I worked with Entity Framework & TypeORM in the past. In both, system you can be very relaxed. If any problem occurs when you run migrations in the pipeline, your pipeline will fail & your db will stay intact.
I can not imagine otherwise 😄

[AndriiSherman]

MySQL can't run DDL statements in transaction mode. How would that work for MySQL?

[killjoy2013]

you're right about MySQL.
Shall we have up() & down() methods? How about runing down methods in reverse order in MySQL?

[AndriiSherman]

this is a topic for another discussion we have: #1339

[jwatte]

There's also the problem that, if you have certain pending operations in a transaction, there's other operations that can't be run.
Doing DDL, DML, and index creation all in the same transaction won't work.
But we'd want to apply as much as possible from within a particular migration, transactionally.

[AdditionAddict]

would be nice to include the migration sql file name in the __drizzle_migrations table to see what has run at a glance

I assume the below is a bug (sqlite turso)?

[milon27-pyng]

yeah, this is very important. it's difficult which migration is which hash

[renchris]

@AndriiSherman

When you run migrate on a database that already has all the tables from your schema, you need to run it with the drizzle-kit migrate --no-init flag, which will skip the init step. If you run it without this flag and get an error that such tables already exist, drizzle-kit will detect it and suggest you add this flag.

Are these changes live now in the current drizzle-orm and drizzle-kit versions?

When I drizzle-kit generate a schema change of
ALTER TABLE `item` ADD `count` integer DEFAULT 0 NOT NULL;
and then run drizzle-kit migrate, I get the error that is about another unrelated table with
SqliteError: table `credential` already exists

Also I cannot find the --no-init flag, when I run pnpm drizzle-kit migrate --no-init I get Unrecognized options for command 'migrate': --no-init

[kylesloper]

Same here. --no-init flag unrecognized

[argenacho]

We are currently facing a challenge running migrations with multiple devs working in multiple feature branches, all of them integrating their work in the traditional "develop, stage, production" branches. Devs currently generate and run migrations locally, for their changes, and commit the migrations folder to git. This of course generates conflicts while merging the journals and snapshots folders. The integration branches are pulled by github actions to deploy to the corresponding environment, and is currently running the migrations that are stored on the migrations folder committed to git (which had to go a troublesome and manual merge conflict resolution process before landing on the repo). This, of course, is not ideal. I'd like for the CI process to introspect the changes of the environment's db, generate a migration against the schema file, and run it, all without any local persistence or "memory". Would this scenario be possible in the new version?

[dbussink]

    id serial,

I'd also recommend not using serial for MySQL. It doesn't actually create a primary key, but a unique key which is usually treated the same if it's the only unique non-nullable key, but it's imho better to be explicit:

CREATE TABLE __drizzle_migrations (
    id bigint auto_increment primary key,
    hash text not null,
    created_at bigint,
    status varchar(255)
)

It's also a common thing that people using Drizzle on PlanetScale end up with duplicate indexes when using serial, which then ends up getting a schema recommendation.

[dengfuping]

+1, serial data type maybe not compatibale with part of Database like PostgreSQL (#2183), SQLite (#1227) and OceanBase (34107050032) . Hope to replace serial with bigint + auto_increment to declare primary key in __drizzle_migrations table.

[syabro]

Any updates when? Now it's a blocker for me

[szamanr]

please reconsider naming of the status "rollbacked". it sounds like broken english. a better term would be "rolled_back".

[pkyeck]

would be interesting to know which of the aforementioned points made it into the 0.34.0 release?

[bondehagen]

@AndriiSherman What is the status on this?

[NomeQ]

+1 for Implicit Transactions Removal. Hoping there is an update soon.

[victor9000]

It would be great to track when a migration was applied with something like applied_at

[darcyrush]

Strict Migrations by Default

Thank you for explaining the logic behind this

[jaycoolslm]

I am also failing with the issue

drizzle-kit migrate --no-init

Unrecognized options for command 'migrate': --no-init

[hendrik-huels]

For Postgres, would be great if object owners defaulted to pg_database_owner to avoid migration issues across environments.