Compatibility with Slonik v33

[aust1nz]

The Slonik 33 release removes the sql method in favor of sql.type and sql.fragment.

This prevents the @slonik/migrator CLI from working effectively with a

Error: (0 , slonik_1.sql) is not a function

message.

I'm able to monkey-patch the slonik/migrator file and replace instances of sql with sql.unsafe to get things running against Slonik 33, but I'm not really sure what the fix would be to ensure that recent versions of Slonik and earlier versions of Slonik are working effectively.

[mmkal]

Yes, honestly I think this was an completely unnecessary breaking change. The way I see it, slonik 29 was abandoned and replaced by a weird, sql-client-with-lots-of-zod-boilerplate. I would recommend not updating for now unless you happen to want to go along with all of Gajus's whims. Having said that, at some point I'll see about updating in a backwards compatible way. Maybe by making a @slonik/compat library which wraps slonik and uses a sql.unsafe || sql expression with a type cast. No ETA on that, but I'll aim to do something before the end of the year. So yeah - for now either stay on a lower version or stick with you patch.

Having said all that, the migrator and typegen library will still definitely be maintained and get updates, I just haven't figured out exactly what the updates looks like yet.

[aust1nz]

Thanks! I actually enjoy the Zod typing for query results that Slonik 30+ implement, but totally understand the challenge of updating a library like this to accommodate a recent breaking change. I'm a novice to this general library space, but your wrapper functionality seems like it would solve the issue.

The migrator tool is lightweight and really fulfills the need perfectly, thanks for the work!

[pwadmore-ea]

I'd really appreciate an update on any plans to address this issue please.

While the migrator seems really ease to use and lightweight I have concerns about prolonged dependency on an ageing version of Slonik.

[janpaepke]

Unfortunately this isn't a "minor rewrite", the switch in slonik from "trust me, those are the return types" to "make sure this is the return type", isn't only a conceptual one.

Given how recent this change is and how frequent the slonik API changed (3 major versions within the last quarter alone), I can very much understand @mmkal's tendency to wait a bit to A) see what gajus settles on and B) consider the best option for implementation.

That being said, if this feature is important to you, I would recommend contributing: Propose mode for implementation and/or create a pull request.

[olalonde]

FWIW I hit this bug, read this comment thread and decided to give a go to https://github.com/prisma/prisma instead.

[akutruff]

Wow, so I've been TRYING to adopt slonik, and please correct me if I'm wrong but there presently is no end-to-end golden path in the ecosystem for migrations. Given this thread, it sounds like this project isn't going to be moving forward until there is a philosophical reckoning.

I would be fine using another migration tool in the node world that aims to be purely SQL driven, but every other migration tool seems to either be lagging in updates as well, or a jump into paying for a commercial tool like Flyway. Sqitch seems like it would just get the job done, but there's no opportunity to run JavaScript during the migrations which is rough if you want to do something custom along the way. Using knex for migrations would mean adopting two query builders for the same project. Prisma appears to be a major minefield.

It's a shame.

[aust1nz]

Wow, so I've been TRYING to adopt slonik, and please correct me if I'm wrong but there presently is no end-to-end golden path in the ecosystem for migrations. Given this thread, it sounds like this project isn't going to be moving forward until there is a philosophical reckoning.

Just to be clear, slonik-tools is not part of the slonik package.

While the tool is not v33 compatible, it's a pretty simple library. Just grab the code in the migrator index and template, install the required dependencies, and switch sql to sql.unsafe where necessary.

If you can figure out a way to do the above while keeping the migrator working for Slonik <33, I suspect the maintainer would accept a PR.

[akutruff]

I appreciate the response but your suggestion crosses my risk aversion threshold.

Just to be clear, slonik-tools is not part of the slonik package.

Very aware. This project is mentioned in the slonik docs though. This project is mentioned in blog posts when discussing the merits of slonik. DB migrations have to be considered in the same breath as ORM/query builder strategy.

While the tool is not v33 compatible, it's a pretty simple library. Just grab the code in the migrator index and template, install the required dependencies, and switch sql to sql.unsafe where necessary.

I'm very reluctant to take on ANY dependency where this is the workflow for handling changes. Imagine if this advice were across all the NPM packages in a project. Scary. I have no idea what the ramifications of this suggestion is without basically maintaining my own fork.

As far as pull requests...

#411 leads to #407

Non-trivial to say the least. Again, I'm a new user trying to adopt slonik, and there's no way I'd try to attempt to unwind this without prior investment and understanding of the issues at play.

