How to create database, schema, and then table inside Postgresql using init.sql

Question

I am trying to create an init.sql file to automatically create an user, database, schema, and table. Currently, the user and database are created, but I am having troubles with my schema and table. Please see below for my code:

docker-compose:

postgres_db:
image: postgres:14.1
restart: unless-stopped
environment:
  POSTGRES_USER: ${SERVER_DB_USER}
  POSTGRES_PASSWORD: ${SERVER_DB_PASS}
volumes:
  - ./data/postgres:/var/lib/postgresql/data
  - ./admin/config:/docker-entrypoint-initdb.d
ports:
  - "5432:5432"
networks:
  - bypass

init.sql file inside /admin/config:

CREATE USER bypass;
CREATE DATABASE bypass;
GRANT ALL PRIVILEGES ON DATABASE bypass TO bypass;

\c bypass;
CREATE SCHEMA bypass_project;

CREATE TABLE bypass_project.Bypass_Data (
    id serial PRIMARY KEY,
    Date_Time TIMESTAMP,
    Module VARCHAR(25),
    Area VARCHAR(25),
                         );

After running docker-compose up, I am not able to see the schema and the table. I'm pretty sure \c bypass is used incorrectly. How do I connect to the bypass database and create everything there?

Are you sure that you want do it here? Usually it's being done at application level with some migrations mechanism. — Yevhenii Kosmak
– Yevhenii Kosmak, Commented Dec 23, 2021 at 0:09
I am definitely new to database management. I am making an application that will continuously write data from Python to Postgresql. I assumed that the database structure had to be constructed before data can flow into it. What would be the best practices to put in init.sql files and where would I populate other items? — Rui Nian
– Rui Nian, Commented Dec 23, 2021 at 0:15

Oleksii Tambovtsev · Accepted Answer · 2021-12-23 00:35:42Z

2

Your definitely have errors in your SQL syntax in the CREATE TABLE statement. You should remove trailing comma:

CREATE TABLE bypass_project.Bypass_Data (
    id serial PRIMARY KEY,
    Date_Time TIMESTAMP,
    Module VARCHAR(25),
    Area VARCHAR(25)
);

Also you can try (second bypass is your DB user):

\c bypass bypass;

instead of

\c bypass;

Also \c is a command specific to psql. If you try to execute an SQL script containing such command in another client, it will not work.

In this case you can try to use init.sh script instead of init.sql and use psql in your bash-script. You can see full example here.

If it does not help you, could you provide more information? I think docker-compose log would be helpful.

edited Dec 23, 2021 at 0:35

answered Dec 23, 2021 at 0:23

Oleksii Tambovtsev

2,9041 gold badge6 silver badges24 bronze badges

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

Comments

Yevhenii Kosmak · Accepted Answer · 2021-12-23 00:37:10Z

1

You should leave just this in your init.sql:

CREATE USER bypass;
CREATE DATABASE bypass;
GRANT ALL PRIVILEGES ON DATABASE bypass TO bypass;

Then you should expose the DB port and connect to it from your application container. If you are using Python, as you mentioned in comment, you should use some DB migrations mechanism, like Django ORM or SQLAlchemy. On deployment of python application migration mechanism should be called, it would actualise the state of your database.

Here is an example of configuration of Django + PostgreSQL setup and here is a guide about Django migrations.

answered Dec 23, 2021 at 0:37

Yevhenii Kosmak

3,8802 gold badges13 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to create database, schema, and then table inside Postgresql using init.sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related