Trying to run knex migrations on deployed node.js app with postgresql db

Questions / Help
,
1

I’ve searched the forum and done tons of debugging with GPT to get me to this point, but I need help urgently please!

I have a deployed node.js server here and an accompanying posgresql 40GB database.
I’ve gotten everything deployed properly, but when I connect to my app via fly ssh console and run my knex migrations command: npx knex migrate:latest --knexfile=./knexfile.js

I get this error:
Connecting to fdaa:5:7642:a7b:170:92b6:6016:2… complete
root@7811075a940418:/app# npx knex migrate:latest --knexfile=./knexfile.js
Using environment: production
create table “knex_migrations” (“id” serial primary key, “name” varchar(255), “batch” integer, “migration_time” timestamptz) - cannot execute CREATE TABLE in a read-only transaction
error: create table “knex_migrations” (“id” serial primary key, “name” varchar(255), “batch” integer, “migration_time” timestamptz) - cannot execute CREATE TABLE in a read-only transaction
at Parser.parseErrorMessage (/app/node_modules/pg-protocol/dist/parser.js:283:98)
at Parser.handlePacket (/app/node_modules/pg-protocol/dist/parser.js:122:29)
at Parser.parse (/app/node_modules/pg-protocol/dist/parser.js:35:38)
at Socket. (/app/node_modules/pg-protocol/dist/index.js:11:42)
at Socket.emit (node:events:513:28)
at addChunk (node:internal/streams/readable:324:12)
at readableAddChunk (node:internal/streams/readable:297:9)
at Readable.push (node:internal/streams/readable:234:10)
at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

The key issue is that it says the action cannot be performed in a read-only transaction. I even manually created the tables (via remote connection to my deployed db, which successfully created the tables) and tried to register a user from my deployed front end and got the same error (the user couldn’t be inserted in a read-only transaction).

  1. I verified that my db user has all privileges.
  2. I saw somewhere to remove the [http] section from my fly.toml and that didn’t work.
  3. I scoured for articles about using knex with node in fly.io and didn’t find anything else.

I appreciate your help and attention!

Kind regards

2

SOLVED:
I updated my prod connection in my knexfile.js from this:
connection: {
host: process.env.DB_HOST,
database: process.env.DB_NAME,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
port: process.env.DB_PORT
}

To this:
connection: process.env.DATABASE_URL,

The database_url is the connection string and includes the info for the primary machine.

3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.