Migrations folder structure v3

[AlexBlokh]

Based on production feedback our migrations folder structure completely unusable due to git incompatibility
Current migrations folder structure looks like below

📦 <project root>
└ 📂 drizzle
  ├ 📂 meta
  │ ├ 📜 _journal.json
  │ ├ 📜 0000_snapshot.json
  │ └ 📜 0001_snapshot.json
  ├ 📜 0000_public_electro.sql
  └ 📜 0001_perpetual_sebastian_shaw.sql

We have a journal which keeps track of migration snapshots as a linked list and this way we've being proactively searching for race conditions. Race condition is when you've generated several migrations on different branches from one parent schema.

Originally we were considering this a no go, we thought that you should merge one, then pull to the second branch, revert current migrations and generate new ones. This might be true but turns out for a very minor fraction of the real world cases, it's not a bug, it's a conscious decision and positive scenario which we're blocking for developers and are going to fix urgently.

What would be the new API

1. We will revert back to 1 folder per 1 migration which will contain snapshot.json, migration.sql or migration.ts. Yes, in subsequent release we will add support for typescript migrations. We will also add migration.down.sql for sql down migrations and migration.ts files will have up and down migration functions.
   This also opens a way for you to easily drop unnecessary migration by just deleting a folder, which is a nice bonus and we will remove drop command from drizzle-kit.
2. We will drop the paradigm of snapshots as a linked list and will no longer check race conditions the way we did before, that's not a viable scenario for production development.
3. We will change default indexes prefixes to timestamps, that will lead to less confusions with concurrently created migrations
4. drizzle-kit migrate will apply lexicographically sorted migrations to the database and keep track of applied. Whenever there will be a migration to be applied which has an earlier timestamp than last one in applied migrations - we will throw an error, cause that's definitely an unexpected case for developers to solve. They will potentially regenerate migration and then apply to the database.

new folder structure will look this way:

📦 <project root>
└ 📂 drizzle
  ├ 📂 20240823160430_public_electro
  │ ├ 📜 snapshot.json
  │ └ 📜 migration.sql
  └ 📂 20240823160431_perpetual_sebastian_shaw 
    ├ 📜 snapshot.json
    └ 📜 migration.sql

UPD 1:
While updated folder structure does not introduce any git conflicts, the way Drizzle is generating migration is now a problem
When generating migration - Drizzle will get the newest snapshot of the schema available in the codebase at the time, consume TypeScript schema and compare those 2, based on the difference - it will generate migrations and new snapshot. The problem with current approach is latest json snapshot might now be a forked version with race condition. To overcome this problem we now have 3 options:

• rework existing flow to have database as a source of truth and force our users to provide dev database connection to generate migrations.
• implement merge logic for forked snapshots which will mimic migrations applied on the database in chronological order, I'm not investigating this option and hopefully it will work. We don't want to drastically pivot the way current migration flow works, we use it, we love it
• hybrid of the above two. We want to keep existing flow if it's possible and let you opt in for database aware migrations flow and have the ability to write js/ts migrations too

UPD 2:
After an extensive discussion, rnd and planning with @AndriiSherman - we came to the conclusion that neither of the ways you can do migrations is a silver bullet, either of those has pros and cons. Developers already use and love Drizzle way of generating and applying migrations, we just need to properly address downsides. Developers are now stuck in mostly the same ways they would be stuck with having shadowdb based migrations, we just need to properly communicate collision states and type out step by step guide for them to solve.

We've came to an agreement to focus on current migrations flow, provide proper feedback on all err situation and provide a separate way for developers to manage their migrations without drizzle auto generation, with either a TS/JS builder or purely in SQL

TODO:

• drizzle-kit check upgrade to handle collision checks with detailed explanations and step by step guide
• drizzle-kit check --db check should be able to have access to db migrations table to be aware migrations applied to the database, that would enrich the feedback for the developer
• Drizzle Kit GitHub(others) action, to run checks and provide easy way to fix conflicts in GitHub UI whenever it's possible without human intervention

[L-Mario564]

Since down migrations are going to be introduced, it would be useful to have a "reset" command that effectively just applies all down migrations. Just like in other ORMs like Prisma, this command would be only for development environments.

[AlexBlokh]

We've had some feedback and it does seem it's better to just reset database itself and run all up migrations

[AlexBlokh]

anyway I think that should be a part of rollback/down migrations discussion, this one is about migrations folder structure and blockers it has now

[ThomasAunvik]

@L-Mario564 The way i do it for development, is to just have everyone their own local migrations, and one migration for prod.

