An early look at SQL in Meteor

announce
1

One of the design decisions we made to build Meteor was initially only supporting MongoDB as the backing database. This decision has affected all parts of the stack, for example the use of Minimongo on the client. So while it was obvious that Meteor would eventually need support for other databases, as mentioned in the Meteor roadmap blog post, it was very unclear what that support would look like. We had a lot of internal design discussions and considered many approaches, but at the end of the day there was only one way to find out: Build a prototype and try using it.

And that’s what we’ve done: we have a first prototype of support for using PostgreSQL as the database for a Meteor app, while maintaining all of the features that makes Meteor so great to use:

  1. Live-updating database queries
  2. The ability to use similar code on the client and the server
  3. Automatic optimistic UI
  4. Synchronous-style APIs enabled by Fibers

Try it out

We have written down our thoughts and directions for getting started with this preview in a guide, available here: Meteor PostgreSQL

Give us feedback!

Give the guide a read through to see what we came up with, and try it out by running the example apps! Let us know what you think on this thread, and file any issues you encounter on GitHub.

1158Ă—754 160 KB

36 Likes
2

Amazing! Great job guys, this really begins to open up so many possibilities… Can’t wait to dive into the examples and provide some feedback on the direction…

Best,

David

3

Verry happy to hear that this feature is progressing! Also very happy that your first attempt is with Postgres. That’s the database I’m most interested in using with meteor.

The main thing I would be looking for in sql support is transactions.

http://meteor-postgres.readthedocs.org/en/latest/transactions/ mentions that synchronous calls mean that transactions aren’t needed. This is incorrect. Many of the methods I see using with meteor/postgres will need transaction support. Virtually any method created that modifies multiple tables will need it.

4

The text you pointed out is taking about the browser cache which doesn’t need transactions

5

@sashko & team & all the gurus: need your support for Ubuntu and WIndow. We doesn’t have mac computer :smile:

1 Like
6

So excited about this. Read through the examples and docs and it all looks good to me.

7

My current architecture involves a bunch of celery tasks that run python code on a periodic basis to access various APIs. A dozen or so tasks, running some running every couple of minutes and some just once or twice a day. This is all organized through Django, but there is no UI to speak of. I have hooks in the Django ORM layer, which is backed by PostgreSQL, to automatically synchronize updates to my MONGO_URL, and then I use Meteor to build a cool UI layer over all the data – with stats and summaries that update in real time.

(In fact much of the state of my app can be completely rebuilt just by running through all the hooks on all the tables, so I think of the MONGO_URL layer as a sort of semi-persistence layer anyway – all the stats and summaries can be quickly recomputed – I’m thinking of recommending this architecture.)

Anyway, I’m hoping that this package will allow me to have both a POSTGRES_URL and a MONGO_URL in the same app – I’d love to bring my architecture all the way around and get a nice little distributed system going, with things synchronizing every which way.

3 Likes
8

This is SO great!. Thanks for accelerating this. Please take a look to AlaSql for client side work in addition to Knex.
Best regards!

9

This looks great. Will there be support for using multiple SQL databases or SQL and mongodb at the same time? Also I did not see how to publish none reactive data to the client. How is that done?

10

Great news! Finally the dreams of using meteor and normal sql databases are becoming true.

Thank you, @sashko. Please, keep developing this project so we can see it in the docs in the near future. :wink:

3 Likes
11

I have no doubt this will increase the Meteor adoption. At least I will be able to play more times the Meteor card when designing a new app :smile:

1 Like
12

I agree: this is sure to increase Meteor adoption, if and when it is ready for prime time.

13

wondering how reactivity works with Postgres… is it polling or what?

14

From the code it looks like it uses both triggers and poll & diff:

15

Hiyas, I’d like to share my experiences using Meteor with MySQL.

One of my companies is using Ben Green’s numtel:mysql package, which provides reactive support in Meteor for MySQL. It relies on the MySQL binary log (using row mode) as the event source - similar to how the oplog is used with MongoDB.

We are running it in a production system used for real-time location tracking. We recently submitted some improved code which dramatically increased its performance and Ben has incorporated it into the latest release.

We chose MySQL over PostgreSQL for two reasons:

*We already had a LEMP-based ecosystem for our other apps and use MySQL slave-replication.

*We are using the TokuDB storage engine, which is not supported by PostgreSQL.

TokuDB is a fantastic piece of technology that has improved our database performance because of its more efficient fractal tree data structure. It has reduced our storage requirements thanks to its support for table compression and it has allowed easier maintenance by supporting online schema and index changes.

TokuDB is only officially supported with the MySQL forks Percona Server and MariaDB. Only Percona Server is compatible with Meteor because the current stable version of MariaDB (10.0) does not record the precision of datetime/timestamp fields in its binary log, but this will be fixed in MariaDB 10.1.

I strongly encourage the MDG to implement official Meteor support for MySQL based on Ben Green’s package.

At the moment, there is no “minisql” equivalent of “minimongo” so we sometimes use Mongo.Collections in memory as temporary storage, e.g. where the option “connection:null” is set.

However, I just did some googling and found sql.js - a port of sqlite to JavaScript - https://github.com/kripken/sql.js/ - I wonder if this could form the basis of a “minisql” for Meteor.

Also, the current official Meteor accounts packages are directly coupled with MongoDB, so we’ve had to develop our own accounts system.

It would be great if the official accounts-base code could be tweaked to allow users to add handlers to intercept/override the bits of code that initialize and access the Mongo.Collection “users”.

5 Likes
16

The comment says: “Note that the client-side stub is synchronous, so there is no need for a transaction”

I’m just referring to the method simulation. Clearly the server-side SQL calls will need transactions. We already have the implementation, just need to document it: https://github.com/meteor/postgres-packages/blob/c91c47d53cb8c8566a6e2c704add3cc653690fc4/packages/accounts-base-pg-driver/accounts-base-pg-driver.js#L78-L87

@tsamatech this should work fine on Windows, we just don’t have documentation for how to install PostgreSQL there. Have you tried it?

@jononomo you should be able to connect to Mongo and Postgres at the same time, if you pass both MONGO_URL and POSTGRESQL_URL and add the mongo and pg packages. Let me know if you get around to trying it!

@vchigne you mentioned AlaSql - what benefits would developers get if we supported it? I feel like shipping a whole SQL implementation to the client would have to bring a lot to be worth it (the Knex over Minimongo implementation is very light)

@vlasky implementing MySQL shouldn’t be too hard - there is only a little PostgreSQL-specific code. Hopefully we can build a proof of concept soon.

Thanks for all of the feedback so far!

1 Like
17

Wondering if -with the the coice for knex, which does not support it as far as I can see- MS SQL is out of scope? I think meteor would gain al lot by focussing on MS SQL (lots of users).

18

11/10, would meteor again

2 Likes
19

Yeah although I think it’s a much smaller step to just add support to Knex for MS SQL. This is still on the path to that eventual future.

20

If you would like to talk more about MSSQL support, send me an email. (ben@latenightsketches.com) While working on the Postgres live select package, I did research into MSSQL and it’s definitely possible.