This package allows you to use PostgreSQL reactively with Meteor as seamlessly as possible.
It provides a method to publish PostgreSQL queries on the server. The query result will be available reactively on the client in a Minimongo collection. The collection can be used in the usual way.
Data modifications (UPDATE, INSERT) can only be made from the server side. There are methods available that you can call from your server-side methods.
It has been used in a small scale production environment quite successfully.
For a full working example take a look at: https://github.com/Richie765/meteor-pg-leaderboard
Requires PostgresSQL version 9.3 or above.
meteor npm install meteor-pg --saveThere are two environment variables used to configure your database connection.
export PG_URL=postgres://user:password@host:port/db
export PG_CHANNEL=your_channel
meteor runThe channel is used for LISTEN/NOTIFY on the PostgreSQL database and cannot be used by more than one application on the same database.
On the server side, import the package early on to establish the database connection. Your /server/main.js file would be a good place to do this.
import 'meteor-pg';Only use this for read-only SELECT queries that you want to be reactive. For non-reactive (SELECT) queries you can use a method.
Within a publish function on the server:
return mpg.select(collection, query, params, triggers);| Parameter | Description |
|---|---|
collection |
The name of the Minimongo collection where the results will be stored on the client-side. |
query |
SELECT query to run and observe. May contain placeholders following pg-promise. Each row must contain a unique _id field as described below. |
params |
The parameters to the query, following pg-promise. Single values will be $1. Array elements will be $1..$n. Object properties will be $*property* where ** is one of (), [], {} or //. See pg-promise for details. |
triggers |
function(change). The trigger function, see below. |
Your query must always return an _id field which uniquely identifies
each row returned. This is needed so that the changed rows can be identified.
For simple queries, this could just be an alias to the PK.
For multi-table queries, this could be a combination of different PK's, eg:
SELECT CONCAT(userid, '-', taskid) AS _id, * FROM user, task;This does not mean you have to include the PK's of all the tables involved. You just need to uniquely identify each row returned.
This function will be called whenever there is a change to one of the underlying tables of the query.
You should determine if this change requires a rerun of the query. If so, you should return true.
One parameter is passed, change. It contains the following fields:
| Field | Description |
|---|---|
table |
String, name of the table that changed. This will always be in lowercase. |
insert |
For INSERT, true |
delete |
For DELETE, true |
update |
For UPDATE, an object that contains the old and new values of each changed column. If a column score changed from 10 to 20, change.update.score.from would be 10 and change.update.score.to would be 20. |
row |
The row values, for UPDATE, the NEW row values |
old |
For UPDATE, the OLD row values |
ES6 syntax makes it easy to write your trigger function, e.g.:
function trigger({ table, row }) {
if(table === 'user' && row.name === 'name') return true;
if(table === 'task' && row.status === 'completed') return true;
}// Server side
import mpg from 'meteor-pg';
Meteor.publish('allPlayers', function() {
let sql = `
SELECT id AS _id, *
FROM players
ORDER BY score DESC
`;
function triggers() {
// This function is rather important.
// For now, just trigger any change
return true;
}
return mpg.select('players', sql, undefined, triggers);
});
// Client side
Players = new Mongo.Collection('players');
Template.leaderboard.onCreated(function () {
this.subscribe('allPlayers');
});
Template.leaderboard.helpers({
players: function () {
// Still need to sort client-side since record order is not preserved
return Players.find({}, { sort: { score: -1, name: 1 } });
},
});The mpg object provides the following methods that you can call within your Meteor methods to execute INSERT and UPDATE (and non-reactive SELECT) statements: query, none, one, many, oneOrNone, manyOrNone, any, result, stream, func, proc, map, each, task, tx.
These methods take the same parameters as the methods of pg-promise.
The difference is that these are called within a fiber using Promise.await, so they wait for the statement to be executed. You can
use the return value directly, it is not a 'promise'.
// Server side
import mpg from 'meteor-pg';
Meteor.methods({
'incScore': function(id, amount){
let sql = `
UPDATE players
SET score = score + $[amount]
WHERE id = $[id]
`;
mpg.none(sql, { id, amount });
}
});
// Client side
Meteor.methods({
// Optional stub for latency compensation, see note below
'incScore': function(id, amount){
Players.update(id, { $inc: { score: amount } });
}
});
Template.leaderboard.events({
'click .inc': function () {
Meteor.call('incScore', Session.get("selectedPlayer"), 5);
}
});For running complex queries, involving multiple JOIN's and/or additional processing in JavaScript,
you can use mpg.table_observer and mpg.query_observer directly. For the documentation see
https://github.com/richie765/pg-table-observer and https://github.com/richie765/pg-query-observer.
If you need direct access to the pg-promise object or database handle, they are available as mpg.pgp and mpg.db.
- There is some flicker when using latancy compensation (client-side methods).
- MongoDB is still required for Accounts (pull requests welcome)