Migration management with Slonik

[dbstratta]

Hi all! :wave:

I would love to manage my migrations using Slonik.

What do you think of adding migration management?

Great library, by the way!

[gajus]

Can you define migration management requirements?

It is a very broad requirement at this point.

What benefits would it have to use Slonik over, e.g. https://github.com/sqitchers/sqitch?

Migration seems to be a separate issue from database driver, but I am happy to be proven wrong.

[dbstratta]

Of course. I was thinking more along the lines of what Knex.js does with migrations.

For example, we could do

slonik create-migration create_users_table

And it would create a file named something like 1551912556532_create_users_table.js in a migrations folder.

And we could write the migrations using the sql function. For example:

// 1551912556532_create_users_table.js

import { sql } from 'slonik'

export async function up(connection) {
  await connection.query(sql`
    CREATE TABLE users (
      id integer PRIMARY KEY,
      first_name varchar(64),
      last_name varchar(64),
      -- ...
    );
  `);
}

export async function down(connection) {
  await connection.query(sql`
    DROP TABLE users;
  `);
}

And then, we could do:

slonik migrate
slonik undo-migration
# etc.

The main advantage would be that we could write our migrations with sql, which I think would be very nice.

What do you think?

[ghost]

what are you all using for migrations right now? I really need a solution.

[Sharaal]

I using knex for migrations, SQL Tags for query building and pg as database driver. knex solves the part of migrations more or less pretty well.

I think migrations, query building and database driver are completely different topics. On the one side its good to have all of these in one solution (so I like the idea slonik provide migrations), but on the other side it's hard to have one thing fits in all use cases and like solutions supporting the possibility to switch one or multiple parts if a project has different requirements (e.g. what if a project has a MySQL database).

