Aynsc/await JavaScript with postgresql queries-CodePudding

I'm trying to write a code that will drop all the tables before creating them again. However, due to the JavaScript nature of async, these execute statements happen asynchronously sometimes attempting to create a table before dropping it. Here's my code any help making this function better is highly appreciated!

#!/usr/vin/env node

const { config } = require('./database/config.js');
const { Client, Pool } = require('pg');
const chalk = require('chalk');

const execute = async (query, params = []) => {
    const client = new Client(config.db);
    client.connect();
    await client.query(query, params, (err, res) => {
        if (res) {
            let querySplit = query.split(" ");
            if (querySplit[0] === "DROP"){
                console.log(`TABLE ${querySplit[4].toUpperCase()} DROPPED`);
                client.end()
            }
            else{
                console.log(`TABLE ${querySplit[2].toUpperCase()} CREATED`);
                client.end()
            }
        }
        else{
            console.log(err);
            client.end();
        }
    });
};

const deleteTables = () => {
    execute(`DROP TABLE IF EXISTS book`);
    execute(`DROP TABLE IF EXISTS namedPerson`);
    execute(`DROP TABLE IF EXISTS type`);
    execute(`DROP TABLE IF EXISTS subject`);
    execute(`DROP TABLE IF EXISTS title`);
    execute(`DROP TABLE IF EXISTS format`);
    execute(`DROP TABLE IF EXISTS author`);
    execute(`DROP TABLE IF EXISTS editor`);  
    execute(`DROP TABLE IF EXISTS translator`);
    execute(`DROP TABLE IF EXISTS edition`);
    execute(`DROP TABLE IF EXISTS bookType`);
    execute(`DROP TABLE IF EXISTS bookSubject`);
    execute(`DROP TABLE IF EXISTS publisher`);
    execute(`DROP TABLE IF EXISTS agreement`);
    execute(`DROP TABLE IF EXISTS bookEdition`);
}

const createDatabases = () => {
    //Creation of book
    execute(`CREATE TABLE book (
        bookID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
        bookDescriptor VARCHAR(250),
        bookNote VARCHAR(250)
    )`);
    
    //Creation of namedPerson
    execute(`CREATE TABLE namedPerson (
        namedPersonID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        fname VARCHAR(250) NOT NULL,
        lname VARCHAR(250) NOT NULL,
        nobilityTitle VARCHAR(250),
        lifeYears VARCHAR(250), 
        personNote VARCHAR(250)
    )`);

    //Creation of type
    execute(`CREATE TABLE type (
        typeID VARCHAR(50) PRIMARY KEY,
        editionString VARCHAR(250) NOT NULL
    )`);

    //Creation of subject
    execute(`CREATE TABLE subject (
        subjectID VARCHAR(50) PRIMARY KEY,
        subjectdescription VARCHAR(250) NOT NULL
    )`);

    //Creation of title
    execute(`CREATE TABLE title (
        titleID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        titleString VARCHAR(250) NOT NULL
    )`);
    
    //Creation of format
    execute(`CREATE TABLE format (
        formatID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        formatName VARCHAR(250) NOT NULL
    )`);

    //Creation of author
    execute(`CREATE TABLE author (
        namedPersonID INT NOT NULL,
        bookID INT NOT NULL
    )`);

    //Creation of editor
    execute(`CREATE TABLE editor (
        namedPersonID INT NOT NULL,
        bookID INT NOT NULL
    )`)

    //Creation of translator
    execute(`CREATE TABLE translator (
        namedPersonID INT NOT NULL,
        bookID INT NOT NULL
    )`);

    //Creation of edition
    execute(`CREATE TABLE edition (
        editionID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        editionString VARCHAR(250) NOT NULL
    )`);

    //Creation of bookType
    execute(`CREATE TABLE bookType (
        bookID INT NOT NULL, 
        typeID VARCHAR(50)
    )`);

    //Creation of bookSbuject
    execute(`CREATE TABLE bookSubject (
        bookID INT NOT NULL, 
        subjectID VARCHAR(50)
    )`);

    //Creation of publisher
    execute(`CREATE TABLE publisher (
        publisherID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        publisherName VARCHAR(250),
        publisherLocation VARCHAR(250),
        publisherType VARCHAR(250)
    )`);

    //Creation of agreement
    execute(`CREATE TABLE agreement (
        agreementTypeID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        agreementTypeName VARCHAR(250), 
        agreementTypeNote VARCHAR(250)
    )`);
    
    //Creation of bookEdition
    execute(`CREATE TABLE bookEdition (
        bookID INT,
        editionID INT,
        publisherID INT,
        titleID INT,
        formatID INT,
        year VARCHAR(250),
        yearNote VARCHAR(250),
        numberPages INT,
        numberVolumes VARCHAR(250),
        agreementTypeID INT, 
        salePrice INT,
        paymentAgreedAmount INT, 
        illustrations VARCHAR(250), 
        illustrationsPayment INT,
        copiesSold INT,
        copiesRemaining INT,
        profitLoss INT,
        proceedsAuthor VARCHAR(250)
    )`);
};


