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

[PDieE]

I hope to add default row migration

[PDieE]

and COMMENT also

[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 ?

[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.