Knex cannot find table in Cloud SQL Postgres from Cloud Functions

Question

I am trying to connect to a Postgres 12 DB running in Cloud SQL from a Cloud Function written in TypeScript.

I create the database with the following:

import * as Knex from "knex"

const { username, password, instance } = ... // username, password, connection name (<app-name>:<region>:<database>)

const config = {
   client: 'pg',
   connection: {
      user: username,
      password: password, 
      database: 'ingredients',
      host: `/cloudsql/${instance}`,
      pool: { min: 1, max: 1}
   }
}

const knex = Knex(config as Knex.Config)

I am then querying the database using:

const query = ... // passed in as param
const result = await knex('tableName').where('name', 'ilike', query).select('*')

When I run this code, I get the following error in the Cloud Functions logs:

Unhandled error { error: select * from "tableName" where "name" ilike $1 - relation "tableName" does not exist
    at Parser.parseErrorMessage (/workspace/node_modules/pg-protocol/dist/parser.js:278:15)
    at Parser.handlePacket (/workspace/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/workspace/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.stream.on (/workspace/node_modules/pg-protocol/dist/index.js:10:42)
    at Socket.emit (events.js:198:13)
    at Socket.EventEmitter.emit (domain.js:448:20)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at Pipe.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

I created the table using the following commands in the GCP Cloud Shell (then populated with a data from a CSV):

\connect ingredients;
CREATE TABLE tableName (name VARCHAR(255), otherField VARCHAR(255), ... );

In that console, if I run the query SELECT * FROM tableName;, I see the correct data listed.

Why does Knex not see the table: tableName, but the GCP Cloud Shell does?

BTW, I am definitely connecting to the correct db, as I see the same error logs in the Cloud SQL logging interface.

Mikael Lepistö · Accepted Answer · 2020-11-16 07:51:14Z

2

Looks like you are creating the table tableName without quoting, which makes it actually lower case (case insensitive). So when creating schema do:

CREATE TABLE "tableName" ("name" VARCHAR(255), "otherField" VARCHAR(255), ... );

or use only lower-case table / column names.

answered Nov 16, 2020 at 7:51

Mikael Lepistö

19.9k4 gold badges73 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Knex cannot find table in Cloud SQL Postgres from Cloud Functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related