[aust1nz]

Non-trivial to say the least. Again, I'm a new user trying to adopt slonik, and there's no way I'd try to attempt to unwind this without prior investment and understanding of the issues at play.

Yep, you have to make your own decisions and I'm a consumer of these tools rather than a maintainer, so I'm certainly not invested in pushing you one way or another!

For what it's worth, I've had the ~500 modified lines of the migrator library as part of my app/src since opening this ticket in November, and haven't needed to look back at it once, though it's always possible future Slonik changes force additional updates.

Like you said, the other node options that I know of that have a migration tool built in are knex and Prisma. They both have their own strengths but writing plain SQL isn't key among them.

[latobibor]

To be on the philosophical side of this thing, I am, just like @aust1nz very aware of knex and Prisma and damn they are pain to work with the moment you have to write any complex queries. You are not just back at square 1 writing a "raw" SQL, you then also need to convince your colleagues not to make 5+ queries to the db, because "that's what ORM gives you safe and easy". Which is a constant friction... I'm not speaking about the migration tool.

Slonik on the other hand is the right level of abstraction. But there is a huge problem with it: slonik should not have been ever a thing needed to be written. A type-safe official Postgres client enabling complex queries, shipped with a migration tool is the thing we are missing.

Which then raises the question: why is still below the database world to provide its ports? We are interacting with the DB and then all sorts of clunks and gimmicks must be made on top of them for every project using the DB. Talk about duplicate effort...! Why is this not one product?
"Hello, I would like to query complex relational data that works natively with TypeScript and Javascript!"
"Here it is, sir."

If I had the funding I would totally see this a startup. The NoSQL world got one half right, the SQL world had the other half. A DB that ships its own "Slonik", developed together with carefully planned releases and mature support is what we need.

[oguimbal]

For those interested, here is a dirty little script that you can run as post-install that monkey patches Slonik migrator to fix that:

import fs from 'fs';

const content = fs.readFileSync('./node_modules/@slonik/migrator/dist/index.js', 'utf8');

if (content.includes('sloniksql')) {
  console.log('👍 @slonik/migrator already patched');
} else {
  let newContent = content.replaceAll('slonik_1.sql', 'sloniksql');

  newContent = newContent.replace(
    'class SlonikMigrator',
    `
// monkey patch of slonik migrator
// see  https://github.com/mmkal/slonik-tools/issues/417
const sloniksql = slonik_1.sql.unsafe;
Object.assign(sloniksql, slonik_1.sql);

class SlonikMigrator`,
  );

  fs.writeFileSync('./node_modules/@slonik/migrator/dist/index.js', newContent);

  console.log('👍 Patched @slonik/migrator');
}

warning: it assigns everything that you can find on sql to sql.unsafe so it is not exactly transparent (but it should work)

[nathanchapman]

@oguimbal you should be able to use patch-package for a more reliable patching solution

[aust1nz]

Also, for others hitting this thread, if you're looking for similar functionality to slonik-migrate, you could consider node-pg-migrate. It's powered by PG, just like Slonik is, but doesn't rely on a specific Slonik version.

It's not well-documented, but node-pg-migrate does support SQL migrations, not only the migration DSL. Here's a blog that outlines the SQL setup.

The format isn't exactly the same here, so if you wanted to move from @slonik/migrator to that tool you'd have to update your migration table and file format, but it's a decent option that doesn't have the compatibility issues with Slonik.

[mmkal]

Update on this: there have been four breaking releases of slonik since this issue was created: https://github.com/gajus/slonik/releases/tag/v37.0.0

Having said that, I would be willing to accept a PR that updates the slonik version in this repo. I agree with @latobibor that it's crazy there's this patchwork of products. Sadly slonik ain't it, because it's too hard to keep up with its gung-ho approach to changes (37 major versions of a raw sql client...) - but for now it's still a viable option, at least. So if someone wants to open a PR, I will review, test, merge and publish.

[nathanchapman]

@mmkal five* breaking changes lol
gajus/slonik#483
💀

[eioo]

@oguimbal you should be able to use patch-package for a more reliable patching solution

Here is the diff that solved my problem:

diff --git a/node_modules/@slonik/migrator/dist/index.js b/node_modules/@slonik/migrator/dist/index.js
index 6153f5c..bad4eab 100644
--- a/node_modules/@slonik/migrator/dist/index.js
+++ b/node_modules/@slonik/migrator/dist/index.js
@@ -9,12 +9,17 @@ const slonik_1 = require("slonik");
 const path = require("path");
 const ts_command_line_1 = require("@rushstack/ts-command-line");
 const templates = require("./templates");
