I have a package for npm that I have built locally for a couple of months. In order to have it work properly, it requires access to a PostgreSQL database. Therefore it needs to, preferably, auto-run a script that creates the database and the tables.
Is this possible, and if possible, where can I make such script? I'd like to automate as much as possible when
psql -qAtX -v ON_ERRORS_STOP=1 -f my_script.sql and error if it returns nonzero. It should be just like running any other command in your install script.
You can have
psql connect to the
postgres database initially, then
CREATE DATABASE mydb; \c mydb
reconnect to the new DB. But personally, I'd have one psql invocation create the DB, then another connect to the new DB. It simplifies error handling.
You can set the postgres instance to connect to via environment variables
PGDATABASE, etc (see the
psql manual) or via parameters passed to the command.
psql will default to the name of the current unix/windows user account if one isn't specified.