Is it possible to "CREATE DATABASE ..." with pg-promise and PostgreSQL (9.5)?

Question

Question is:

1) Can pg-promise be used to create a new database (schemas, et. al.)?

Using node package 'pg-promise' I can't seem to figure out if it's possible to create a new database. I can connect to an existing database and have no issue there. However, when I run without a database name I get an error:

This is what I am trying:

host = host || '127.0.0.1'
port = port || '5432'

const pgp = require('pg-promise')(pgpInitOptions)

// database connection details
const pgConnectionOptions = {
  host: host,
  port: port,
  user: user,
  password: pass
}

// database instance
const localDB = pgp(pgConnectionOptions)

let escapedDbName = dbName.replace(/\"/g, '""');
let sql = 'CREATE DATABASE "' + escapedDbName + '"';

localDB
.any(sql)
.then((results) => {
  console.log('creation of database successful', results)
})
.catch((error) => {
  console.error('creation of database failed', error)
})

Then, I get this error:

creation of database failed
Error: database "jeff" does not exist

"jeff" is my ubuntu login name.

Something is wrong with the credentials you are providing. You must use a valid database account, i.e. the administrative user + password you used when installing the PostgreSQL server, which would have the access rights to create new databases. Then it should just work. — vitaly-t
– vitaly-t, Commented Mar 11, 2018 at 21:49
Sorry, I should have mentioned they are passed in as variables. — Hawkeye64
– Hawkeye64, Commented Mar 11, 2018 at 22:44

vitaly-t · Accepted Answer · 2020-06-21 19:48:19Z

9

First, you need to connect to an existing database, using an admin account which has the right to create new databases.

Then you can create new databases via a regular query...

const pgp = require('pg-promise')(/* initialization options */);

const db = pgp({
    database: 'any-existing-db',
    port: 5432,
    user: 'postgres', // any admin user
    password: 'admin-password'
});
    
await db.none('CREATE DATABASE $1:name', ['my_database']);

I am running such code locally, and it works fine.

edited Jun 21, 2020 at 19:48

answered Mar 11, 2018 at 21:54

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is it possible to "CREATE DATABASE ..." with pg-promise and PostgreSQL (9.5)?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related