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

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?

CodePudding user response：

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.

CodePudding user response：

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.