How to write a bash script that access to database inside docker then insert data?

Question

Provided, I have to go through a route of

Get into docker
Get into postgreSQL database
Create table
Insert data

Which is very troublesome to do it frequently since I am testing. I want to write a bash script (.sh file) that do everything in 1 run.

Here are all my command line I need to run

docker exec -it <mycontainer> bash

psql -h localhost -p 5432 -U postgres

CREATE SCHEMA bank;

CREATE TABLE bank.holding (
    holding_id int,
    user_id int,
    holding_stock varchar(8),
    holding_quantity int,
    datetime_created timestamp,
    datetime_updated timestamp,
    primary key(holding_id)
);

insert into bank.holding values (102100, 2, 'VFIAX', 10, now(), now());

You will need to run them one at a time using the -c option to psql. For example psql -h localhost -p 5432 -U postgres -c 'create schema bank; and then sql -h localhost -p 5432 -U postgres -c 'create table bank.holding ( ... ); and so on. You can put all your command in a file and then use the -f (--file) option to read all commands from a file. That would likely be the better call, e.g. psql -h localhost -p 5432 -U postgres -f file-w-create.txt. — David C. Rankin
– David C. Rankin, Commented May 30, 2022 at 5:46
PostqreSQL Docker image provides a way initilize data using scripts. Please read section Initialization scripts from its official docs. TLDR; just mount your sql scrips inside /docker-entrypoint-initdb.d using docker volumes, and it will run on database init — usuario
– usuario, Commented May 30, 2022 at 6:27
Also consider using your application framework's database migration system to run these (and especially the CREATE TABLE commands). — David Maze
– David Maze, Commented May 30, 2022 at 10:37

Tolis Gerodimos · Accepted Answer · 2022-05-30 07:25:52Z

2

Firstly, it will assist you on automating the procedure if you expose your container port

docker container run ... -p 5432:5432 ...

The above procedure will enable you to access the container port, by directly accessing your localhost:5432

Next run

psql -h localhost -p 5432 -U postgres -f bash_automation_script.txt

bash_automation_script.txt

CREATE SCHEMA bank;

CREATE TABLE bank.holding (
    holding_id int,
    user_id int,
    holding_stock varchar(8),
    holding_quantity int,
    datetime_created timestamp,
    datetime_updated timestamp,
    primary key(holding_id)
);

insert into bank.holding values (102100, 2, 'VFIAX', 10, now(), now());

answered May 30, 2022 at 7:25

Tolis Gerodimos

4,5832 gold badges13 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

How to write a bash script that access to database inside docker then insert data?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related