How to initialize a postgres database tables in microservice architecture with node running in docker

Question

What is the best practice for initializing a node microservice's tables in a postgres database ? Should it be on service start ?

I'm thinking of copying all the .sql files to the container (during docker build) + install psql into that container, and only run the .sql files during docker run before npm start. Does this make sense ?

I need to consider the fact that soon I will need to manage upgrading the database's tables for the microservice as well.

A) Declaratively - see answer by @VonC; B) Programmatically - you can have your own script load SQL and execute it (useful if you have a dynamic aspect to it). — vitaly-t
– vitaly-t, Commented May 16, 2016 at 14:09

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

One possibility is the one recommended by the Docker Postgres image:

How to extend this image

If you would like to do additional initialization in an image derived from this one, add one or more *.sql or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary).

After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files and source any *.sh scripts found in that directory to do further initialization before starting the service.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 15, 2016 at 21:54

VonC

1.4m569 gold badges4.8k silver badges5.7k bronze badges

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

4 Comments

georgehdd Over a year ago

So I'll need to add another docker file for the database ?

VonC Over a year ago

@georgehdd at least reuse the Dockerfile, but with the additional sql and scripts as described in that section: a new docker build will give you an extended image.

Gabriel Kohen Over a year ago

Usually there are going to be several instances (containers) for the microservice running. If each of them will try to initialize the microservice internal DB they all access you will have race condition. Better do it as part of your service orchestration service-init or your CI/CD.

VonC Over a year ago

@GabrielKohen Agreed. These days (two years later), this would be done with Kubernetes and an operator (as in medium.com/@cloudark/…)

Collectives™ on Stack Overflow

How to initialize a postgres database tables in microservice architecture with node running in docker

1 Answer 1

How to extend this image

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related