Finalize sql support

[littlewhywhat]

• provide docs
• verify that sql migration in fact works as expected (see discussion https://github.com/salsita/node-pg-migrate/pull/530#issuecomment-564651446)

[littlewhywhat]

Also - do we actually need to support this?

[Shinigami92]

I also don't directly know why we should support this. TypeScript has already type support for various helpers from us. And if someone just want plain SQL migrations, I think there are other tools that could do that. So we could declare it as out of scope and add an alternative tool to the docs :thinking:

[moltar]

I think we could refactor the project to work with pluggable providers. A provider interface will provide an array of migrations. How this provider gets those migrations is up to the implementation.

This would allow:

• Load migrations from .ts files from a dir
• Load migrations from .sql files from a dir
• Manually supply an array of migration instances (we have this use case; see: #651)

Further, decoupling each provider into a separate package. Right now, because this main package uses dynamic requires, some tools like esbuild and other bundlers complain about it.

• @node-pg-migrate/runner
• @node-pg-migrate/provider-ts
• @node-pg-migrate/provider-sql

And node-pg-migrate simply combines @node-pg-migrate/runner with default provider @node-pg-migrate/provider-ts.

[Shinigami92]

@moltar What would be the benefit of that?

[moltar]

What would be the benefit of that?

Of which part of my proposal?

[Shinigami92]

refactor the project to work with pluggable providers

[moltar]

Benefits:

• End users are able to implement their own providers, e.g. *.sql, if the core does not want to support it
• Decouples runner from the obtainment of migrations, which helps with the dynamic require issue, describe above

[tbo]

Also - do we actually need to support this?

I would very much like to see it supported, because:

• Even if the focus is on typescript some use cases are easier to handle in pure SQL files (see https://github.com/salsita/node-pg-migrate/issues/275#issuecomment-400278921)
• The alternatives AFAIK don't match the feature set, documentation & popularity of this project

[theoephraim]

The main use case (and why it was originally supported) is to handle an initial schema dump as your first migration. This is super useful if you're migrating an existing project over to this tool. Additionally there are definitely awkward advanced use cases where it may just be a lot cleaner to use pure sql.

Even if you're not moving an existing project over, it can be very useful to start a project without using migrations. For example my usual workflow for new projects is to use sequelize's "sync" features to automatically sync the db schema to my models until the code is somewhat stable and I'm deploying to prod for the first time. At that point, the extra overhead of writing migrations rather than just wiping the db and resyncing becomes worth it. I do a sql schema-only dump as my first migration, and then I'm good to go.

[nabe1653]

I found this library for this purpose. I desired SQL file style migration tool on npm package like migr8 on Ruby, Other node migration tools gives ORM style or run SQL on node code. It is usefull too but some developer required to use as SQL. SQL can be written by pure DB developer and able to use about dumped file or other SQL generator tools output file. Specially, some project does not allow use ORM on prj rule for some reasons. SQL file is able to continue using even if tool support is finished.

Some web topics/stackoverflow said this lib can use migration-file-language=sql option but document dose not have...

Almost simple DB-migration tools used Java or Ruby. I think because API server was written by Java or Ruby. Today many user will use DB with api-framework by node. I think other developer wished simple sql node migration tool too.

[mrl5]

hello, just FYI the "hidden feature" of raw sql migrations is the reason why I use node-pg-migrate :) more info in https://github.com/salsita/node-pg-migrate/discussions/937

please don't remove this feature. I actually think that it should be documented - you might gain more users thanks to that