Publishing data

You can publish pretty much any query you can run on your Postgres database, and it will be reactively re-run and updates pushed to the client. Depending on how complex your query is and whether you prefer to use a query builder or a raw SQL string, there are different ways to publish the data.

Since SQL queries can do joins and aggregates, it's very possible to end up with data on the client which has a different schema than the table on the server. We'll talk about how to deal with that in some of the more complex cases listed below.

Publishing a simple select on a table

If you just have some rows in your database that you want to use on the client, publishing the data is very simple, and the schema on the client will be the same as on the server.

With Knex

// Define the table
Todos = new PG.Table("todos");

// Publish the data
Meteor.publish('todos', function(listId) {
  // Check arguments - note that IDs are integers
  check(listId, Match.Integer);

  // Build a query with Knex and return it
  return Todos.where("list_id", listId);
});

With raw query

Notice that when you don't use the query builder, you need to manually specify the name of the table with publishAs so that Meteor knows what table to put the data in on the client.

Read more here: Knex raw queries.

// Define the table
Todos = new PG.Table("todos");

// Publish the data
Meteor.publish('todos', function(listId) {
  // Check arguments - note that IDs are integers
  check(listId, Match.Integer);

  // Build a query with Knex and return it; you need to set the name of the
  // table manually
  return PG.knex.raw(
    "SELECT * FROM todos WHERE list_id=?",
    [listId]
  ).publishAs("todos");
});

Publishing data with aggregate columns

If you want to add some extra rows to your database that are aggregates or join results, the schema of your table on the client will be different from that of the server.

Consider the following query:

select "lists".*, count(todos.id)::integer as incomplete_count
from "lists"
left join "todos" on
  "todos"."list_id" = "lists"."id" and
  "todos"."checked" = FALSE
where "user_id" is null
group by "lists"."id"

This selects rows from the lists table, and then adds an extra column called incomplete_count which contains the number of todos that aren't checked off.

In this case, on the client the published data will have one more column than the table on the server, so you should consider this when making queries on the client or server.

Publishing with Knex

Here is how you could write this query with Knex (it's a little ugly, I know):

Meteor.publish("publicLists", function () {
  return Lists
    .select("lists.*", PG.knex.raw("count(todos.id)::integer as incomplete_count"))
    .where({user_id: null})
    .leftJoin("todos", function () {
      this.on("todos.list_id", "lists.id")
        .andOn("todos.checked", "=", PG.knex.raw("FALSE"));
    })
    .groupBy("lists.id")
    .from("lists");
})

Publishing with raw SQL query

Note that you can use ES2015 template strings to write multiline queries nicely. Also, Knex handles writing is null for you based on the object you pass to .where(), but in raw SQL you have to know ahead of time if the argument is going to be null or not.

Meteor.publish("publicLists", function () {
  return PG.knex.raw(`
select "lists".*, count(todos.id)::integer as incomplete_count
from "lists"
left join "todos" on
  "todos"."list_id" = "lists"."id" and
  "todos"."checked" = FALSE
where "user_id" is null
group by "lists"."id"
  `).publishAs("lists");
})

Publishing two different views on the same table

// XXX use publishAs to specify different table names for the client