Question
Knex Migration Lock Table Creation Fails Due to Missing CREATE TABLE Permission
Zo River
0 reputation · 21 May 2022, 15:48 UTC
54.4K views1
Loading…
0 reputation · 21 May 2022, 15:48 UTC
When running knex migrate:latest, Knex attempts to create the internal lock table knex_migrations_lock before executing any user migration files. If the database user associated with the Knex instance lacks the CREATE TABLE privilege in the target schema, the operation aborts with an error such as ER_NO_SUCH_TABLE or a permission‑denied message, and the migration process stops.
This behavior is present in all supported dialects (PostgreSQL, MySQL, SQLite) and has been unchanged since the lock table was introduced in Knex 0.15.0 through the 1.x series. Because the lock table is a core part of Knex’s migration concurrency safeguard, the library currently offers no configuration option to disable it or to substitute an external locking mechanism.
Should the lock table creation be made optional via a configuration flag? Can users provide their own locking implementation without forking Knex? Is there a plan to document a supported workaround for environments where the Knex user cannot create tables?
35275 reputation · 22 May 2022, 00:13 UTC
To resolve this failure, the database user configured in your Knex connection must be granted CREATE privileges on the target schema. Because Knex requires the knex_migrations_lock and knex_migrations tables to manage state and concurrency, there is currently no configuration flag to disable these tables or substitute the locking mechanism with an external provider.
Depending on your database dialect, apply the following scoped permissions to the migration user:
knexjsCREATE on the specific schema (usually public).GRANT CREATE ON SCHEMA public TO your_user;CREATE privilege for the target database.GRANT CREATE ON target_db.* TO 'your_user'@'host';Knex migrations operate on a "bootstrap" principle. Before any user-defined migration files are executed, Knex checks for the existence of its internal metadata tables. If they do not exist, it attempts to create them. This process happens before the migration logic enters the up or down functions of your migration files.
If the user lacks CREATE TABLE permissions, the process aborts immediately. This is a hard requirement because the knex_migrations_lock table prevents multiple instances of an application from running migrations simultaneously, which would otherwise lead to race conditions and corrupted schema states.
To confirm the issue is permission-based and not a configuration error, run a manual test using the same credentials as your Knex instance:
-- Attempt to create a dummy table manually
CREATE TABLE migration_test (id INT PRIMARY KEY);
DROP TABLE migration_test;If this command fails with a permission-denied error, the database user's grants are the root cause.
If granting CREATE permissions does not resolve the issue, please provide the exact error code (e.g., MySQL 1142 or PostgreSQL 42501). This will determine if the failure is due to missing schema-level permissions or a more restrictive ownership constraint on the database.
Use comments to ask for clarification. Post a solution as an answer.
5,670 reputation · 21 May 2022, 20:36 UTC
While granting CREATE TABLE privileges resolves the immediate error, it's worth noting that Knex also requires UPDATE permissions on the knex_migrations_lock table for proper lock management. Even if you pre-create the table manually, missing UPDATE rights will cause subsequent migration attempts to fail with 'permission denied' errors during lock acquisition.
In restricted environments, consider this alternative approach:
-- PostgreSQL example
CREATE TABLE IF NOT EXISTS knex_migrations_lock (
id INTEGER PRIMARY KEY,
is_locked BOOLEAN NOT NULL DEFAULT FALSE,
lock_until TIMESTAMP
);
GRANT UPDATE ON knex_migrations_lock TO migration_user;This allows the restricted user to participate in the locking mechanism without requiring CREATE privileges. However, this approach should be documented carefully as it bypasses Knex's bootstrap safety checks.