search

brianc / node-postgres

PostgreSQL client for node.js.
https://node-postgres.com
MIT License
12.04k stars 1.21k forks source link

error: relation "users" does not exist #3259

Closed XTREME1738 closed 2 weeks ago

XTREME1738 commented 2 weeks ago

When executing queries, I get an error:

40 |     res = resolve
41 |     rej = reject
42 |   }).catch((err) => {
43 |     // replace the stack trace that leads to `TCP.onStreamRead` with one that leads back to the
44 |     // application that created the query
45 |     Error.captureStackTrace(err)
               ^
error: relation "users" does not exist
 code: "42P01"

      at C:\Users\xtreme\cspg\server\node_modules\pg-pool\index.js:45:11
      at processTicksAndRejections (native:1:1)

This happens while attempting to create the users table while it exists with a CREATE TABLE IF NOT EXISTS query:

CREATE TABLE IF NOT EXISTS cspg_users
(
        id character varying(255) NOT NULL,
        email text NOT NULL,
        username character varying(255) NOT NULL,
        password character varying(255) NOT NULL,
        mfa_enabled boolean DEFAULT false,
        mfa_secret text,
        mfa_backup_passphrase text,
        created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
        updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
        last_login_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
        last_ip character varying(255),
        force_password_change boolean DEFAULT false,
        admin boolean DEFAULT false,
        CONSTRAINT cspg_users_pkey PRIMARY KEY (id),
        CONSTRAINT cspg_users_ukey UNIQUE (email, username)
);

And some other tables (which have a constraint with a foreign key to users) both while they exist and while they do not exist, however the queries work perfectly fine in pgAdmin. The tables do get created if they do not exist, however this error also is not caught by the catch block on the query, or the catch block on the pool itself, which means my application always crashes due to this error.

brianc commented 2 weeks ago

This is almost certainly an issue with your code as node-postgres does nothing to alter, change, interpolate or anything else to your query text - it just passes it as is into the database. That being said...I'm happy to help diagnose but I'd need to see more code - maybe there's something that's not being awaited or this query is somehow executing before the users table is created? Please note in your example you're using the table name cspg_users but your error references the table users - could that be the problem?

XTREME1738 commented 2 weeks ago

This is almost certainly an issue with your code as node-postgres does nothing to alter, change, interpolate or anything else to your query text - it just passes it as is into the database. That being said...I'm happy to help diagnose but I'd need to see more code - maybe there's something that's not being awaited or this query is somehow executing before the users table is created? Please note in your example you're using the table name cspg_users but your error references the table users - could that be the problem?

Yes I saw that, and wondered but I couldn't find any mention in any of my queries with just users in. Definitely not the best code ever written but it semi-works. Here is a snippet of my code:

class DatabaseQueries {
    private pool: Pool;
    private prefix = config.postgres.table_prefix; // <-- "cspg_"

    public constructor() {
        this.pool = DatabaseConnection.instance.pool!; // I have a class which just creates a connection and has an instance where it stores the pool, this can be assumed to just be a new Pool
        this.makeTables();
    }

    private createTable(tableName: string, columns: string[]): Promise<void> {
        return new Promise((resolve, reject) => {
            tableName = `${this.prefix}${tableName}`;

            const columnDefinitions = columns.join(', ');

            const createTableQuery = `CREATE TABLE IF NOT EXISTS ${tableName} (${columnDefinitions});`;
            this.pool.query(createTableQuery).then(() => resolve())
                .catch((err) => {
                    reject(err); // Not caught here
                });
        });
    }

    public async makeTables(): Promise<void> {
          try {
          console.debug('Creating users table');
          /* Users table constructed query
          CREATE TABLE IF NOT EXISTS cspg_users
                  (
                          id character varying(255) NOT NULL,
                          email text NOT NULL,
                          username character varying(255) NOT NULL,
                          password character varying(255) NOT NULL,
                          mfa_enabled boolean DEFAULT false,
                          mfa_secret text,
                          mfa_backup_passphrase text,
                          created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
                          updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
                          last_login_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
                          last_ip character varying(255),
                          force_password_change boolean DEFAULT false,
                          admin boolean DEFAULT false,
                          CONSTRAINT cspg_users_pkey PRIMARY KEY (id),
                          CONSTRAINT cspg_users_ukey UNIQUE (email, username)
                  );
                  */
          await this.createTable('users', [
              `id character varying(255) NOT NULL`,
              `email text NOT NULL`,
              `username character varying(255) NOT NULL`,
              `password character varying(255) NOT NULL`,
              `mfa_enabled boolean DEFAULT false`,
              `mfa_secret text`,
              `mfa_backup_passphrase text`,
              `created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP`,
              `updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP`,
              `last_login_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP`,
              `last_ip character varying(255)`,
              `force_password_change boolean DEFAULT false`,
              `admin boolean DEFAULT false`,
              `CONSTRAINT ${this.prefix}users_pkey PRIMARY KEY (id)`,
              `CONSTRAINT ${this.prefix}users_ukey UNIQUE (email, username)`,
          ]);
          console.debug('Creating websites table');
          /* Websites table constructed query
          CREATE TABLE IF NOT EXISTS cspg_websites
                  (
                          id character varying(255),
                          domain character varying(255) NOT NULL,
                          created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
                          updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
                          user_ids character varying(255)[],
                          admin_id character varying(255),
                          CONSTRAINT cspg_websites_admin_id_fkey FOREIGN KEY (admin_id) REFERENCES cspg_users(id) ON DELETE CASCADE,
                          CONSTRAINT cspg_websites_ukey UNIQUE (domain),
                          CONSTRAINT cspg_websites_pkey PRIMARY KEY (id)
                  );
                  */
          await this.createTable('websites', [
              `id character varying(255)`,
              `domain character varying(255) NOT NULL`,
              `created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP`,
              `updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP`,
              `user_ids character varying(255)[]`,
              `admin_id character varying(255)`,
              `CONSTRAINT ${this.prefix}websites_admin_id_fkey FOREIGN KEY (admin_id) REFERENCES ${this.prefix}users(id) ON DELETE CASCADE`,
              `CONSTRAINT ${this.prefix}websites_ukey UNIQUE (domain)`,
              `CONSTRAINT ${this.prefix}websites_pkey PRIMARY KEY (id)`
          ]);
          console.debug('Creating password_resets table');
          /* Password resets table constructed query
          CREATE TABLE IF NOT EXISTS cspg_password_resets
                  (
                          id character varying(255),
                          user_id character varying(255),
                          token character varying(255) NOT NULL,
                          expires_at TIMESTAMP NOT NULL,
                          CONSTRAINT cspg_password_resets_user_id_fkey FOREIGN KEY (user_id) REFERENCES cspg_users(id) ON DELETE CASCADE,
                          CONSTRAINT cspg_password_resets_pkey PRIMARY KEY (id),
                          CONSTRAINT cspg_password_resets_ukey UNIQUE (token)
                  );
                  */
          await this.createTable('password_resets', [
              `id character varying(255)`,
              `user_id character varying(255)`,
              `token character varying(255) NOT NULL`,
              `expires_at TIMESTAMP NOT NULL`,
              `CONSTRAINT ${this.prefix}password_resets_user_id_fkey FOREIGN KEY (user_id) REFERENCES ${this.prefix}users(id) ON DELETE CASCADE`,
              `CONSTRAINT ${this.prefix}password_resets_pkey PRIMARY KEY (id)`,
              `CONSTRAINT ${this.prefix}password_resets_ukey UNIQUE (token)`
          ]);
          console.debug('All tables created successfully!')
          } catch (e) {
              console.error('An error occurred when creating the tables', e); // Not caught here
          }
    }
}

Edit: added the full queries to each createTable call

Edit 2: I think that I may just be being stupid here, I just have to fix a permission error then I can say for sure.

brianc commented 2 weeks ago

Nothing jumps out at me at first glace, though there are a couple things...

1) you're calling this.makeTables() in the constructor but not awaiting it (because constructors can't be async) - wouldn't recommend this. You do have a big try/catch around everything so in theory it shouldn't ever throw but...still kinda yikes.

