pumlhorse-postgres

Provides Postgres functions for Pumlhorse scripts

Wraps pg package

Installing npm module

npm install pumlhorse-postgres

Connecting to a database

The connect function takes a connection string. See pg documentation for connection string details.

modules: 
  - pg = pumlhorse-postgres
steps:
  - pg.connect: postgres://my_username:my_password@server_name/database_name

It's likely that you would want to store this connection string in a context file and reference the variable instead.

Inserting data

The following code inserts three records into the myFavoriteMovies table.

modules:
  - pg = pumlhorse-postgres
steps:
  - pg.connect: $postgresConnectionString
  - pg.insert:
      table: favoritemovies
      data:
        - name: Shawshank Redemption
          stars: 4.5
          notes: Excellent cinematography
          reviewer: $username
          reviewDate: ${new Date()}
        - name: The Matrix
          stars: 4.5
          notes: Groundbreaking
          reviewer: $username
          reviewDate: ${new Date()}
        - name: Hot Rod
          stars: 5
          notes: Just plain perfect
          reviewer: $username
          reviewDate: ${new Date()}

Retrieving data

Assuming that the data above has been inserted, we can retrieve it like so

modules:
  - pg = pumlhorse-postgres
steps:
  - pg.connect: $postgresConnectionString
  - movies = pg.query:
      parameters:
        - 4
      sql: >
             SELECT name, stars, notes, id
             FROM favoritemovies
             WHERE stars >= $1
             ORDER BY stars, name DESC
  - for:
      each: row
      in: $movies
      steps:
        - log: 
            - %s (%s stars) - %s
            - $row.name
            - $row.stars
            - $row.notes

The code above outputs the following lines:

• Hot Rod (5 stars) - Just plain perfect
• Shawshank Redemption (4.5 stars) - Excellent cinematography
• The Matrix (4.5 stars) - Groundbreaking

If you don't want to use the for function, you can reference the result as an array:

  - log: $movies[0].name # logs "Hot Rod"

Multiple connections

If your script needs multiple connections, you can explicity pass the connection to the functions

modules:
  - pg = pumlhorse-postgres
steps:
  - conn1 = pg.connect: $connection1String
  - conn2 = pg.connect: $connection2String
  - pg.insert:
      connection: $conn1
      table: table1
      data:
        - #table1 data
  - pg.insert:
      connection: $conn2
      table: table2
      data:
        - #table2 data
  - movies = pg.query:
      connection: $conn1
      parameters:
        #parameters
      sql: #SQL query