An early look at SQL in Meteor

announce
22

nice work. I extended the work for Apache couchDB - https://github.com/mariobriggs/meteor-accounts-couchdb/

23

I’m using alasql as a client only persistent storage and it provides a very fast and small local database engine, with almost full SQL DML and DDL capacity.

24

I just finished reading “Your First Meteor Application” and want to use PostgreSQL.

The document http://meteor-postgres.readthedocs.org/en/latest/ is good but I find it hard to follow

The use of additional packages (React, Router, etc). As I do not see any require(...) it was hard to find out which meteor packages are used as there are too many packages with the same name.

If there is a similar book “Your First Meteor Application with PostgreSQL” it would be great.

25

Any news regarding this feature? Cheers.

26

This project is not actively worked on right now, but we respond to all PRs pretty quickly. Take a look yourself: https://github.com/meteor/postgres-packages/pulls?utf8=✓&q=is%3Apr+

1 Like
28

Meteor’s github project looks like abandonware. @numtel, would you say that we can use your PG package in production?

29

Yep - the Meteor Postgres experiment was never intended to be a production-level fully maintained project. It says that in several places on the repo. But I think some of the ideas are good, and could be integrated into packages such as @numtel’s.

1 Like
30

@sashko, Meteor is so good that we are trying to find workable solutions to one of the most important parts of the framework, Database access. I don´t know if you have tried, but there is no serious way to develop a Line of Business app using MongoDB.The fact that we are trying to use it instead of something like sails.js despite the lack of interest of the MDG about this feature is a good indicator of how good are the other parts of Meteor.I hope this changes soon.Regards

31

I seem to remember you stating in a thread a few weeks back that someone was actively working on options to fulfill roadmap request #1. Is this still the case?

*edit:

33

I’m pretty sure that It’s hard to manage a LOB appp without an ACID compliant database.
Also I guess the maintenance its kind of hard.

34

Hi vlasky,

I am also working on mysql with Meteor using Ben’s package, good to know that you already are using it in production. Is it possible for you to share the Meteor accounts changes that you have done with the community.

1 Like
35

Hello athakwani,

We did not end up porting Meteor’s Accounts package to MySQL. After analysing the existing code, we saw that it heavily depended on nested JSON objects.

MySQL 5.6 and below do not have native JSON support, which means that nested objects would have to be replaced with multiple table joins and/or application-level JSON handling.

Also, we couldn’t find an in-built way to hook/intercept all the points in the code that access the Mongo Collections.

For this reason, we created our own proprietary (and barebones) accounts system which did not have all the bells and whistles of Meteor’s.

MySQL 5.7 now has a JSON datatype so we may revisit the task of porting Meteor’s native accounts package in the near future.

The other alternative for the time being is to use Mongo only for Accounts and MySQL for everything else.

36

Is that true? I am new to mongoDB and used it for few apps. I enjoy using it and do not feel that way like you do…

1 Like
38

Hi all,

We’ve been building and maintaining a small line of business application since mid 2012. It’s used by, at most, 10 concurrent users and the dataset is small (an uncompressed MongoDB dump is 58MB).

We started out using Django and MySQL but switched to Meteor mid 2013 because we thought the client, server, and API development benefits out-weighed the lose of an RDBMS.

After 2 years, our code base is now mostly attempts to implement RDBMS features, e.g.; unique, joining, ordering, aggregation, transactions, ACID, skip, limit, cascade delete, nulls first/last, auditing. In order to get everything robust we’ve had to;

  • serialised all writes to the MongoDB database (limiting us to one instance);
  • denormalized;
  • stored derived fields;
  • use non-reactive server-side joins;
  • create upper-case and lower-case versions of each string field.

We’ve been playing with Meteor PostgreSQL for about a week and we’re very impressed. We fixed so many problems, it’s way faster, and we thrown away so much code. Also, auxiliary backup/restore type tasks have become way easier. We’re keeping MongoDB for accounts because we don’t want to fight Meteor. Here is the pattern we’re currently using;

  1. Pure, normalised tables within PostgreSQL. All the checks and constraints that you want.

    CREATE TABLE customers (
    id SERIAL PRIMARY KEY
    , name TEXT NOT NULL
    );

    CREATE TABLE sites {
    id SERIAL PRIMARY KEY
    , name TEXT NOT NULL
    , customer_id INTEGER NOT NULL REFERENCES customers (id);
    );

  2. For each use of the data on the client (e.g., a table of sites, or a select input of customer names), we create a view in PostgreSQL. The view maps the internal data into a Mongo-style, Meteor-friendly, and JavaScrip-friendly shape

    CREATE VIEW sites_table_view AS
    SELECT
    sites.id AS “siteId”
    , sites.name
    , customers.id AS “customerId”
    , customers.name AS "customerName"
    FROM customers
    LEFT JOIN companies ON site.customer_id = companies.id;

  3. We create a client-side only collection for each view.

SitesTableRows = new Mongo.Collection('/sites/table');

  1. We create a publication that lets the client subscribe to a window of the whole view.

Because there may be many sites tables on the screen, each client passes a tag so that they can pull out the window they’ve asked for from the client-side collection.

