When I need to use MySQL in production

[tenzan]

These days, is it still true statement: "If you need to use MySQL in production - don’t use Meteor" ?

Or there’s still some tweaks? I will also need to create REST APIs and same reactiveness as with mongo…

[maxhodges]

SQL is on the Meteor Roadmap but presently there is no official support. Your best bet is to try the experimental third-party SQL packages on atmosphere
https://atmospherejs.com/?q=sql

but keep in mind you may have to rework your application when official support is introduced.

May I ask why you need SQL? I’m a former SQL DBA but now as a product manager I really prefer mongo for operational data. It’s much faster to get things in production when you don’t need to first define and implement a normalized relational data model. Mongo is more flexible and “lean” startup friendly.

If you have legacy data to access is it read-only? Can you port it to mongo? Or can you access it by putting an API in front?

Let us know a bit more about you use-case and maybe we can offer some alternative solutions.

[maxhodges]

this just announced

An early look at SQL in Meteor

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 …

[vlasky]

This is not a true statement at all!

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

Contrary to what many people believe, normalized relational data models are often often very efficient, promote consistency and reduce the need for storage space. Many people confuse non-normalized database schemas (a result of laziness or ignorance of system analysis and design) with denormalized databases (a deliberate design decision to reduce the level or normalization to improve performance in specific circumstances).

Our existing webapps and RESTful services were developed to operate within a LEMP ecosystem (Linux, Nginx, MySQL, PHP). We have introduced Meteor (NodeJS) into this ecosystem to create our new generation of reactive webapps.

At the moment, the main limitations is that there is no ‘minimysql’ equivalent of ‘minimongo’ and that the official Meteor accounts packages are coupled with MongoDB. We have dealt with this in two ways:

1. We’ve had to develop our own MySQL-based accounts system.
2. We sometimes use Mongo.Collections (minimongo) in memory as temporary storage, e.g. where you pass the option “connection:null” to the constructor.

I am very keen to share my tips with others who want to benefit from the amazing combo of Meteor & MySQL.

For those running Meteor under Linux, I recommend the MySQL fork Percona Server 5.6 for the following reasons:

1. I find Percona Server 5.6 to be stable and well-supported

2. I consider Percona Server 5.6 preferable to Oracle MySQL because of its inbuilt support for the TokuDB storage engine.

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.

Unfortunately, the current stable version of MariaDB 10.0 is not suitable - it has a broken implementation of row-mode binary logging that doesn’t transmit the size/precision of timestamp/datetime fields, which confuses the binlog-following code in numtel:mysql. The MariaDB maintainers have fixed this in the 10.1 release, which is still in beta.

[tenzan]

Thanks, guys!

Here I’ve posted a more specific question

When dealing with `many-to-many` relations in MySQL using Meteor

Please consider this as part of the When I need to use MySQL in production I made separate topic by making a specific question. Let’s see a classic many-to-many relations as below with a join table called teacher_student. I have 2 questions: Can this relation be ported into Mongo ? AFAIK, when it comes to many-to-many relation, the only way is to deal with RDBMS? If I will want to use this in MySQL, is it possible to deal with this schema and keep the reactiveness feature of Meteor ? …

[maxhodges]

We have introduced Meteor (NodeJS) into this ecosystem to create our new generation of reactive webapps.

At the moment, the main limitations is that there is no ‘minimysql’ equivalent of ‘minimongo’

Does this mean you’re not actually benefitting from Meteor’s latency compensation i.e. Optimistic UI?

Many people confuse non-normalized database schemas (a result of laziness or ignorance of system analysis and design) with denormalized databases (a deliberate design decision to reduce the level or normalization to improve performance in specific circumstances).

That’s true but perhaps an oversimplification of the benefits of NoSQL. It’s not that noSQL developers simply lack discipline regarding data persistence, it’s that we’ve come to view NoSQL as a superior choice for operational data overall.

As a product manager I find that developers are able to build much faster when they don’t need to wait for a DBA to design some rigid schema (especially in the beginning when we don’t yet fully understand the data model anyway.)

I have decades of experience with RDBMS in enterprise, including a position at Dell Computer Japan as the DBA for their online store. At first the NoSQL approach was very foreign to me, and I had to re-learn a lot of data modeling ideas. But now I actually prefer mongoDB for operational data when building large complex web applications. But you can always write the important data out to a traditional RBDMS, or Amazon RedShift, for data-warehousing, reporting, accounting, etc.

So the way I think about MongoDB is that if you take MySql, and change the data model from relational to document based, you get a lot of great features: embedded docs for speed, manageability, agile development with schema-less databases, easier horizontal scalability because joins aren’t as important.

[titolancreo]

Is there any way to use your package with ES6? I’m not able to import the class using meteor with Angular2 (typescript)

[korus90]

You could try Apollo Graphql instead, docs can be found here: http://dev.apollodata.com/

It’s database-agnostic, so you can use whatever database you want. However, you don’t get true ‘reactivity’, you have to poll the database using pollInterval or use postgres for the listen/notify feeds that make it awesome. Or RethinkDB as that ‘pushes’ changes to the front-end.

In the last year a lot has happened!

[titolancreo]

Apollo is not an option because we can’t poll the database. That’s why I think that using mysql bin logs its perfect for our purposes.

I’m trying to replicate their code to use it with ES6, but I don’t know how can I subscribe with Meteor.subscribe.

[vlasky]

I am not sure why ES6 would be an issue. It is backwards compatible with ES5.