const App = async () => {
    deleteTables();
    createDatabases();
}

App();

CodePudding user response：

In your example, you are not awaiting promises. And it is better to use Promise.all when you need to await multiple promises.

You can do it like this :

#!/usr/vin/env node

const { config } = require('./database/config.js');
const { Client } = require('pg');

const client = new Client(config.db);

const execute = async (query, params = []) => {
    try {
        const res = await client.query(query, params);

        if (res) {
            const querySplit = query.split(' ');
            if (querySplit[0] === 'DROP') {
                console.log(`TABLE ${querySplit[4].toUpperCase()} DROPPED`);
            } else {
                console.log(`TABLE ${querySplit[2].toUpperCase()} CREATED`);
            }
        }
    } catch (err) {
        console.error(err);
    }
};

const deleteTables = async () => {
    return Promise.all([
        execute(`DROP TABLE IF EXISTS book`),
        execute(`DROP TABLE IF EXISTS namedPerson`),
        execute(`DROP TABLE IF EXISTS type`),
        execute(`DROP TABLE IF EXISTS subject`),
        execute(`DROP TABLE IF EXISTS title`),
        execute(`DROP TABLE IF EXISTS format`),
        execute(`DROP TABLE IF EXISTS author`),
        execute(`DROP TABLE IF EXISTS editor`),
        execute(`DROP TABLE IF EXISTS translator`),
        execute(`DROP TABLE IF EXISTS edition`),
        execute(`DROP TABLE IF EXISTS bookType`),
        execute(`DROP TABLE IF EXISTS bookSubject`),
        execute(`DROP TABLE IF EXISTS publisher`),
        execute(`DROP TABLE IF EXISTS agreement`),
        execute(`DROP TABLE IF EXISTS bookEdition`),
    ]);
};

const createDatabases = async () => {
    return Promise.all([
        //Creation of book
        execute(`CREATE TABLE book (
            bookID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
            bookDescriptor VARCHAR(250),
            bookNote VARCHAR(250)
        )`),

        //Creation of namedPerson
        execute(`CREATE TABLE namedPerson (
            namedPersonID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            fname VARCHAR(250) NOT NULL,
            lname VARCHAR(250) NOT NULL,
            nobilityTitle VARCHAR(250),
            lifeYears VARCHAR(250), 
            personNote VARCHAR(250)
        )`),

        //Creation of type
        execute(`CREATE TABLE type (
            typeID VARCHAR(50) PRIMARY KEY,
            editionString VARCHAR(250) NOT NULL
        )`),

        //Creation of subject
        execute(`CREATE TABLE subject (
            subjectID VARCHAR(50) PRIMARY KEY,
            subjectdescription VARCHAR(250) NOT NULL
        )`),

        //Creation of title
        execute(`CREATE TABLE title (
            titleID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            titleString VARCHAR(250) NOT NULL
        )`),

        //Creation of format
        execute(`CREATE TABLE format (
            formatID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            formatName VARCHAR(250) NOT NULL
        )`),

        //Creation of author
        execute(`CREATE TABLE author (
            namedPersonID INT NOT NULL,
            bookID INT NOT NULL
        )`),

        //Creation of editor
        execute(`CREATE TABLE editor (
            namedPersonID INT NOT NULL,
            bookID INT NOT NULL
        )`),

        //Creation of translator
        execute(`CREATE TABLE translator (
            namedPersonID INT NOT NULL,
            bookID INT NOT NULL
        )`),

        //Creation of edition
        execute(`CREATE TABLE edition (
            editionID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            editionString VARCHAR(250) NOT NULL
        )`),

        //Creation of bookType
        execute(`CREATE TABLE bookType (
            bookID INT NOT NULL, 
            typeID VARCHAR(50)
        )`),

        //Creation of bookSbuject
        execute(`CREATE TABLE bookSubject (
            bookID INT NOT NULL, 
            subjectID VARCHAR(50)
        )`),

        //Creation of publisher
        execute(`CREATE TABLE publisher (
            publisherID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            publisherName VARCHAR(250),
            publisherLocation VARCHAR(250),
            publisherType VARCHAR(250)
        )`),

        //Creation of agreement
        execute(`CREATE TABLE agreement (
            agreementTypeID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            agreementTypeName VARCHAR(250), 
            agreementTypeNote VARCHAR(250)
        )`),

        //Creation of bookEdition
        execute(`CREATE TABLE bookEdition (
            bookID INT,
            editionID INT,
            publisherID INT,
            titleID INT,
            formatID INT,
            year VARCHAR(250),
            yearNote VARCHAR(250),
            numberPages INT,
            numberVolumes VARCHAR(250),
            agreementTypeID INT, 
            salePrice INT,
            paymentAgreedAmount INT, 
            illustrations VARCHAR(250), 
            illustrationsPayment INT,
            copiesSold INT,
            copiesRemaining INT,
            profitLoss INT,
            proceedsAuthor VARCHAR(250)
        )`),
    ]);
};

const App = async () => {
    await client.connect();
    await deleteTables();
    await createDatabases();
    await client.end();
};

App();