CODEWITHSUNDEEP
javascriptnode.jspostgresqlpromise
twgardner2
twgardner2

Reputation: 660

node/pg/PSQL: Create Tables using Promises

I am writing a simple Node/Express/React/Postgres application and I'm using the pg package to interface with my Postgres Server.

I require three tables, table1, table2, and table3. table2 has a foreign key in table1 and table3 has a foreign key in table 2, so the order that I need to create the tables in is: table1 then table2 then table3.

I am attempting to use promises to enforce this order in my asynchronous table creation calls. I've generally followed Brian Carlson's suggested Project Structure, but clearly I'm doing something wrong.

Here are the simplified, relevant files from my project:

db.js:

const { Pool } = require('pg');

// Create pool connection to database
const pool = new Pool({
  user: XXXX,
  host: XXXX,
  database: XXXX,
  password: XXXX,
  port: XXXX
});

// Pool emitters
pool.on('connect', () => {
  console.log('Connected a client to the database');
});

pool.on('remove', () => {
  console.log('Disconnected a client from the database');
});

pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

// This structure taken from Brian Carlson's pg API Documentation
// https://node-postgres.com/guides/project-structure
module.exports = {
  query: (text, params) => {
    console.log('Making a query!');
    return pool.query(text, params);
  }
};

table_scripts.js:

const db = require('../db');

const Database_Scripts = {
  create_table_1: () => {
    const create_table_1_query = {
      text: `CREATE TABLE IF NOT EXISTS
        public.table_1
      (
        id smallserial,
        name text NOT NULL,
        PRIMARY KEY (id)
      );`
    };
    return db.query(create_table_1_query);
  },

  create_table_2: () => {
    const create_table_2_query = {
      text: `CREATE TABLE IF NOT EXISTS
      public.table_2
    (
        id smallserial,
        table_1_id integer NOT NULL REFERENCES public.table_1(id),
        name text NOT NULL,
        PRIMARY KEY (id)
    );`
    };
    return db.query(create_table_2_query);
  },

  create_projects_table: () => {
    const create_table_3_query = {
      text: `
      CREATE TABLE IF NOT EXISTS
          public.table_3
          (
           id smallserial,
           table_3_id integer NOT NULL REFERENCES public.table_2(id),
           name text NOT NULL,
           PRIMARY KEY (id)
          );`
    };

    return db.query(create_table_3_query);
  }
};

module.exports = Database_Scripts;

create_tables.js:

const Table_Scripts = require('./table_scripts');

Table_Scripts.create_table_1()
  .then(Table_Scripts.create_table_2())
  .then(Table_Scripts.create_table_3())
  .catch(error => console.log(error.stack));

package.json:

{
  "name": "app",
  "version": "0.0.0",
  "scripts": {
    "start": "nodemon ./bin/www",
    "create_tables": "node ./database/scripts/create_tables.js"
  }
}

When I run my create_tables script (npm run-script create_tables), I get the following (sanitized) errors:

Connected a client to the database
Connected a client to the database
Connected a client to the database
Disconnected a client from the database
(node:13444) UnhandledPromiseRejectionWarning: error: relation "public.table_1" does not exist
(node:13444) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a
catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:13444) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
Disconnected a client from the database
(node:13444) UnhandledPromiseRejectionWarning: error: relation "public.table_2" does not exist
(node:13444) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a
catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 2)
Disconnected a client from the database

I've been able to get this script to work converting the functions to async functions, however, I'd really like to understand what I'm doing wrong here.

Upvotes: 1

Views: 1204

Answers (1)

alx.lzt
alx.lzt

Reputation: 486

Your problem seems to be that you're concurrently creating the tables despite explicitly needing to do the opposite.

According to the documentation, pool.query() returns a promise if not provided a callback function as a third argument. You need to wait for each of your db.query() promises to be resolved. Thus it'll wait for the first table to be created, then create the second one and finally the third one.

I would recommend using the async/await syntax

async function createTables () {
    try {
        const create_table_1_query = {
            text: `yourQuery1`
        };
        // awaits for the first table to be created
        await db.query(create_table_1_query);

        const create_table_2_query = {
            text: `yourQuery2`
        };
        // awaits for the second table to be created
        await db.query(create_table_2_query);

        const create_table_3_query = {
            text: `yourQuery3`
        };
        // awaits for the third table to be created
        await db.query(create_table_3_query);
    } catch (e) {
        console.error(e.stack);
    }
}

module.exports.createTables = createTables;

You can then call await createTables();

Upvotes: 4

Related Questions