Knex for posgres using default schema to create lock table which is conflicting for multiple projects

Question

I am using knex with postgres in my Node project, I have two different project using same database but different schemas. I can operate with different schemas while writing any query or doing migration using
knex.schema.withSchema , but knex is not creating lock table in those respective schemas. It uses default public schema in database to create lock tables. So first project would migrate fine but when I migrate second project it throws error- Error: The migration directory is corrupt, the following files are missing: 20210430124918_test.ts

which is the file for first project. Since it uses same public schema with same name for creating lock table it thinks file is missing.

I don't want to change the migration-lock file name in knexfile.ts, Is there a way to specify which schema to be used for creating lock tables for different projects.

Thanks in Advance.

I normally change the table name using --migrations-table-name (or in code). Not sure right now, but I think this impacts the lock table, too. Other than that, try setting the searchPath in the knexfile, or if that is not practical - you can always do ALTER ROLE app1 SET search_path = "app1"; -- provided that your apps use separate DB users. — Robert Kawecki

Mikael Lepistö Mikael Lepistö · Accepted Answer · 2021-07-28T19:47:46

For different projects you need to specify a separate knexfiles which tells the correct migration directory and correct schema name for migrations to use.

https://knexjs.org/#Migrations-API

Though would be a lot easier to just use completely separate databases for both projejcts.

Knex for posgres using default schema to create lock table which is conflicting for multiple projects

1 Answers