3

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.

georgehdd
  • 433
  • 3
  • 12
  • 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 May 16 '16 at 14:09

1 Answers1

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.

Community
  • 1
  • 1
VonC
  • 1,262,500
  • 529
  • 4,410
  • 5,250
  • So I'll need to add another docker file for the database ? – georgehdd May 16 '16 at 05:35
  • @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. – VonC May 16 '16 at 10:11
  • 1
    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. – Gabriel Kohen Jan 08 '19 at 15:23
  • 1
    @GabrielKohen Agreed. These days (two years later), this would be done with Kubernetes and an operator (as in https://medium.com/@cloudark/why-to-write-kubernetes-operators-9b1e32a24814) – VonC Jan 08 '19 at 16:15