I switched from a PostgreSQL server to newer version by dumping all the data. Everything seems to be in place correctly, but Knex.js fails to understand the migrations.
All migrations are listed correctly in knex_migrations table and there's not lock present in knex_migrations_lock. Yet running knex migrate:list will cause the following error
error: create table "knex_migrations" ("id" serial primary key, "name" varchar(255), "batch" integer, "migration_time" timestamptz) - relation "knex_migrations" already exists
I've run the same command against both the old and new databases and the debug log shows the following differences. Name of the tables and schemas should be default.
Old
Using environment: local
knex:client acquired connection from pool: __knexUid1 +0ms
knex:query select * from information_schema.tables where table_name = ? and table_schema = current_schema() undefined +0ms
knex:bindings [ 'knex_migrations' ] undefined +0ms
knex:client releasing connection to pool: __knexUid1 +14ms
knex:client acquired connection from pool: __knexUid1 +0ms
knex:query select * from information_schema.tables where table_name = ? and table_schema = current_schema() undefined +13ms
knex:bindings [ 'knex_migrations_lock' ] undefined +13ms
knex:client releasing connection to pool: __knexUid1 +4ms
knex:client acquired connection from pool: __knexUid1 +0ms
knex:query select * from "knex_migrations_lock" undefined +6ms
knex:bindings [] undefined +6ms
knex:client releasing connection to pool: __knexUid1 +3ms
knex:client acquired connection from pool: __knexUid1 +0ms
knex:query select "name" from "knex_migrations" order by "id" asc undefined +2ms
knex:bindings [] undefined +2ms
knex:client releasing connection to pool: __knexUid1 +2ms
Found 27 Completed Migration file/files.
-- list of migrations omitted
No Pending Migration files Found.
New
Using environment: local
knex:client acquired connection from pool: __knexUid1 +0ms
knex:query select * from information_schema.tables where table_name = ? and table_schema = current_schema() undefined +0ms
knex:bindings [ 'knex_migrations' ] undefined +0ms
knex:client releasing connection to pool: __knexUid1 +20ms
knex:client acquired connection from pool: __knexUid1 +0ms
knex:query create table "knex_migrations" ("id" serial primary key, "name" varchar(255), "batch" integer, "migration_time" timestamptz) undefined +20ms
knex:bindings [] undefined +20ms
knex:client releasing connection to pool: __knexUid1 +5ms
-- ... and the error posted above
The error seems to say that Knex cannot find the migrations as it tries to create the table, but then again it cannot create the table since it already exists. There seems to be a bit gone sideways in the system.
How do I tell Knex to start using the migrations already in the table and continue as is? All the migrations are executed on the old db before the dump, so there's nothing to do.
It was user rights issue. Below is what I think has happened. It's more of a Postgres thing, but knex also has a role in this play.
Originally the database has been created empty. The user is created and given access to control the data with following commands
CREATE DATABASE my_db;
CREATE USER my_user with PASSWORD 'foo';
GRANT ALL ON DATABASE my_db to my_user;
ALTER DATABASE my_db OWNER to my_user;
Although granting all on database sound powerful, I think it actually gives rights to use the database, not the tables itself.
After this, knex migrations were used to create the tables, which means that the user running the scripts already had rights to the tables.
When I dumped the data, it didn't follow the same flow since the tables were not created by the user. The user needs to be granted access to the tables as well, for example to give access to all tables GRANT ALL ON ALL TABLES IN SCHEMA public to my_user. Rights to tables can also be given individually if needed.