github actions - run sql script in postgres service

Question

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

Tim · Accepted Answer · 2020-12-02 12:30:54Z

4

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';"

answered Dec 2, 2020 at 12:30

Tim

8072 gold badges11 silver badges23 bronze badges

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

Comments

Attila Fulop · Accepted Answer · 2023-04-03 21:06:56Z

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;"

Collectives™ on Stack Overflow

github actions - run sql script in postgres service

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related