How to create table after first creating a database with Node and pg

Question

I'm trying to create a node app that can set itself up on the database end by creating a database then the table and fields after. Below are the two functions I'm using to do each task independently of each other. Can I please get some help on how to combine these together? Should I be using pg-promise rather than pg?

function createDatabase(){

const pool = new pg.Pool({
    user: 'postgres',
    host: '127.0.0.1',
    database: 'postgres',
    password: 'postgres',
    port: '5432'}
);

pool.query("CREATE DATABASE myApp;", 
    (err, res) => {
    console.log(err, res);
    pool.end();

});
}


function createTable(){

const pool = new pg.Pool({
    user: 'postgres',
    host: '127.0.0.1',
    database: 'myApp',
    password: 'postgres',
    port: '5432'}
);

pool.query("CREATE TABLE session(sessionguid UUID NOT NULL, created 
text NOT NULL, sessionlife integer NOT NULL)", 
    (err, res) => {
    console.log(err, res);
    pool.end();
});

}

Getty

Does this answer your question? node-postgres create database – Trenchant

Answer 1

For creating a database from code, I use the client instead of pool for this one. Here's the example:

        const { Pool, Client } = require('pg')
        const client = new Client({
            user: 'postgres',
            host: 'localhost',
            password: 'postgres',
            port: 5432
        })
        await client.connect()
        await client.query(`DROP DATABASE IF EXISTS ${dbname};`)
        await client.query(`CREATE DATABASE ${dbname};`)
        await client.end()

        //call the pool you just created after the database has been created.

Side

Answer 2

Maybe the following code will help you. The table will now be created in the callback immediately after the "CREATE DATABASE" query has finished.

function createDatabase(){
const pool = new pg.Pool({
    user: 'postgres',
    host: '127.0.0.1',
    database: 'postgres',
    password: 'postgres',
    port: '5432'}
);

pool.query("CREATE DATABASE myApp;", (err, res) => {
    console.log(err, res);

    pool.query("CREATE TABLE session(sessionguid UUID NOT NULL, created text NOT NULL, sessionlife integer NOT NULL)", (err, res) => {
        console.log(err, res);
        pool.end();
    });
});
}

Aorist

Almost but this leads me to my next challenge and why I did not try your solution. The database in the connection is postgres so when the create table is fired it is created in the postgres database rather then the new one that was created in the line before it. – Getty