what tools do you recommend for database migration ?

[francelwebdev]

what tools do you recommend for database migration ?

[lroal]

Personally, I prefer to roll my own - and not use any external tools.
I manually create the migration scripts and name them something like 001_initial_schema.sql and 002_add_users_table.sql.

Then I create a small node program or bash script that runs each script in a transaction.
To keep track of which script is run, you need to add db_version table with the last run script to check against.

CREATE TABLE IF NOT EXISTS db_version (
            id SERIAL PRIMARY KEY,
            script_name VARCHAR(255) NOT NULL,
            executed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

I will come back with a complete running example later this week or early next week.
Maybe I should add it to the documentation as well.

If you prefer an external tool, you could use Liquibase or Flyway.
However, these tools require Java. I use Liquibase at my current job. But this is mainly because we are in a mixed environment with Java already. We would probably not use it in a pure node environment.

[lroal]

I promised to give you a full example. But then I got the flu or something and was unable to work. I will come back to you.

[francelwebdev]

other simple tools ?

[lroal]

There is something called db-migrate, but you still have to write manual sql.

I have never tested it. But it has a reasonable amount of downloads and latest release on npm was 6 months ago.

[francelwebdev]

Hi @lroal ,
what do your think about this migration tool (https://github.com/shahriarKabir44/migratify) for mysql ?

[lroal]

The migration tool you're looking at might not be the best choice because:

• It uses local log files to track migrations, which can get messy if you're working in a team or on different machines. It also means that it can only support one single database (excluding separate environments for development, staging and production)
• Very few people are using it, so there's not much proof it works well or support if you run into issues.
• There's no github actions for automatic testing. Without tests, it's hard to trust that the tool won't break something.
• There are no changelog or issues

[lroal]

@francelwebdev What if you could generate a prisma schema via command cli ?
Then you could use prisma for the migrations.
At least as a temporary solution.
prisma/prisma#24290

[andreasbhansen]

That would be an interesting combination, have you given this any more thought, @lroal ?

[lroal]

No, but I don't see any showstoppers. It is just a matter of priorities. There are some things I'd like to do first, like supporting other cloud databases like turso, xata, planetscale and neon.
Ideally, I would like to have builtin migrations in Orange, but it is not the right time to do that now. It is a long term goal.