Connect to a Postgres Database and Run a Query From a Bash Script

Published: Jun 7, 2019
Updated: Jun 17, 2022

Table of Contents

Prerequisites #

Files #

A query.sql file:

SELECT 'foo'
WHERE 1 = 1
UNION ALL
SELECT 'bar'
WHERE 2 = 2;

An .env file:

PGHOST='localhost'
PGPORT='5432'
PGDATABASE='some_database'
PGUSER='some_user'
PGPASSWORD='some_password'

A script.sh file:

#!/usr/bin/env bash

# If psql is not available, then exit
if ! command -v psql > /dev/null ; then
  echo "This script requires psql to be installed and on your PATH. Exiting"
  exit 1
fi

# Load database connection info
set -o allexport
source .env
set +o allexport

# Connect to the database, run the query, then disconnect
psql -t -A -f ./query.sql

Usage #

Make it executable:

$ chmod 755 script.sh

Print results to stdout:

$ ./script.sh
foo
bar

Write results to file:

$ ./script.sh > results.txt

Notes #