Skip to main content

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

Published: 2019-06-07 • Last updated: 2021-04-06

Table of Contents

# Prerequisites

  • psql (PostgreSQL) version 9.0.x or higher is installed and on your PATH. See install steps for Mac
  • The following files are in the same directory

# Files

A query.sql file:

-- this file must end in a new line
SELECT 'foo'
WHERE 1 = 1
SELECT 'bar'
WHERE 2 = 2;

An .env file:

export POSTGRES_HOST="localhost"
export POSTGRES_PORT="5432"
export POSTGRES_DATABASE="some_database"
export POSTGRES_USERNAME="some_user"
export POSTGRES_PASSWORD="some_password"

A file:

#!/usr/bin/env bash

# Load database connection info
source .env

# Read query into a variable

# 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 ..."
  exit 1

# Connect to the database, run the query, then disconnect
-c "${sql}"

# Usage

Make it executable:

$ chmod 755

Print results to stdout:

$ ./

Write results to file:

$ ./ > results.txt

# Notes

  • There isn’t an option to pass the password, so that’s why the PGPASSWORD environment variable is set
  • The -t option turns off printing of column names and result row count footers
  • The -A option switches to unaligned output mode