Search code examples
postgresqlnpmpsql

Postgres - npm script to drop/add database

I want to simplify my life and automate the process of adding/dropping my test db via an npm script, however I am running into issues.

Attempt 1:

"drop-db:local": "psql postgres \"drop database blog_db; create database blog_db; \\c blog_db; CREATE EXTENSION \"pgcrypto\";\""

After running this, I keep getting the following error

psql: error: could not connect to server: FATAL:  Peer authentication failed for user "drop database blog_db; create database blog_db; \c "

Attempt 2:

changed psql postgres to psql -h localhost -U rm postgres

So this opens the db in my terminal but that seems to ignore some stuff as mentioned in the msg below

psql: warning: extra command-line argument "drop database blog_db; create database blog_db; \c blog_db; CREATE EXTENSION pgcrypto;" ignored

What am I doing wrong?

This is a list of my db users

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rm        | Superuser, Create DB                                       | {}

db version: psql (12.2 (Ubuntu 12.2-2.pgdg18.04+1))

Solution

  • You need to use -c or -f with your psql command. As the psql help shows:

    -c, --command=COMMAND    run only single command (SQL or internal) and exit
-f, --file=FILENAME      execute commands from file, then exit

    As you are using multiple commands so it will be better of you use -f followed by a sql file name that has all the commands e.g your drop_create_db.sql file can have following code:

    drop database blog_db; 
create database blog_db; 
\c blog_db; 
CREATE EXTENSION "pgcrypto";

    And you may run this file by using the following command

    "drop-db:local": psql -U postgres -d postgres -p 5432 -f /tmp/drop_create_db.sql