pumlhorse-postgres

0.1.0 • Public • Published

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 

Package Sidebar

Install

npm i pumlhorse-postgres

Weekly Downloads

0

Version

0.1.0

License

MIT

Last publish

Collaborators

  • mdickin