Postgres - npm script to drop/add database

Question

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))

Martijn Pieters · Accepted Answer · 2022-10-07 12:28:23Z

1

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

edited Oct 7, 2022 at 12:28

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered Mar 10, 2020 at 8:21

Amjad Shahzad

8327 silver badges15 bronze badges

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

1 Comment

asus Over a year ago

Thanks! The command that worked for me was "drop-db:local": "psql -U rm -d postgres -p 5432 -f sql/drop_create_db.sql" - different user and omitting the / before sql directory

Collectives™ on Stack Overflow

Postgres - npm script to drop/add database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related