What happens when you add vlasky:mysql to your project?

[titolancreo]

Hi!

The problem is that I can’t import LiveMysql and LiveMysqlKeySelector packages. Meteor don’t recognize them.

server/main.ts (21, 26): Cannot find module 'meteor/vlasky:mysql

I don’t know if I’m trying to import it wrong.
This is how I’m doing it:

import {LiveMysql} from ‘meteor/vlasky:mysql’

But also, It does not exist any export of LiveMysqlKeySelector on your code, so I can’t use it.

I tried another thing. I have copied your LiveMysql.js as typescripts of my project and also I copied LiveMysqlKeySelector from mysql-live-select (as ts). I install all the npm dependencies and I had to add them to both files with Npm.require. Also I had to fix LiveMysql.js of mysql-live-select adding var EJSON = require('ejson');

Doing this, I was able to connect to the database, but I’m still having problems fetching the data. I tried using MeteorObservable.subscribe and Meteor.subscribe and the collection is always empty.

Example:

this._playersSub = MeteorObservable.subscribe('allPlayers').subscribe(() => {
        this.player= Players.find({}).zone();
    }
);      


this._playersSub2 = Meteor.subscribe('allPlayers2').subscribe(() => {
        this.player2= Players.find({}).zone();
    }
);      


this._playersSub3 = MeteorObservable.subscribe('allPlayers3').subscribe(data => {
        this.player3= data;
    }
); 

this._playersSub4 = Meteor.subscribe('allPlayers4').subscribe(data => {
        this.player4= data;
    }
);      

Thanks!

[vlasky]

@titolancreo, I have not yet had the need to use ES6 with meteor, so pardon my ignorance.

I am going to presume that you ran “meteor add vlasky:mysql” before attempting to use the ES6 import statement.

It’s a stab in the dark, but I have just released version 1.2.6 of vlasky:mysql. It includes some changes to packages.js to hopefully improve ES6 compatibility, so give it a try.

If someone here has ES6 expertise and can advise me of other changes that may be required to make my module fully compatible with the ES6 import syntax, I will incorporate them.

[titolancreo]

Yes, I did it. I’m going to try this new version and I’lll tell you if it works.

[none]

Small example with Meteor and MySQL (-:

[titolancreo]

Hi,

It is not working yet with meteor & Angular2.

I ran:

meteor add vlasky:mysql

Then, inside server/main.ts

import { Meteor } from 'meteor/meteor';
import {LiveMysql, LiveMysqlKeySelector} from 'meteor/vlasky:mysql'
//LiveMysqlKeySelector is not exported on the package, so it is not working.

Meteor.startup(() => {

});

var liveDb = new LiveMysql({
    host: 'localhost',
    port: 3306,
    user: 'api',
    password: 'xxxxxxx',
    database: 'test'
});

Meteor.publish('allPlayers', function(){
    return liveDb.select(
        `SELECT * FROM players ORDER BY score DESC`,
        null,
        LiveMysqlKeySelector.Index(),
        [ { table: 'players' } ]
    );
});

I don’t need to import LiveMysql to use it and it connects to mysql without problems.

But there are some problems when I subscribe to the event:

First, EJSON is not defined in mysql-live-select/lib/LiveMysql.js, so it fails. To fix it you just need to add to https://github.com/vlasky/mysql-live-select/blob/master/lib/LiveMysql.js

var EJSON = require('ejson');

Then, when it is solved, I get this error:

 Exception from sub allPlayers id zTmxvsnsextQuP36r TypeError: Cannot read property 'Index' of undefined

The problem is that LiveMysqlKeySelector is not exported in your package.

I export that class inside my code in order to use it, and the publish doesn’t throw any error, but I don’t know why the array is always empty.

PD: Thanks @none, but I can’t use Apollo.

[vlasky]

LiveMysqlKeySelector is located in the NPM package vlasky:mysql-live-select.

If you look at the end of lib/LiveMysqlKeySelector.js, you will see the export line:

module.exports = LiveMysqlKeySelector;

[titolancreo]

Yes, of course, but you also need to export it inside the meteor package in order to use it with ES6

[nzomedia]

Hi,
I’m currently trying to create an account system (CRUD user accounts and loggin) using mysql.
We currently use numtel:mysql for data access and reactivity. Can you please give me some guide line about how to make a custom account system with mysql without losing Meteor’s features like having the currently logged in user in Meteor.user() or Meteor.userId ? Any help will be apreciated.

Also thank you for mentionning Percona server, i checked it and it seems very good.
(Sorry for my bad english)

[robfallows]

@nzomedia

Can you please give me some guide line about how to make a custom account system with mysql without losing Meteor’s features like having the currently logged in user in Meteor.user() or Meteor.userId ?

If it was that easy, it would have been done …

Actually it was done by MDG a couple of years ago - with PostgreSQL. You may get some ideas from that work:

postgres-packages/packages at master · meteor/postgres-packages

[nzomedia]

Thank you, i have done some research and found that account-base package (https://github.com/meteor/meteor/tree/devel/packages/accounts-base) is used for creating users as well as logging in.
The problem is that it uses a collection to store logged in users. That collection (accounts.users) is in turn used everywhere else to get user data. I also found that we can create custom logging handlers via this dated tutorial: https://meteorhacks.com/extending-meteor-accounts/

But since we need to have user data from a SQL database and it has to be reactive, custom login handler isn’t enough. I’m hopping postgres exemple will be usefull.
Thanks again