Bitbucket Pipelines : interact with Posgresql database

[Jacques Yakoub]

Hello,

First thing , I read https://community.atlassian.com/t5/Bitbucket-questions/How-do-I-use-Postgres-in-Bitbucket-Pipelines/qaq-p/461910 and https://confluence.atlassian.com/bitbucket/use-services-and-databases-in-bitbucket-pipelines-874786688.html.

For the settings I have no problem but I would to know how to interact with the database ?

For example, load a file of sql insert/create table via psql and then run test

Here is my config file : 

# This is a sample build configuration for Javascript.
# Check our guides at https://confluence.atlassian.com/x/VYk8Lw for more examples.
# Only use spaces to indent your .yml configuration.
# -----
# You can specify a custom docker image from Docker Hub as your build environment.
image: node:6.9.5

pipelines:
  default:
    - step:
        script:
          # Step 1 : Install yarn with the easy way : https://yarnpkg.com/en/docs/install#alternatives-tab
          - curl -o- -L https://yarnpkg.com/install.sh | bash -s -- --version 0.23.4
          # Step 2: Belived https://confluence.atlassian.com/bitbucket/javascript-node-js-with-bitbucket-pipelines-873891287.html#Javascript(Node.js)withBitbucketPipelines-ManagingdependencieswithYarn
          # PATH variable should change like they said
          - export PATH=$HOME/.yarn/bin:$PATH
          #Allow to use mock controllers for test
          - export CONTROLLERS_FOR_TEST=TRUE
          # Step 3: Install app dependencies
          - yarn install
          #Allow to test app in production context
          - export NODE_ENV=production
          #Allow to specify the JWT TOKEN Signature (and not use OAuth0)
          - export SECRET_SIGNATURE_JWT_TOKEN=pokémon47
          #run the test with mock controllers
          - npm test
        services:
          - postgres

# Step 5: Start the test (real database)

definitions:
  services:
    postgres:
      image: postgres:9.3

          #Code not working
          # - docker exec -ti postgres bash
          # prepare PostgreSQL
          # -  psql -U "postgres" -c "CREATE DATABASE custom_db" # create a new database
          # -  psql -U "postgres" -c "CREATE USER custom_user WITH PASSWORD 'custom_pass'" # create a new user : custom_user
          # -  psql -U "postgres" -d "custom_db" -f sql/database_tables.sql # create tables
          # -  psql -U "postgres" -d "custom_db" -f sql/database_grant.sql # give him the credentials
          # -  psql -U "postgres" -d "custom_db" -f sql/test_insert.sql # add basic data inside the database : some parkings and reasons
          # settings for running REST API
          # - export DB_HELPDESK_DB=custom_db
          # - export DB_HELPDESK_USER=custom_user
          # - export DB_HELPDESK_PASS=custom_pass
          # test

[SebC]

Two ways I can think of to interact with your databases; personally I would write a task in JS to interact with Postgres, so that you can run it with npm eg.

npm run load_db

You can also install the postgresql client from the OS packages. Node 6.9.5 is based on debian, so you can use apt-get to install the postgresql-client package.

apt-get update && apt-get install -qy postgresql-client

[Jacques Yakoub]

Thanks but I cannot start or run any psql command  .... Didn't forgot anything ?

postgres -D /var/lib/postgresql/data
or
pg_ctl -D /var/lib/postgresql/data -l logfile start

aren't working ...