CODEWITHSUNDEEP
postgresqlgithub-actions
asus
asus

Reputation: 1759

github actions - run sql script in postgres service

I want to run a script in the postgres service in github actions that creates a table and adds an extension. How can I do that? Do I need to make a shell script or can I do right in the yaml file?

sql script

drop database mydb;
create database mydb;
\c mydb;
CREATE EXTENSION "pgcrypto";

workflow

name: API Integration Tests

on: 
    pull_request:
    push:
        branches:
            -master

env:
    DB_HOST: localhost
    DB_USERNAME: postgres
    DB_PASSWORD: rt

jobs:
    build:
        runs-on: ubuntu-latest

        strategy:
          matrix:
            node-version: [10.x, 12.x, 13.x]


    services:
        postgres:
          image: postgres:latest
          env:
            POSTGRES_DB: mydb        
            POSTGRES_PASSWORD: helloworl
            POSTGRES_USER: postgres
          ports:
            - 5433:5432
          # Set health checks to wait until postgres has started
          options: >-
            --health-cmd pg_isready
            --health-interval 10s
            --health-timeout 5s
            --health-retries 5
    steps:
        - uses: actions/checkout@v1
        - name: Use Node.js ${{ matrix.node-version }}
            uses: actions/setup-node@v1
            with:
            node-version: ${{ matrix.node-version }}
        - name: npm install
            run: npm ci
        - name: npm test
            run: npm run test

Upvotes: 8

Views: 5291

Answers (2)

Attila Fulop
Attila Fulop

Reputation: 7011

Tim's answer is almost perfect; what's missing is the host, port, and DB configuration. Extensions are DB specific, so you need to tell psql which database to connect to. Additionally, the Github actions assigned port needs to be resolved:

jobs:
  test:
    # ...
    services:
      postgres:
        image: postgres
        env:
          POSTGRES_PASSWORD: secret
          POSTGRES_DB: testdb
        ports:
          - 5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:
      # ...
      - name: Enable Postgres Trigram Extension
        run: |
          PGPASSWORD=secret psql -U postgres -h 127.0.0.1 -p ${{ job.services.postgres.ports[5432] }} -d testdb -tc "CREATE EXTENSION pg_trgm;"

Upvotes: 0

Tim
Tim

Reputation: 807

You can add a step that uses PSQL commands.

Here's an example step that creates your database:

- name: Create database
  run: |
     PGPASSWORD=helloworl psql -U postgres -tc "SELECT 'CREATE DATABASE mydb' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')"

By the way, I note that the next command you wanted was: CREATE EXTENSION "pgcrypto";, which I assume is because you want to generate UUIDs (Common use case). Please note that you do not need this for get_random_uuid() as this is natively support in Postgres from v13 onwards.

However if you really, really, really wanted to add pgcrypto, you can use this step:

- name: Enable pgcrypto extension
  run: |
     PGPASSWORD=helloworl psql -U postgres -tc "CREATE EXTENSION 'pgcrypto';"

Upvotes: 4

Related Questions