PostgreSQL support in Meteor

In a popular platform for quickly building web applications, official SQL support will soon appear. Previously, developers have repeatedly refused to do this, arguing that SQL does not fit into the philosophy of the project. However, community perseverance has done its job and now you can try the preliminary implementation of SQL support in Meteor. All the details - 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 MongoDB's NoSQL database, which is ideally suited for implementing such meteor chips as delay compensation and reactivity. And the initial lack of SQL support in this ideology can be understood, since for its implementation is required to implement the following:

1. Implement the SQL database on the client side and simulate the execution of all queries on the client to eliminate the delay in the application
2. Implement a subscription to queries that sends changes to clients when updating any tables specified in the query in the database
3. Synchronize a client copy of the database with the server based on subscriptions

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

1. For execution of 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 are made in the client Minimongo
2. The implementation of subscriptions is made on the basis of triggers github.com/meteor/postgres-packages/tree/master/packages/pg/observe-driver . 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 is how a subscription publication with a combination of several tables will look like:

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 ] }) 

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

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