node-postgres create database

Question

I am using node-postgres, and at the beginning of my application I want to check whether the database exists or not. So my workflow idea is as following:

1. Check whether myDb is existing
2. If it is there, create the tables
3. If not, then create first the database, then tables

As you see it is a really easy process, however, the driver implementation requires to have a database name postgres://username:password@host/database to be connected, which means you need to connect to a database first.

So what I am doing now is to connect to postgres database at the beginning, making a query to create database, cathing the exception if it is already there, then closing my connection and connecting to the newly created database, then creating the tables. Here is the code:

var conStringPri = 'postgres://' + username + ':' + password + '@' + host + 
    '/postgres';
var conStringPost = 'postgres://' + username + ':' + password + '@' + host + 
    '/' + dbName;

pg.connect(conStringPri, function(err, client, done) { // connect to postgres db
    if (err)
        console.log('Error while connecting: ' + err); 
    client.query('CREATE DATABASE ' + dbName, function(err) { // create user's db
        if (err) 
            console.log('ignoring the error'); // ignore if the db is there
        client.end(); // close the connection

        // create a new connection to the new db
        pg.connect(conStringPost, function(err, clientOrg, done) {
            // create the table
            clientOrg.query('CREATE TABLE IF NOT EXISTS ' + tableName + ' ' +
                    '(...some sql...)';
        });
    });
});

As you see I am opening and closing the connection twice, and this way seems wrong to me. I'll be glad if you propose a better way, or maybe explain how did you accomplish this.

Answer 1

Here is a script I use which is essentially just executing shell commands with execa:

import execa from 'execa';

class DatabaseService {
  public async setupDatabase() {
    const logCmd = (cmd: execa.ExecaChildProcess) => {
      cmd.stdout.on('data', (data) => {
        this.logger.log(data.toString());
      });

      cmd.stderr.on('data', (data) => {
        this.logger.error(data.toString());
      });
    };

    const createUser = () => {
      return new Promise<void>((resolve, reject) => {
        const cmd = execa('createuser', [Config.databaseUser, '--superuser']);

        logCmd(cmd);

        let userExists = false;

        cmd.stderr.on('data', (data) => {
          if (
            data
              .toString()
              .includes(`role "${Config.databaseUser}" already exists`)
          ) {
            userExists = true;
          }
        });

        cmd.on('exit', (code) => {
          if (!userExists && code) {
            reject(new Error(`Failed to create user for database: ${code}`));
          } else {
            resolve();
          }
        });
      });
    };

    const createDatabase = () => {
      return new Promise<void>((resolve, reject) => {
        const cmd = execa('createdb', [Config.databaseName]);

        logCmd(cmd);

        let databaseExists = false;

        cmd.stderr.on('data', (data) => {
          if (
            data
              .toString()
              .includes(`database "${Config.databaseName}" already exists`)
          ) {
            databaseExists = true;
          }
        });

        cmd.on('exit', (code) => {
          if (!databaseExists && code) {
            reject(new Error(`Failed to create database: ${code}`));
          } else {
            resolve();
          }
        });
      });
    };

    await createUser();
    await createDatabase();
  }
}

As you can see, the script detects if the user or database already exists and will ignore errors in those events, because the intended state of Postgres will have been met, and thats all I care about when I run it.

Answer 2

Install

npm install --save -g pgtools

CLI Example

createdbjs my_awesome_db --user=admin --password=admin

Answer 3

This is a bit old but I just want to share how I handled this kind of setup.

You need to call the third param from the callback which is the done from pg.connect(conn, (err, client, done) => {}). This will release the connection and bring back to pool.

 async.series([
   done => {
     pg.connect(connPrimary, (err, client, releaseConn) => {
      if (err) return done(err)

      client.query(`CREATE DATABASE ${conf.database}`, (err) => {
        if (err && !~err.message.indexOf('already exists')) {
          return done(err)
        }

        client.end()
        releaseConn()
        done()
      })
    })
  },
  done => {
    let connSecondary = `postgres://${conf.user}:${conf.password}@${conf.host}:${conf.port}/${conf.database}`

    pg.connect(connSecondary, (err, client, releaseConn) => {
      if (err) return done(err)

      let createTableQuery = `CREATE TABLE IF NOT EXISTS test_table(_id bigint primary key, co2_field varchar(40) NOT NULL, temp_field int NOT NULL, quality_field decimal NOT NULL, reading_time_field timestamp NULL)`

      client.query(createTableQuery, err => {
        if (err) return done(err)

        releaseConn()
        done()
      })
    })    
  }
], err => {
  should.ifError(err)
  doneInit()
})

Answer 4

I've just written a module for that: https://github.com/olalonde/pgtools

var pgtools = require('pgtools');
pgtools.createdb({
  user: 'postgres',
  password: 'some pass',
  port: 5432,
  host: 'localhost'
}, 'test-db', function (err, res) {
  if (err) {
    console.error(err);
    process.exit(-1);
  }
  console.log(res);
});

Hopefully it can make your code a bit cleaner.

Answer 5

As you see it is a really easy process, however, the driver implementation requires to have a database name postgres://username:password@host/database to be connected, which means you need to connect to a database first.

It's not because of the driver implementation, it's PostgreSQL itself. It's the same with any other language or driver.

A client needs to be connected to a database in order to do anything, including a CREATE DATABASE. Besides the postgres database, template1 is often used for this purpose too.

Then, since you must connect to the freshly created database to create objects inside it, there's no way to avoid opening another connection.

In short, what you're doing can't be simplified, it's already optimal.