How can I make an init.sql script run when using Docker with Postgresql?

Viewed 95

I'm trying to run docker and postgresql. To do this, I have such a docker-compose.

version: '3'

services:
    postgres:
        image: postgres:12
        restart: always
        networks:
            - backend
        ports:
            - '5432:5432'    
        volumes:
            - ./db_data:/var/lib/postgresql/data
            - ./app/config/init.sql:/docker-entrypoint-initdb.d/create_tables.sql
        env_file:
            - ./app/config/.env
        healthcheck:
            test: [ "CMD", "pg_isready", "-q", "-d", "devdb", "-U", "postgres" ]
            timeout: 45s
            interval: 10s
            retries: 10
    
    app:
        build: app
        ports:
            - 3200:3200
        networks:
            - backend
        depends_on:
            postgres:
                condition: service_healthy

volumes:
    db_data:

networks:
    backend:
        driver: bridge

now I'm creating an init.sql file:

app/config/init.sql

CREATE TABLE todos(
    id SERIAL PRIMARY KEY NOT NULL,
    title VARCHAR NOT NULL,
    description VARCHAR NOT NULL,
    creating_time timestamp
);

I use the pgx driver to communicate with Postgresql:

insertQuery  string = `INSERT INTO todos(title, description, creating_time) VALUES($1, $2, $3)`
dbUrl := fmt.Sprintf("postgres://%s:%s@%s:5432/%s", connInfo.User, connInfo.Password, connInfo.Host, connInfo.DBName)
db, errDB := pgxpool.Connect(context.Background(), dbUrl)
if errDB != nil {
    fmt.Println(errDB)
}

conn, err := db.Acquire(context.Background())
if err != nil {
    fmt.Errorf("Unable to acquire a database connection: %v\n", err)
}
defer conn.Release()
datetime := time.Now()
dt := datetime.Format(time.RFC3339)
_, err = db.Exec(context.Background(), insertQuery, "todo.Title", "todo.Description", dt)
if err != nil {
    fmt.Println(err)
}

And when the container starts, everything goes fine until it comes to executing the request. Then I get an error relation "todos" does not exist at character 13

I've never done this before, so I can't figure out what's wrong.The connection to the database is normal, since it does not issue an error, but for some reason the script for creating the table is not executed.I will be glad to help how to solve this problem.

1 Answers

how's going?

This entry point that you're configure would work only if does not exists any database configured. As you can see here. The main objective of this entry point is configure the database to be used, once this is already made does not make sense executing again.

My suggestion to you is use this lib. It is pretty straightforward to use it. If you want to, I can share some examples.

Please let me know if this helped you.

Related