0
import { pool } from '../models/pool';
const createUsersTable = `
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  firstName VARCHAR NOT NULL CHECK (LENGTH(firstName) BETWEEN 3 AND 150),
  lastName VARCHAR(86),
  userEmail TEXT NOT NULL UNIQUE CHECK (LENGTH(userEmail) BETWEEN 5 AND 280),
  mobileNumber TEXT NOT NULL UNIQUE CHECK (mobileNumber ~ '^([0|\+[0-9]{2})?([7-9][0-9]{9})$'),
  password VARCHAR NOT NULL CHECK (password ~ '^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*[^\da-zA-Z]).{8,}$'),
  gender VARCHAR NOT NULL CHECK (gender IN ('MALE','FEMALE','OTHERS'))
  )
  `;

const addAdressColumnInUsersTable=`
    ALTER TABLE IF EXISTS users ADD COLUMN address VARCHAR;
`;

export const executeQueryArray = async (arr) =>
  new Promise((resolve) => {
    const stop = arr.length;
    arr.forEach(async (q, index) => {
      await pool.query(q);
      if (index + 1 === stop) resolve();
    });
  });
export const createTables = () => executeQueryArray([createTableQueries]);
export const updateTables= () => executeQueryArray([addAdressColumnInUsersTable]);

I called the createTables, updateTables functions in the app.js file and after restarting the application with nodemon getting error like ** address column is already added into users table.** So new I would to configure to run all sql scripts at the time or application start and it should run only once. can any one help me out to solve this issue with any predefined js library or even with manual implementation ?

Thirumalesu
  • 9
  • 4
  • First, I suggest you use an orm like Typeorm or Prisma. than this might be useful https://stackoverflow.com/questions/6520999/create-table-if-not-exists-equivalent-in-sql-server – Iman Hosseini Pour Jun 07 '23 at 05:05
  • Your scripts are _almost_ idempotent. Why are you adding the address column seperately? Why not include it in the original table creation script (which is idempotent) – Nick.Mc Jun 07 '23 at 05:51
  • If the adress column creation script has to be seperate, then I suggest you use INFORMATION_SCHEMA to first check if it exists. BTW you should always specify a size for datatypes, i.e. `VARCHAR(50)` instead of `VARCHAR` – Nick.Mc Jun 07 '23 at 06:00
  • for creating tables using if exists it will work, but in feature if I wanted to add a new column to existing table like address how can we handle it. to fix this issue only I wanted configure the scripts to execute at one time even if I restart the application – Thirumalesu Jun 07 '23 at 16:00
  • Hi @ImanHosseiniPour , could you please exaplin or share any documents related to the typeorm or prisma which menctioned in the above. I am really not sure about those – Thirumalesu Jun 07 '23 at 16:01

0 Answers0