search

db-migrate / node-db-migrate

Database migration framework for node
Other
2.32k stars 360 forks source link

What's the best practice for adding db-migrate to a project with an existing database? #828

Open murderteeth opened 7 months ago

murderteeth commented 7 months ago

I'm submitting a...

Current behavior

db-migrate works great if you're greenfielding a new project. but my project is already live and has a database with existing schema and data. what's the recommended approach for adding db-migrate to this project?

related https://github.com/db-migrate/node-db-migrate/issues/604#issuecomment-1438955056

Expected behavior

I expect clear guidance\docs on integrating db-migrate with live projects that already have schema and data. Alternatively, I expect that if db-migrate isn't suitable for projects that already have schema and data, the docs\README make that clear instead.

Minimal reproduction of the problem with instructions

NA

What is the motivation / use case for changing the behavior?

NA

Environment


db-migrate version: latest

Additional information:
- Node version: LTS
- Platform:  linux

Others:
klansser commented 1 month ago

I've simply created innit-up.sql with current schema creation by CREATE TABLE IF NOT EXISTS. File innit-down.sql is empty and doesn't drop any table.

murderteeth commented 1 month ago

I've simply created innit-up.sql with current schema creation by CREATE TABLE IF NOT EXISTS. File innit-down.sql is empty and doesn't drop any table.

tu! yeah that's what I did too https://github.com/murderteeth/kong?tab=readme-ov-file#how-to-baseline-a-production-db-in-flight

wzrdtales commented 1 month ago

That is a thing you can do yes. I wrote before I started working on db-migrate itself, a tool to convert an existing db to migrations (https://github.com/wzrdtales/node-ultimate-migrate) . I do not really recommend using it, and it anyway only support mysql/mariadb, though. A SQL file is a way to start off an existing state, the pain of migrating to migrations is big, that is why in the best case you do it from the beginning. The tool I wrote back in the days in the frustration of having to get a legacy project into migrations.

The downside of your SQL approach. Db-migrate does support stateful atomic migrations in its v2 definition, those you couldn't really use without defining the existing schema. However, v2 migrations (https://github.com/db-migrate/node-db-migrate/issues/508, examples are in the text here https://github.com/db-migrate/node-db-migrate/issues/627#issue-451084676) are something we use internally everywhere, but it is not strictly released yet though, so that is probably not important to you right now. With v1 migrations which are just explicit and have no real intelligence in them your SQL file approach will not result in any problems.