package.json

{
  "name": "@repo/db",
  "version": "0.0.0",
  "private": true,
  "scripts": {
    "db:up": "drizzle-kit up --config drizzle.config.local.ts",
    "db:generate": "drizzle-kit generate --config drizzle.config.local.ts",
    "db:drop": "drizzle-kit drop --config drizzle.config.local.ts",
    "db:migrate": "node --env-file=.env -r esbuild-register ./migrate.ts",
    "db:build": "node --env-file=.env -r esbuild-register ./build_data.ts",
    "db:prod:generate": "drizzle-kit generate --config drizzle.config.prod.ts",
    "db:prod:drop": "drizzle-kit drop --config drizzle.config.prod.ts",
    "db:prod:up": "drizzle-kit up --config drizzle.config.prod.ts",
    "db:prod:migrate": "node -r esbuild-register ./migrate.ts",
    "db:prod:build": "node --env-file=.env.prod -r esbuild-register ./build_data.ts",
    "db:push": "drizzle-kit push --config drizzle.config.local.ts",
    "db:studio": "drizzle-kit studio --verbose --config drizzle.config.local.ts"
  }
}

drizzle.config.local.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./schema/tables.ts",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DB_URL ?? "",
  },
  out: "./migrations/local",
  verbose: true,
  strict: true,
});

drizzle.config.prod.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./schema/tables.ts",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DB_URL ?? "",
  },
  out: "./migrations",
  verbose: true,
  strict: true,
});

And add migrations/local to .gitignore

[budivoogt]

Thanks for sharing this. Exactly what I needed to use migrations locally and in staging/production.

[PDieE]

I hope to add default row migration

[PDieE]

and COMMENT also

[AlexBlokh]

can you elaborate on those two?

[chitraanshpopli]

can you elaborate on those two?

For the COMMENT I think they mean the COMMENT ON COLUMN and COMMENT ON TABLE statements in DBs like postgres.

[mokevnin]

It would be nice to have auto-reversible migrations like in Rails or Django

https://guides.rubyonrails.org/active_record_migrations.html#using-the-change-method

[AlexBlokh]

that should be a part of a different discussion - #1339

[kenn]

Two things:

• Offer serial numbering as an option (and less noisy option should be the default)
• Timestamp format

A lot of projects are run by solo or small teams where migration conflicts are very rare. 14 digits are overkill for them.

For this reason, Rails offers a config option to disable timestamp-based migration and keep using 001, 002, etc.

config.active_record.timestamped_migrations = false

At a later time, as conflict concerns grow as your team size, you can just turn on the timestamp, and the sort order would still be maintained. (unless you reach 2024 migrations by then)

0001_first
0002_second
20240823160430_our_team_is_now_bigger

Also, I find timestamped migration names are poorly designed in ORMs, including Prisma. There's a balance in timestamp nomenclature: ISO-8601 exists because dashes between components are more human friendly.

20240823160430_public_electro
20240823-160430_public_electro
2024-08-23-160430_public_electro
2024-08-23-16-04-30_public_electro

To me, 2024-08-23-160430 wouldn't look too bad.

Or better yet, why not break down into folders, when you have hundreds of migrations?

2024/08-23-160430_public_electro
     08-23-160431_perpetual_sebastian_shaw

I'd put the food for thoughts on the table.

[kenn]

Did you never have multiple devs pushing two of the same numbered migrations?

I didn't say "never", I said ~10 times in 18 years. :)

I think it depends on maturity of the team and nature of the product.

I'd just say it's better to have a coordination process for rebase / merge that includes how to deal with migration conflicts anyway — if you have data migration (INSERTs/UPDATEs/DELETEs) with data dependency, there will be ordering dependency anyway, and timestamped migrations won't solve that problem.

It's like sweeping real problems under the carpet. Sure, you don't get merge conflicts with timestamps, but you still have things to consider carefully (anything beyond DDL, data dependency changes) before merge. I'd rather let the conflicts happen, there's a good chance that enforces double checking.

[AlexBlokh]

we will no longer have _journal.json, if we're going to have indexes as prefixes - we will lose the ability to have dates of the migrations generated, which we currently store in db and we plan to use to plot history of migrations if/when collisions are introduced

[AlexBlokh]

it's easy to have indexes, we already have them, yet it's a burden to maintain
and we already have teams who switch away from drizzle migrations because of the unnecessary collisions

[AlexDaniel]