Feel free to have a look at a blog article I wrote recently about this topic (if I'm allowed to link here): http://blog.sharaal.de/2019/03/12/knex-vs-alternatives.html

[ghost]

@Sharaal Upon your suggestion, I used knex for migrations, but run into a case that I honestly don't understand how to solve in knex and much rather would learn the SQL way of doing it. Is there any other nice method for migrations?

[Sharaal]

What's the actual problem?

knex give you the environment for migrations by managing them. In the migrations scripts you can use the schema builder to create tables. But if you feel limited in something and need raw SQL you can always use knex.raw in the migrations scripts. Also it should be possible to initialize and use slonik or pg also. The only important thing is to return a promise in the .up and .down functions.

[MANTENN]

A nice addition to the documentation would be a few ways to abstract the code, and ways to implement it this would leave the sql as is, and enable developers to get started quickly especially for those who are coming from ORMs.

[MANTENN]

Perhaps using a folder structure like /tables and having an index file inside the tables directory to create them.

[Sharaal]

@MANTENN you mean all about the migrations? I would just add a link to https://knexjs.org/#Migrations to the documentation. It's not a topic currently in slonik.

[MANTENN]

@Sharaal correct.

[benjamine]

I like the idea of keeping slonik simple and focused, it seems migrations is a problem better solved by other packages (that could use slonik or not), here's an example of a lib who seems solve the migrations problem pretty well: https://www.npmjs.com/package/node-pg-migrate

[gilbert]

Not saying that slonik should add migrations, but to add an interesting perspective, Mammoth lets you specify your tables in JS and then generates migrations based on that.

[mmkal]

I created a package that uses slonik for this, because I didn't want to have to use a new client or duplicate my config for migrations: https://www.npmjs.com/package/@slonik/migrator

I'm using it in my projects but it's still in the early stages, feedback welcome!

[gajus]

I like the idea of keeping slonik simple and focused, it seems migrations is a problem better solved by other packages (that could use slonik or not),

Agreed.

node-pg-migrate or @slonik/migrator should already serve as a good starting point.

[gajus]

A recent comment I made on the subject:

I don’t have [a requirement for migration scripts] and struggle to understand why anyone would. Perhaps our development environments are very different. In my case, we have development environment where all the changes are being made ad-hoc (using CLI or whatever GUI tool to modify schema). Then when development environment needs to be deployed to production we identify the changes that were made (e.g. using https://www.postgrescompare.com/ or https://github.com/djrobstep/migra) and run the queries required to bring the two databases in sync (these tools generate migration queries for you). Regularly we delete development environment and re-create it by cloning production and removing sensitive data (e.g. PI customer date) using a hand-crafted procedure in the database.

– https://medium.com/@gajus/i-dont-have-this-problem-and-struggle-to-understand-why-anyone-would-fc743a0fd0f6

[Sharaal]

If there is a team of devs and everyone has a local environment. Additionally there are test, stage and at the end production environments in the cloud. So there are many environments you need to track which state the database currently have and which migrations are needed to run. Also never forget migrations are not only schema changes, there are also data migrations possible if its needed to update the data.

And that's the point a migration management solution shines: Track the state of the database and run all migrations which not already run in the past.

How the migrations are created, manually or with a comparing tool like you mentioned. If they are in native SQL or .js Scripts using a Schema Builder or anything else doesn't matter I think.

I also created a basic migration solution supports .sql files or .js files getting the client instance if you are interested in:

• Source: https://github.com/Sharaal/sql-pg/blob/master/bin/migrate.js
• Docu: https://github.com/Sharaal/sql-pg/wiki/Migrations (Just because I don't want to have always the heavy knex dependency for this)

[gajus]

If there is a team of devs and everyone has a local environment. [..]

Sounds like a colossal waste of time to ask every developer setup their own local dev environment and maintain it.

Make a clone of regular database, remove sensitive data, make database available for cloning to any developer, i.e. instead of working to resolve schema differences, nuke local/ developer instance database daily and populate with a new schema.

[Sharaal]

In our team it's just docker up and a npm run dev do the migration + app start. No waste of time needed.

What's the alternative? In our company it's a basic requirement every dev has a running local environment for developing and testing new features. Also having a dev environment in the cloud dependent teams can develop against it, stage for the QA to testing new releases and the production environment are needed.

But of course. Everyone should evaluate if a migration management solution is needed or not.

But creating a .sql file and putting in the create table query can be done very easily and fast, more is not needed to do to have all databases in sync in all environments.

[gajus]

In our team it's just docker up and a npm run dev do the migration + app start.

This assumes perfect world condition where developer made no changes to the local database schema.

What's the alternative?

We use cloud infrastructure to spin up as many instances as needed for testing. Literally hundreds of database instances come and go daily.

I struggle to understand why there is a "basic requirement" for every developer to have a locally hosted database instance, but that is outside of scope for this discussion.

I had a look at:

https://github.com/Sharaal/sql-pg/blob/master/bin/migrate.js

This simply (as Sharaal said) creates a table "migrations" to track last executed migrations and executes scripts that weren't executed before.

I would consider some of the more evolved options beforehand, e.g. https://github.com/sqitchers/sqitch

There is no need for this tool to be Node.js specific as there is nothing Node.js/ Slonik specific about this problem. The requirement is to simply document changes and execute queries that database in sync with some source database.

[Sharaal]

This assumes perfect world condition where developer made no changes to the local database schema.

Not outside of the migrations.

I struggle to understand why there is a "basic requirement" for every developer to have a locally hosted database instance, but that is outside of scope for this discussion.

To be able to work without (good) internet connection. We have a lot of remote developers and people working remotely during train traveling and co. But yes, that's another topic. :)

There is no need for this tool to be Node.js specific as there is nothing Node.js/ Slonik specific about this problem. The requirement is to simply document changes and execute queries that database in sync with some source database.

That's right. I just done it, because it's simple and I want to have the option to use the sql tags inside of migrations if it's needed.

[karlhorky]

@gajus Regardless of personal opinions, would you be open to adding to the readme a quick note about migrations with links to the other projects?

Maybe based on your comment above?

node-pg-migrate or @slonik/migrator should already serve as a good starting point

[karlhorky]

And to add my reasoning why I really like migrations:

Database migrations allow the team to track the (potentially ephemeral) changes to the state of the database in version control. Version control diffs + pull requests + the rest of the version control toolchain is in my opinion a better way to document, communicate and discuss those changes. Plus, then you can have your automated tests running as well, to verify that schema updates don't break anything.

[karlhorky]

Opened a quick PR to document the other recommended migration options here: https://github.com/gajus/slonik/pull/105

[gajus]

As a quick update, my views expressed in an earlier comment are no longer an accurate representation of the current stance on database migrations.

For the last two years we've been using Flyway and it hasn't let us down once. I've updated documentation to mention Flyway and other alternatives.

[karlhorky]

Nice, glad it's working for you!

For the last two years we've been using Flyway and it hasn't let us down once

Are there any open source repos that we can see the migrations in? Flyway sounds interesting and I'd be into seeing an example of how they do it.

[gajus]

Not ones that we maintain, but there appear to be a few on GitHub

https://www.google.com/search?q=site%3Agithub.com+flyway+example

That being said, I will publish an article about how we do it, since our setup is quite advanced and powerful.

Will update this thread when it is out.

[github-actions[bot]]

:tada: This issue has been resolved in version 31.2.5 :tada:

The release is available on:

• GitHub release
• npm package (@latest dist-tag)

Your semantic-release bot :package::rocket: