Postgres sql script to manage database in nodejs

Question

I am trying to automate database creation and dropping of tables on postgres sql from node.js. I have written a script that works well but its behaving in a weird manner. Let me post the code here first then explain this problem.

db.js file

      require('dotenv').config()

  const { Pool } = require('pg')
  const isProduction = process.env.NODE_ENV === 'production'

  // don't try to load .env file on Heroku
  if (process.env.NODE_ENV !== 'production') {
    require('dotenv').config()
  }

  // get the current environment
  var env = process.env.NODE_ENV

  console.log(env);

  // convert to uppercase
  var envString = env.toUpperCase()

  // access the environment variables for this environment
  var DB_USER = process.env['DB_USER_' + envString]
  var DB_PASSWORD = process.env['DB_PASSWORD_' + envString]
  var DB_HOST = process.env['DB_HOST_' + envString]
  var DB_PORT = process.env['DB_PORT_' + envString]
  var DB_DATABASE = process.env['DB_DATABASE_' + envString]

  const connectionString = `postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_DATABASE}`

  const pool = new Pool({
    connectionString: connectionString
  });

  pool.on('connect', () => {
    console.log('connected to the database');
  });

  /**
   * Create Users Table
   */
  const createUsersTable = () => {
    const queryText =
      `CREATE TABLE IF NOT EXISTS users (
          userId SERIAL PRIMARY KEY,
          firstName VARCHAR (50) NOT NULL,
          lastName VARCHAR (50) NOT NULL,
          email VARCHAR (50) NOT NULL,
          password VARCHAR (255) NOT NULL,
          gender VARCHAR(10) check (gender in ('Male', 'Female')),
          jobRole VARCHAR(10) check (jobRole in ('Admin', 'User')),
          department VARCHAR (100) NOT NULL,
          address VARCHAR (100) NOT NULL,
          createdOn TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
          UNIQUE (email)
        )`;

    pool.query(queryText)
      .then((res) => {
        console.log(res);
        pool.end();
      })
      .catch((err) => {
        console.log(err);
        pool.end();
      });
  }

  /**
   * Create GIFs Table
   */
  const createGifsTable = () => {
    const queryText =
      `CREATE TABLE IF NOT EXISTS Gifs (
          gifId SERIAL PRIMARY KEY,
          image VARCHAR (50) NOT NULL,
          title VARCHAR (50) NOT NULL,
          flags INTEGER DEFAULT 0,
          userId INTEGER REFERENCES Users(userId) ON DELETE CASCADE,
          createdOn TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
        )`;

    pool.query(queryText)
      .then((res) => {
        console.log(res);
        pool.end();
      })
      .catch((err) => {
        console.log(err);
        pool.end();
      });
  }

  /**
   * Create Articles Table
   */
  const createArticlesTable = () => {
    const queryText =
      `CREATE TABLE IF NOT EXISTS Articles (
          articleId SERIAL PRIMARY KEY,
          title VARCHAR (100) NOT NULL,
          article TEXT NOT NULL,
          flags INTEGER DEFAULT 0,
          userId INTEGER REFERENCES Users(userId) ON DELETE CASCADE,
          createdOn TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
        )`;

    pool.query(queryText)
      .then((res) => {
        console.log(res);
        pool.end();
      })
      .catch((err) => {
        console.log(err);
        pool.end();
      });
  }

  /**
   * Create Category Table
   */
  const createCategoryTable = () => {
    const queryText =
      `CREATE TABLE IF NOT EXISTS Category (
          categoryId SERIAL PRIMARY KEY,
          category VARCHAR (50) NOT NULL,
          articleId INTEGER REFERENCES Articles(articleId) ON DELETE CASCADE,
          createdOn TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
        )`;

    pool.query(queryText)
      .then((res) => {
        console.log(res);
        pool.end();
      })
      .catch((err) => {
        console.log(err);
        pool.end();
      });
  }

  /**
  * Create ArticleComments Table
  */
  const createArticleCommentsTable = () => {
    const queryText =
      `CREATE TABLE IF NOT EXISTS ArticleComments (
          commentId SERIAL PRIMARY KEY,
          comment TEXT NOT NULL,
          flags INTEGER DEFAULT 0,
          articleId INTEGER REFERENCES Articles(articleId) ON DELETE CASCADE,
          createdOn TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
        )`;

    pool.query(queryText)
      .then((res) => {
        console.log(res);
        pool.end();
      })
      .catch((err) => {
        console.log(err);
        pool.end();
      });
  }

  /**
  * Create GifComments Table
  */
  const createGifCommentsTable = () => {
    const queryText =
      `CREATE TABLE IF NOT EXISTS GifComments (
          commentId SERIAL PRIMARY KEY,
          comment TEXT NOT NULL,
          flags INTEGER DEFAULT 0,
          gifId INTEGER REFERENCES Gifs(gifId) ON DELETE CASCADE,
          createdOn TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
        )`;

    pool.query(queryText)
      .then((res) => {
        console.log(res);
        pool.end();
      })
      .catch((err) => {
        console.log(err);
        pool.end();
      });
  }

  /**
  * Drop Users Table
  */
  const dropUsersTable = () => {
    const queryText = 'DROP TABLE IF EXISTS Users';
    pool.query(queryText)
      .then((res) => {
        console.log(res);
        pool.end();
      })
      .catch((err) => {
        console.log(err);
        pool.end();
      });
  }

  /**
  * Drop Gifs Table
  */
  const dropGifsTable = () => {
    const queryText = 'DROP TABLE IF EXISTS Gifs';
    pool.query(queryText)
      .then((res) => {
        console.log(res);
        pool.end();
      })
      .catch((err) => {
        console.log(err);
        pool.end();
      });
  }

  /**
  * Drop Articles Table
  */
  const dropArticlesTable = () => {
    const queryText = 'DROP TABLE IF EXISTS Articles';
    pool.query(queryText)
      .then((res) => {
        console.log(res);
        pool.end();
      })
      .catch((err) => {
        console.log(err);
        pool.end();
      });
  }

  /**
  * Drop Category Table
  */
  const dropCategoryTable = () => {
    const queryText = 'DROP TABLE IF EXISTS Category';
    pool.query(queryText)
      .then((res) => {
        console.log(res);
        pool.end();
      })
      .catch((err) => {
        console.log(err);
        pool.end();
      });
  }

  /**
  * Drop ArticleComments Table
  */
  const dropArticleCommentsTable = () => {
    const queryText = 'DROP TABLE IF EXISTS ArticleComments';
    pool.query(queryText)
      .then((res) => {
        console.log(res);
        pool.end();
      })
      .catch((err) => {
        console.log(err);
        pool.end();
      });
  }

  /**
  * Drop GifComments Table
  */
  const dropGifCommentsTable = () => {
    const queryText = 'DROP TABLE IF EXISTS GifComments';
    pool.query(queryText)
      .then((res) => {
        console.log(res);
        pool.end();
      })
      .catch((err) => {
        console.log(err);
        pool.end();
      });
  }

  /**
  * Create All Tables
  */
  const createAllTables = () => {
    createUsersTable();
    createGifsTable();
    createArticlesTable();
    createCategoryTable();
    createArticleCommentsTable();
    createGifCommentsTable();
  }
  /**
  * Drop All Tables
  */
  const dropAllTables = () => {
    dropArticleCommentsTable();
    dropGifCommentsTable();
    dropCategoryTable();
    dropGifsTable();
    dropArticlesTable();
    dropUsersTable();
  }

  pool.on('remove', () => {
    console.log('client removed');
    process.exit(0);
  });


  module.exports = {
    createUsersTable,
    createGifsTable,
    createArticlesTable,
    createCategoryTable,
    createArticleCommentsTable,
    createGifCommentsTable,
    createAllTables,
    dropUsersTable,
    dropGifsTable,
    dropArticlesTable,
    dropCategoryTable,
    dropArticleCommentsTable,
    dropGifCommentsTable,
    dropAllTables
  };

  require('make-runnable');

In this file, I have created individual functions to create and drop database tables. My script on package.js looks like:

"scripts": {
  "start": "NODE_ENV=dev nodemon server.js",
  "test": "NODE_ENV=test mocha --exit",
  "dbCreate": "NODE_ENV=dev node -e 'require(\"./db\").createAllTables()'"
}

Finally, since I have added all individual create table commands in the db.js to the createAllTables function and added this to the scripts, I expect the tables to be created when I run the command npm run dbCreate from the command line.

My Problem

When I execute the command npm run dbCreate, it creates either 1 or 2 tables of the total. When I execute again, it creates one ot two more tables in addition. So for all the tables to be created, I have to execute the command repeated, and yet I want this to be done at one go. Same thing happens when I replace the createAllTables function with dropAllTables. Is there anything that I am doing wrong? I want to use this script while running automated tests.

Postgres sql script to manage database in nodejs

My Problem

Answers (1)

Related Questions