@AlexBlokh wait, you're not planning to store dependencies of the migrations? I'm not sure that can work… 🤔 How do you know in which order the migrations need to be applied? (see also #2448)

[ThomasAunvik]

Imo, if there is a migration already in the main branch (for example migration nr13), the merging branch needs to drop their local migration, and generate a new migration which becomes (migration nr14).

And, migration history isn't important until you are dealing with production and staging migrations.

For local migrations if you want, it would be better to just separate migrations for local dev to gitignore. And generate migrations for pdo in git when you really need to.

[Vinlock]

I'm really looking forward to it being this:

📦 <project root>
└ 📂 drizzle
  ├ 📂 20240823160430_public_electro
  │ ├ 📜 snapshot.json
  │ └ 📜 migration.sql
  └ 📂 20240823160431_perpetual_sebastian_shaw 
    ├ 📜 snapshot.json
    └ 📜 migration.sql

[AlexBlokh]

that's already implemented, I'm currently working on collisions detection and proper prompts for the dev to resolve those
we plan to have a github flow for that too

[nihaux]

Hello and let me start by saying, I've tried drizzle for the first time on the new project and so far liking it very much.
So thx for the hard work, first and foremost.

One thing that i just discovered and bothers me though is working with migrations, multiple branches and the merge of it.
I feel like I must have missed something important or am doing something very wrong because as it is, it seems like a nightmare.

Let me give you an example to illustrate my point:

Master contains migrations up to 30.
Branch A contains migration 31, 32.
Branch B contains migration 31, 32, 33, 34

A is merged in master => ok.
Master now contains migrations up to 32.

Now the problem, you want to update branch B by rebasing on master.

• commit with migration B(31) is applied
  -> rename sql file B(31) to 33
  -> fix journal to add migration now 33 (make sure idx and tag attributes are now 33*)
  -> revert changes to snapshot 31
  -> create new file snapshot 33, based on snapshot 32
  -> manually! import diff from snapshot B(31) into newly created snapshot 34
  -> rebase --continue

• commit with migration B(32) is applied =>same thing we did for B(31)

• commit with migration B(33) is applied => same thing we did for B(31)

• commit with migration B(34) is applied => same thing we did for B(31)

So this is a real example I just did on my code and it very time consuming and very very error prone due to human intervention.

I feel like I must have missed something completly, how are other people handling this use case ?

If we had some down migrations I could have downed the migrations on B branch, remove the snapshots, journal entries and just kept the schema.ts then regenerated new snapshots from there, which would have made it less error prone.

For context I am coming from other migration systems that considers the database as the source of truth so you don't have this problem of linked snapshot to fix.

Please enlighten me, am I taking crazy pills here ? What did i miss ?

[nihaux]

Hello @AlexBlokh, sry to ping, but if you could take a quick look at my use case and tell me if I missed anything or if there is any workaround to make this task less painfull, that would be greatly appreciated.

[AlexBlokh]

that's exactly the problem we will fix with this release

you will still have to redo 33 and 34 migrations, but it will be all explained inline with Drizzle Kit and in the docs

[AlexDaniel]

It breaks my heart to move from indexed migration prefixes, but they don't really work at all when you have 2+ team members

@AlexBlokh they actually do work beautifully, except that in Drizzle there's just 1 migration folder for the entire project. This is why conflicts happen all the time.

Please check out Django, which resolves this in the vast majority of cases. Here's how it works:

A single projects consists of different apps (let's call them “modules” here for simplicity) and each module has its own “models” file (in Drizzle's terms it's schema.ts) and a separate migration/ folder. For example:

modules/
├── users/
│   ├── schema.ts
│   ├── migrations/
│   │   ├── 0001_initial
│   │   ├── 0002_add_created_at
├── articles/
│   ├── schema.ts
│   ├── migrations/
│   │   ├── 0001_initial
│   │   └── ...
├── … and so on

This way, a developer working on the new articles feature is not conflicting with another developer who is working on account management. Each schema file can do imports from other modules, that is alright. And there can be dependencies between the modules, which is also fine.

Note that I'm already using this kind of directory structure in drizzle for schema files, simply because it's better for code organization – you can have code that is related to some big feature next to its schema. All I need is drizzle to be able to generate migration files next to the schema files.

Note that this is roughly how Django works (and has worked for years), and while conflicts can still happen sometimes (when developers change the schema for the same feature), for the most part they're completely eliminated.

[AlexDaniel]

I'd love to know why people are downvoting, given that Django's way of handling migrations is production-ready and is very effective.

[rkofman]

Can't speak for everyone; but working on a brand new app with a single table, I'm already running into awkward migration collisions with the two developers that are working next to me. This is a point of friction that I've literally never experienced before in a decade of working with database migrations on much larger teams.

Given this, I don't believe that table-specific migrations are enough of a salve.

[AlexBlokh]

we not going to have django like migrations, that's for sure
though I'm currently working on commutative migrations, which will let you change schema on different branches and if they don't collide - it will let you merge those and apply changes to the database and if the do collide - it will let you know the fork point and how to solve

[ssahillppatell]

Will there be any command for moving the current folder structure to v2 from v1? Something like drizzle-kit mirgations-structure-v2

[AlexBlokh]

we already have one and it's called drizzle-kit up and it already supports migration for migration folder, so it will be automatic for you

[Laberbear]

Hello there, I see there's been already quite a bit of discussions about this, so sorry if this question was already answered.

We'd love to use Drizzle in our project, but the absence of Typescript/JavaScript migrations makes it completely impossible.
Is there already an agreement about when this will be implemented?

Just want to know whether it makes sense to wait or continue to build up our app in Sequelize.

[AlexBlokh]

this will be implemented right after migrations v3, I will supposedly start on that next week

[Laberbear]

Sounds good, thanks a lot

[rkofman]

I have a question about the new folder structure:

📦
└ 📂 drizzle
├ 📂 20240823160430_public_electro
│ ├ 📜 snapshot.json
│ └ 📜 migration.sql
└ 📂 20240823160431_perpetual_sebastian_shaw
├ 📜 snapshot.json
└ 📜 migration.sql

If I understand this correctly, the snapshot.json will still be a sticking point in resolving conflicts between developers that are working in parallel:

Developer1 forks from the main branch and creates 20240823160430_public_electro and introduces a new field foo. Meanwhile, Developer2 also forks from main and creates 20240823160431_perpetual_sebastian_shaw which introduces the new field bar. This means that Developer1's snapshot.json will include foo but not bar. Developer2's will have the opposite inclusion (bar but not foo).

When they both merge their code, neither snapshot.json would contain the true state of the database (applying both migrations)! This means that Developer3 trying to run drizzle-kit generate after both branches are merged will not have a good starting snapshot for their next delta.

Am I misunderstanding how this is intended to work?

[rkofman]

I missed the following, which is seemingly intended to solve the problem:

Whenever there will be a migration to be applied which has an earlier timestamp than last one in applied migrations - we will throw an error, cause that's definitely an unexpected case for developers to solve. They will potentially regenerate migration and then apply to the database.

But since this forces a re-generation of the migration, I don't see how it's much better than the current state -- which already forces deletion and re-generation of the "losing" migration by whichever developer happens to merge last.

I would ideally like to request a more self-correcting process: every unapplied migration gets applied, sorted by timestamp. This means that independent migrations don't care about each other. Whereas dependent migrations get applied in order. (Dependent ==> developer had any chance to base their code on the existing migration)

The main wrinkle being that "snapshot" problem I described above -- which I believe is solve-able.

[AlexBlokh]

#2832 (reply in thread)

[rkofman]

Commutative migrations are exactly what I'm asking for! I'm curious what the planned data model to support them is. Happy to look at code or docs, whatever is easiest! Even happy to contribute to the implementation work if that would be useful (I suspect not as it's a core feature that's already on your roadmap).

Context: I ported Drizzle's migrator code in my project* because I needed to be able to run the code in a WebView context. If I know where Drizzle will land, I can update my own code to be compatible ahead of time :)

* the port already applies all unapplied migrations; but is running into the snapshot.json issue mentioned above.

[rkofman]

@AlexBlokh Just checking if you're up for sharing details of how commutative migrations will work (e.g. new structure that supports them).

[jamesarosen]

I'd like a way to store the latest version of a function, stored procedure, or other SQL primitive in a file so the team gets useful diffs on pull requests. The challenge is that if I reference that central file from a migration and then change the file, the migration is no longer a snapshot.

One solution would be to have the slowly-changing SQL in one file, then when you run a migration that references it, it inherits a snapshot and generates a digest to prevent drift. Running the migration applies the snapshot.

This may exist elsewhere, but I haven't found it.

[AlexDaniel]

@jamesarosen yeah, same, we'd also like to have it. We immediately adopted materialized views once they landed, which covers most of what we do, but having something for other primitives would be great.

[jamesarosen]

Another option would be to have drizzle emit the current schema in .sql format instead of in .json. That way, you could at least review the resulting schema changes in the pull request. I don't like this as much as having the slowly-changing function files be the source of truth, but it will definitely solve the core problem.

[kravetsone]

Looks like bunx drizzle-kit export