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

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.