+// monkey patch of slonik migrator
+// see  https://github.com/mmkal/slonik-tools/issues/417
+const sloniksql = slonik_1.sql.unsafe;
+Object.assign(sloniksql, slonik_1.sql);
+
 class SlonikMigrator extends umzug.Umzug {
     constructor(slonikMigratorOptions) {
         super({
             context: () => ({
                 parent: slonikMigratorOptions.slonik,
-                sql: slonik_1.sql,
+                sql: sloniksql,
                 connection: null, // connection function is added later by storage setup.
             }),
             migrations: () => ({
@@ -63,7 +68,7 @@ class SlonikMigrator extends umzug.Umzug {
     }
     migrationTableNameIdentifier() {
         const table = this.slonikMigratorOptions.migrationTableName;
-        return slonik_1.sql.identifier(Array.isArray(table) ? table : [table]);
+        return sloniksql.identifier(Array.isArray(table) ? table : [table]);
     }
     template(filepath) {
         if (filepath.endsWith('.ts')) {
@@ -97,12 +102,12 @@ class SlonikMigrator extends umzug.Umzug {
         return {
             name: params.name,
             path: params.path,
-            up: async (upParams) => migrationModule.up({ slonik, sql: slonik_1.sql, ...upParams }),
-            down: async (downParams) => { var _a; return (_a = migrationModule.down) === null || _a === void 0 ? void 0 : _a.call(migrationModule, { slonik, sql: slonik_1.sql, ...downParams }); },
+            up: async (upParams) => migrationModule.up({ slonik, sql: sloniksql, ...upParams }),
+            down: async (downParams) => { var _a; return (_a = migrationModule.down) === null || _a === void 0 ? void 0 : _a.call(migrationModule, { slonik, sql: sloniksql, ...downParams }); },
         };
     }
     async getOrCreateMigrationsTable(context) {
-        await context.parent.query((0, slonik_1.sql) `
+        await context.parent.query((0, sloniksql) `
       create table if not exists ${this.migrationTableNameIdentifier()}(
         name text primary key,
         hash text not null,
@@ -197,7 +202,7 @@ class SlonikMigrator extends umzug.Umzug {
      */
     async executedInfos(context) {
         await this.getOrCreateMigrationsTable(context);
-        const migrations = await context.parent.any((0, slonik_1.sql) `select name, hash from ${this.migrationTableNameIdentifier()}`);
+        const migrations = await context.parent.any((0, sloniksql) `select name, hash from ${this.migrationTableNameIdentifier()}`);
         return migrations.map(r => {
             const name = r.name;
             return {
@@ -208,19 +213,19 @@ class SlonikMigrator extends umzug.Umzug {
         });
     }
     async logMigration({ name, context }) {
-        await context.connection.query((0, slonik_1.sql) `
+        await context.connection.query((0, sloniksql) `
       insert into ${this.migrationTableNameIdentifier()}(name, hash)
       values (${name}, ${this.hash(name)})
     `);
     }
     async unlogMigration({ name, context }) {
-        await context.connection.query((0, slonik_1.sql) `
+        await context.connection.query((0, sloniksql) `
       delete from ${this.migrationTableNameIdentifier()}
       where name = ${name}
     `);
     }
     async repairMigration({ name, hash, context }) {
-        await context.connection.query((0, slonik_1.sql) `
+        await context.connection.query((0, sloniksql) `
       update ${this.migrationTableNameIdentifier()}
       set hash = ${hash}
       where name = ${name}

This issue body was partially generated by patch-package.

[bgschiller]

I also wanted to use the migrator package with a newer version of slonik. I made some changes and published @bgschiller/slonik-migrator to npm. The code is up at bgschiller/slonik-migrator.

@mmkal , let me know if you'd welcome an MR with these changes; I'd be happy to put one together. I did have to drop one back-compat test, but it's possible you'd know how to fix it.

[mmkal]

@bgschiller sure, I'll try to incorporate. It may affect type safety too, but that might be acceptable . Note that since we started tracking the breaking changes that have gone into slonik on this issue, Gajus said he wants to drop sql.unsafe...

I have a new a pg client + migrator in the works, which has the same query API as slonik (compatible with pretty much all slonik versions since 29, including zod type parsing support), but uses pg-promise as a driver since that seems more reliable as an OSS library. The migrator is pretty much a straight port.

But until that's ready to publish, yes please to the pull request!