We let PostgreSQL do all the sorting and we add an index column so that the client-side collection can get them in the order PostgreSQL dictated.

Finally, we create an ID (aptly id) that’s unique across all subscriptions.

This is the step where we have to fight knex, but is the same across all publications, so it can be abstracted. I’ve omitted much of the argument checks for clarity. I’ve omitted apply filters because it’s as you’d expect.

Meteor.publish('/sites/table', function (tag, filters, order, limit, skip) {
  check(tag, TagPattern);
  check(filters, { name: Match.Optional(NonEmptyString) });
  check(order, OrderPattern);
  check(limit, NonNegativeInteger);
  check(skip, NonNegativeInteger);

  const orderParts = order.map(([column, direction]) => `${ column } ${ direction } NULLS LAST`).join(', ');

  const query = PG.knex('sites_table_view')
    .select('*')
    .select(PG.knex.raw(`('${ tag }'::text || '-'::text || "siteId") AS id`)
    .select(PG.knex.raw(`'${ tag }'::text AS tag`))
    .select(PG.knex.raw(`CAST(row_number() OVER (ORDER BY ${ orderParts }) AS INTEGER) AS index`));
  
  /* apply filter using query.where() */

  query.orderBy('index');
  query.limit(limit);
  query.offset(offset);
  query.publishAs('/sites/table');
  return query;
}

  1. On the client, we have reactive sources for filters, order, limit, offset.

    Template.SitesTable.onCreated(function () {
    this._tag = Random.id();
    this.autorun(computation => {
    this.subsribe(’/sites/table’, this._tag, this.data.filters.get(), this.data.order.get(), this.data.limit.get(), this.data.offset.get());
    });
    });

    Template.SitesTable.helpers({
    rows: function () {
    const tag = Template.instance()._tag;
    return SitesTableRows.find({tag: tag}, { sort: [[‘index’, ‘asc’]] });
    }
    });

We’re not experts at Meteor, PostgreSQL, or MongoDB, I just wanted to share our experience. Any opinions or feedback is welcome.

6 Likes
39

Hey @foxxy,

Thank you for this post! I would be curious to hear more about a couple of things:

  • Do you use minimongo methods on the client or the proposed Knex-like API on top of minimongo?
  • Are your methods “isomorphic”, i.e. do most of your Meteor Methods run both on Client and the Server?
  • How was handling migrations with knex?
  • Any other problems that you faced you want tell us about?
1 Like
41

Hi @slava

Firstly, thanks for this package, it’s exactly what we’ve been waiting for.

We use standard minimongo methods on the client-side. In fact, we don’t really use Knex server-side: much of the functionality we want isn’t supported by knex. So, we end up with raw fragments everywhere. Our approach is to make PostgreSQL do the the heavy lifting (e.g., denormalizing relations and selecting derived values), shove the returned rows into a collection, and operate on the collection as if it was stored in MongoDB in a MongoDB-style.

Nope, most of our methods require aggregation (sum of items to be build sort of thing) over datasets that are too large for the client. So, MongoDB or PostgreSQL, our methods would be server-side only. Our use case doesn’t have many fast changing parts, so latency compensation is not really an issue for us.

We’re yet to handle migrations (this is still a dev version of the application). We’re unlikely to use knex because it doesn’t support all the syntax we want. If I have to write lots of raw knex stuff, I might as well write a pgsql script. I also like keeping the web app and database separate as there are other, non-Meteor parts of the system (a program that scrapes dBASE IV .dbf files from a NetWare 4 file share running inside a VirtualBox (where is dBASE support on your road map)).

I’m not sure what use-case your looking at. We’re probably not it because our user base and data set is so small. In an ideal world, we be able to execute arbitrary commands and publish the cursor. Now, for our needs, we can probably get away with poll-and-diff (like Meteor in the early days, before watching the oplog).

I’ve been looking at the code and I think it works by taking the query and calling explain (format json) to find the tables to watch with triggers. This doesn’t work for:

  -- Selecting a function, not a table or view.
  select * from my_function(x, y);`

because explain does not list the relations used in my_function.

I’m a bit of a security freak, so I don’t like the web app db user to be able to create triggers or functions. I haven’t looked enough at the code yet but is look like it might be possible to create functions that the web app user could call to create the triggers it needs.

I’ll report back as we make more progress and when it’s in production.

8 Likes
42

I know this is just a preview, but it looks great so far!

The last commit was in October, are there any plans to still maintain this?

Thanks!

43

@renatoat Check https://github.com/apollostack/apollo & http://info.meteor.com/blog/reactive-graphql

44

Hi Ben,

I’d like to know more about your MSSQL integrations. I’d like to just poll from our production mssql db using meteor, while also saving any data to mongo.

I’ve got three VMs running (2 REHL, 1 for mongodb and the other hosting the meteor app with Passenger, and 1 ms sql server) all are talking with each other with mssql query data coming back as an array in json format.

I don’t require any livequery support/reactive data, just polling from the ms sql db.

Any suggestions would be really appreciated.

45

There’s a new package that brings PostgreSQL support to Meteor:

Enjoy!

5 Likes