Performing queries on a Postgres docker container through a bash script

Question

I'm working with a simple postgres database and docker. Currently, I have a docker-compose file which creates the container I need and loads in the SQL files. After loading in this data, I would like it to perform a simple query through a bash script that I"m going to use for some basic tests (i.e., confirm # of rows > 0, etc). To start, I'm just trying to make a simple script that will run and print the number of rows (then I can worry about implementing actual testing). Here is what I have so far:

docker-compose.yml:


services:
  postgres:
    image: postgres
    environment:
      POSTGRES_DB: test-db
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    volumes:
    - ./database/create_table.sql:/docker-entrypoint-initdb.d/create_table.sql
    - ./databases/data.sql:/docker-entrypoint-initdb.d/data.sql
    - ./script/test.sh:/docker-entrypoint-initdb.d/test.sh

test.sh:

 #!/bin/bash

echo "Accessing bash terminal of DB container"
docker exec -it postgres_1 bash

echo "Accessing psql terminal"
psql -U postgres

echo "Connecting to database"
\c database

echo "Checking number of rows"
numrows = $("SELECT count(*) FROM my_table")

echo numrows + " found."

Currently when I run docker-compose up, it creates the data from my SQL files and then stays idle. Is there something additional I need to run my script? I am able to do all of this myself through a separate terminal, but I would like this to all be automated so that I can just add tests to my test.sh and then run that rather than having to do it manually each time. What am I missing here? Shouldn't my script work since I really just recreated the commands I was executing manually? Thanks for any help!

Do you have a working setup that does this without Docker? It seems like your script is trying to intermix commands in three different contexts (the host system, a root shell in the database container, the psql shell); trying to drive this through a standard database client library in a more structured programming language, either from the host or from a separate container, seems like it'd be a much more straightforward approach. — David Maze
– David Maze, Commented Mar 31, 2020 at 21:22
ahh okay, makes sense why that wouldn't work then. So if I'm understanding you correctly, might be better to write up some Java code that can connect to the postgres container and then do everything from there? Makes sense, I guess my initial hope was it could just be done in a short bash file. — Polyphase29
– Polyphase29, Commented Mar 31, 2020 at 21:27
I'm partial to Python, but yes, a shell script isn't quite the right tool here. — David Maze
– David Maze, Commented Mar 31, 2020 at 21:28
In principle you could do something like psql -h localhost -c 'SELECT ...' database and then use a library like shUnit2 as a test framework (connecting to the container's published port; don't bind-mount the test script into the container and don't use docker exec). — David Maze
– David Maze, Commented Mar 31, 2020 at 21:30

Kenan Güler · Accepted Answer · 2020-03-31 23:17:27Z

1

By the time your bash script is executed you are already in the postgres container itself. So, you can simply query the database from there - as @DavidMaze already pointed out. Your script might look like this:

#!/bin/bash
db_name='test-db'
db_user='postgres'

function execute_sql() {
  psql --tuples-only -U $db_user -d $db_name -c "$@"
}

function log() {
  printf "Log [%s]: %s\n" "$(date --iso-8601=seconds)" "$*"
}

numrows=$(execute_sql "SELECT count(*) FROM my_table")

log "${numrows} rows found"

Output should be something like that:

postgres_1  | /usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/test.sh
postgres_1  | [2020-03-31T23:02:14+00:00]:      4 rows found

Regarding the testing: If you only want to run SQL queries and don't do/need additional scripting you can simply put your SQL test queries into a .sql file (e.g. test.sql) as well.

One more important thing to mention - which I'm sure you already know: The files (*.sql, *.sh etc.) that you mount to the postgres container are executed in alphabetical order, i.e.

create_table.sql
data.sql
test.sh

So, you are good.

answered Mar 31, 2020 at 23:17

Kenan Güler

2,0237 silver badges16 bronze badges

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

2 Comments

Polyphase29 Over a year ago

Okay cool, thanks for the suggestion. I'm trying this now and getting this error: /usr/local/bin/docker-entrypoint.sh: /docker-entrypoint-initdb.d/test.sh: /bin/bash^M: bad interpreter: No such file or directory. any idea on how to fix? Seems to be related to me being on Windows and trying to execute in a Linux environment.

Kenan Güler Over a year ago

@Polyphase29 you actually don't need the shebang within your script, since according to its docker-entrypoint the shell scripts found in /docker-entrypoint-initdb.d are being sourced - instead of executed, so it won't respect your #!/bin/bash anyway and execute the script within the current shell - which is the bash itself. (cf. #!/usr/bin/env bash in docker-entrypoint.sh)

Collectives™ on Stack Overflow

Performing queries on a Postgres docker container through a bash script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related