2) you might wanna make 

private createTable(tableName: string, columns: string[]): Promise<void> {

into an async function & ditch the manual promise construction inside it. Would also recommend not over-writing the tableName function param, but that's mostly a style thing.

3) inside the createTable function could you change a few things to log more info?

(converting to to async so its easier to read)

private async createTable(tableName: string, columns: string[]): Promise<void> {
const fullTableName = `${this.prefix}${tableName}`;

const columnDefinitions = columns.join(', ');

const createTableQuery = `CREATE TABLE IF NOT EXISTS ${fullTableName} (${columnDefinitions});`;
console.log('executing', createTableQuery)
try{
await this.pool.query(createTableQuery)
} catch (e) {
  console.log('failed to execute query', query)
  console.log('the error is...')
  console.log(e)
  throw e
}
console.log('query executed successfully)
}

Maybe try running that & see?

XTREME1738 commented 2 weeks ago

Nothing jumps out at me at first glace, though there are a couple things...

1. you're calling `this.makeTables()` in the constructor but not awaiting it (because constructors can't be async) - wouldn't recommend this. You do have a big `try/catch` around everything so in theory it shouldn't ever throw but...still kinda yikes.

2. you might wanna make
private createTable(tableName: string, columns: string[]): Promise<void> {

into an async function & ditch the manual promise construction inside it. Would also recommend not over-writing the tableName function param, but that's mostly a style thing.

3. inside the `createTable` function could you change a few things to log more info?

(converting to to async so its easier to read)

private async createTable(tableName: string, columns: string[]): Promise<void> {
const fullTableName = `${this.prefix}${tableName}`;

const columnDefinitions = columns.join(', ');

const createTableQuery = `CREATE TABLE IF NOT EXISTS ${fullTableName} (${columnDefinitions});`;
console.log('executing', createTableQuery)
try{
await this.pool.query(createTableQuery)
} catch (e) {
  console.log('failed to execute query', query)
  console.log('the error is...')
  console.log(e)
  throw e
}
console.log('query executed successfully)
}

Maybe try running that & see?

My bad code lol, can't say it is the best code ever, but I think I have found the problem, it may be unrelated, it seems I might have been running SQL in the background without even realizing it had been called, I will definitely make the code.. better at some point. Anyway sorry for wasting your time, I definitely need to make this code show more of what is executing and when.

brianc commented 2 weeks ago

can't say it is the best code ever

don't even worry about it friend...if you saw the type of code I write when I'm trying to get stuff done as fast as possible you would surely shudder. Also, it's always easier to edit than create. I'm glad you found the problem. Please lmk if you run into anything else - I'm here to help!