Seeding database in GitLab CI

Question

I'm currently working on the GitLab CI pipeline for my Node.js web app.

In my current GitLab CI setup I follow the same steps for a couple of CI jobs:

1. Use the Node image

2. Spin up services: Postgres, Minio, web app

3. Run npm commands that create the data we need to have in the database

Simplified, this is what the gitlab-ci.yml looks like:

validate-snippets:
 stage: test
 interruptible: true
 needs:
 - build docker image
 - npm_ci
 variables:
 FF_NETWORK_PER_BUILD: 'true'
 DATABASE_HOST: database
 DATABASE_PORT: 5432
 DATABASE_NAME: #
 DATABASE_USER: #
 DATABASE_PASSWORD: #
 services:
 - name: postgres:15@sha256:24d6c206bba8c0440bceb24a8d4bf642f60bf7aea94887051ea5761d29c22323
 alias: database
 variables:
 POSTGRES_DB: #
 POSTGRES_USER: #
 POSTGRES_PASSWORD: #
 - name: minio/minio:latest@sha256:14cea493d9a34af32f524e538b8346cf79f3321eff8e708c1e2960462bd8936e
 alias: minio
 entrypoint: ['sh', '-c', 'minio server /data']
 variables:
 MINIO_ROOT_USER: #
 MINIO_ROOT_PASSWORD: #
 - name: '$CI_REGISTRY_IMAGE:$CI_COMMIT_REF_SLUG'
 alias: appsemble
 variables:
 SECRET: secret
 AES_SECRET: secret
 DATABASE_HOST: #
 DATABASE_PORT: #
 DATABASE_NAME: #
 DATABASE_USER: #
 DATABASE_PASSWORD: #
 script:
 - npm run scripts create-user --
 --name $BOT_ACCOUNT_NAME
 --email $BOT_ACCOUNT_EMAIL
 - npm run scripts organization create testOrg
 - npm run scripts block publish 'blocks/*'
 - # // Run the command for this job

Is there a way to perform this seed job once to populate the database with data, create a copy of the database and then re-use that copy of the database in different jobs so they all start with their own copy of the database?

I unfortunately cannot make a simple init.sql file to seed the Postgres image, because the block publish command performs a lot of steps which I'm not sure can be replicated in the SQL file.

Answer 1

Keep your full npm-based seeding, but capture the final database dump in a job artifact. You can then use that in the testing jobs.

# Consider using CI/CD variables for these sensitive values
# (Settings > CI/CD > Variables), rather than hard-coding like this
variables:
 # Database configuration
 DATABASE_NAME: "your_db_name"
 DATABASE_USER: "your_db_user" 
 DATABASE_PASSWORD: "your_db_password"
 DATABASE_HOST: "database"
 DATABASE_PORT: "5432"
seed-database:
 stage: prepare
 variables:
 FF_NETWORK_PER_BUILD: 'true'
 APP_SECRET: "secret"
 APP_AES_SECRET: "secret"
 services:
 - name: postgres:15@sha256:24d6c206bba8c0440bceb24a8d4bf642f60bf7aea94887051ea5761d29c22323
 alias: database
 variables:
 POSTGRES_DB: $DATABASE_NAME
 POSTGRES_USER: $DATABASE_USER
 POSTGRES_PASSWORD: $DATABASE_PASSWORD
 - name: minio/minio:latest@sha256:14cea493d9a34af32f524e538b8346cf79f3321eff8e708c1e2960462bd8936e
 alias: minio
 entrypoint: ['sh', '-c', 'minio server /data']
 variables:
 MINIO_ROOT_USER: $MINIO_USER
 MINIO_ROOT_PASSWORD: $MINIO_PASSWORD
 - name: '$CI_REGISTRY_IMAGE:$CI_COMMIT_REF_SLUG'
 alias: appsemble
 variables:
 SECRET: $APP_SECRET
 AES_SECRET: $APP_AES_SECRET
 DATABASE_HOST: $DATABASE_HOST
 DATABASE_PORT: $DATABASE_PORT
 DATABASE_NAME: $DATABASE_NAME
 DATABASE_USER: $DATABASE_USER
 DATABASE_PASSWORD: $DATABASE_PASSWORD
 script:
 # Your full seeding process
 - npm run scripts create-user -- --name $BOT_ACCOUNT_NAME --email $BOT_ACCOUNT_EMAIL
 - npm run scripts organization create testOrg
 - npm run scripts block publish 'blocks/*'
 # Export the populated database
 - apt-get update && apt-get install -y postgresql-client
 - pg_dump -h $DATABASE_HOST -U $DATABASE_USER $DATABASE_NAME > seeded_database.sql
 # Adjust expiry to suite
 artifacts:
 paths:
 - seeded_database.sql
 expire_in: 2 hours
validate-snippets:
 stage: test
 interruptible: true
 needs:
 - seed-database
 - build docker image
 - npm_ci
 variables:
 FF_NETWORK_PER_BUILD: 'true'
 services:
 - name: postgres:15@sha256:24d6c206bba8c0440bceb24a8d4bf642f60bf7aea94887051ea5761d29c22323
 alias: database
 variables:
 POSTGRES_DB: $DATABASE_NAME
 POSTGRES_USER: $DATABASE_USER
 POSTGRES_PASSWORD: $DATABASE_PASSWORD
 - name: minio/minio:latest@sha256:14cea493d9a34af32f524e538b8346cf79f3321eff8e708c1e2960462bd8936e
 alias: minio
 entrypoint: ['sh', '-c', 'minio server /data']
 variables:
 MINIO_ROOT_USER: $MINIO_USER
 MINIO_ROOT_PASSWORD: $MINIO_PASSWORD
 # Load the pre-seeded database
 before_script:
 - apt-get update && apt-get install -y postgresql-client
 - psql -h $DATABASE_HOST -U $DATABASE_USER -d $DATABASE_NAME < seeded_database.sql
 script:
 - # Your actual test commands here

• mike: Another way might be to build a custom docker container image, with the database pre-seeded, and then push it to your project's container registry. Then have just the validate-snippets job, but with your custom image. If I can, I'll add that as a second answer. I haven't worked out how to include the seeded DB into the image.