Getting Started

Welcome to Meteor-Postgres. This document provides a brief introduction to Meteor-Postgres.

Create a SQL Collection

Please set your environmental variable POSTGRES to your connection string. It should look like this:

MP_POSTGRES=postgres://username:password@localhost/database
// alternatively env var can be DATABASE_URL 

In a JS file that's loaded on the client and the server, instantiate your SQL collections.

tasks = new SQL.Collection('tasks');
usernames = new SQL.Collection('usernames');

Setting up server

The following code should run inside:

if (Meteor.isServer){}

Creating tables on the Server

Tables created on the client are not persistent and will need to be recreated each time the code runs. They will automatically be populated with data from the server-side database, but the first time you run the code you will need to create tables on the server-side database (Postgres). For this we have created helper functions. Please use these functions to create tables and relationships, since they are required in order to add the proper NOTIFY functions to the tables. These functions will throw non-breaking errors if they run and the tables already exist. Also please do not use any underscores("_") on your column names, or they will have naming conflicts in miniSQL.

tasks.createTable({text: ['$string'], checked: ["$bool", {$default: false}]}).save();
usernames.createTable({name: ['$string']}).save();
tasks.createRelationship('usernames', '$onetomany').save();

Setting up publishing to client

The select statement generated by the .publish method will define the data set used to populate the client. After it's published, the unsubscribed data will not be available but the published data set will remain reactive.

tasks.publish('tasks', function(){
  return tasks.select('tasks.id as id', 'tasks.text', 'tasks.checked', 'tasks.createdat', 'username.id as usernameid', 'username.name')
              .join(['INNER JOIN'], ["usernameid"], \[\["username", "d"\]\])
              .order('createdat DESC')
              .limit(100)
});

username.publish('username', function(){
  return username.select('id', 'name')
                 .limit(100)
});

Setting up client

The following code should run inside Meteor.isClient

if (Meteor.isClient){}

Creating tables on the client

Create table will determine how you would like to structure your data on the client, from the data made available by the .publish method.

var taskTable = {
  id: ['$number'],
  text: ['$string', '$notnull'],
  checked: ['$bool'],
  usersid: ['$number']
};
tasks.createTable(taskTable);

var usernamesTable = {
  id: ['$number'],
  name: ['$string', '$notnull']
};
usernames.createTable(usernamesTable);

Select Data

var newTasks = tasks.select('tasks.id', 'tasks.text', 'tasks.checked', 'tasks.createdat', 'usernames.name')
                    .join(['OUTER JOIN'], ['usernamesid'], [['usernames', ['id']]])
                    .where("usernames.name = ?", newUser)
                    .fetch();

Insert Data

Insert data as an object of field-value pairs.

tasks.insert({
  text:text,
  checked:false,
  usersid: user
})
.save();

Update Data

Update data as an object of field-value pairs.

tasks.update({
  id: this.id, 
  "checked": !this.checked
})
.where("id = ?", this.id)
.save();

Remove Data

tasks.remove()
     .where("id = ?", this.id)
     .save();

All database method chains must end with fetch or save.

See Detailed Database Methods for more info.