Postgres adapter for Pumlhorse scripts
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
lib
.gitignore
LICENSE
README.md
app.js
package.json

README.md

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