Search code examples
node.jspostgresqlpg-promise

How to drop and then recreate database with pg-promise?

I would like to be able to reset my database from code by first dropping the database, then recreating it, then recreating all the tables, then repopulating it with data.

But if I simply do this:

await db.any('DROP DATABASE $1',cn.database)

I get the following error:

error: syntax error at or near "'testdb'"

Most likely because I'm attempting to delete the database I'm currently connected to.

So is there a way to delete or reset a database with pg-promise at runtime?

Solution

  • A database name is an identifier, not a value literal, in SQL syntax. You need to use an SQL Name filter:

    await db.none('DROP DATABASE $1~', cn.database);
// or
await db.none('DROP DATABASE $1:name', cn.database);
// or
await db.none('DROP DATABASE ${database:name}', cn);