PostgreSQL support in Meteor

The popular platform for quickly creating web applications will soon receive official SQL support. Previously, developers have repeatedly refused to do this, arguing that SQL does not fit into the philosophy of the project. However, the persistence of the community has done its job, and now you can try the preliminary implementation of SQL support in Meteor. All the details are under the cut!

Meteor is a powerful tool for quickly creating web applications, but the lack of SQL support severely limits its scope. Initially, Meteor is based on NoSQL MongoDB, which is ideal for implementing meteor counters such as delay compensation and reactivity. And the initial lack of SQL support in this ideology can be understood, because to implement it, you need to implement the following:

1. Implement an SQL database on the client side and simulate the execution of all queries on the client to avoid delays in the application
2. To implement a subscription to requests, which carries out the distribution of changes to customers when updating in the database any tables specified in the request
3. Synchronize the client copy of the database with the server based on subscriptions

The Space Elephant team www.meteorpostgres.com has already tried to solve these problems . Based on their experience, official support is now being implemented. The code is available in the GitHub repository github.com/meteor/postgres-packages . Looking at the implementation, we have the following:

1. To execute SQL on the client, Knex knexjs.org is used , which builds the structure of the SQL query and on the basis of which the corresponding changes in the client Minimongo are performed
2. The implementation of the subscriptions is based on the github.com/meteor/postgres-packages/tree/master/packages/pg/observe-driver triggers . Those. In the query, based on the SQL query, the necessary triggers are created in the database, which send pg_notify messages about changes in the database

Here's what a subscription publication will look like with multiple tables joined:

Meteor.publish("user-posts-and-their-comments", function(userId) {
    const postsQuery = Posts.knex()
        .select("posts.*")
        .innerJoin("users", "posts.user_id", userId);
    const commentsQuery = Comments.knex()
        .select("comments.*")
        .innerJoin("posts", "comments.post_id", "posts.id")
        .innerJoin("users", "posts.user_id", userId)
    return [
        postsQuery,
        commentsQuery
    ]
})

The developers posted all the examples here: github.com/meteor/postgres-packages/tree/master/examples

Now the developers are actively improving support for PostgreSQL and will add support for other SQL databases in the future.