jagql / store-sequelize

A relational database handler for jagql

@jagql/store-sequelize

@jagql/store-sequelize is a relational database backed data store for jagql. This is based on (and forked from) jsonapi-store-relationaldb

This project conforms to the specification laid out in the jagql handler documentation.

Supported Databases

• Postgres (>= 9.4)
• MySQL
• MariaDB
• SQLite

Usage

var SQLStore = require("@jagql/store-sequelize");

jsonApi.define({
  resource: "comments",
  handlers: new SQLStore({
    dialect: "mysql",
    dialectOptions: {
      supportBigNumbers: true
    },
    host: "localhost",
    port: 3306,
    database: "jsonapi", // If not provided, defaults to the name of the resource
    username: "root",
    password: null,
    logging: false
  })
});

Note: the logging property controls the logging of the emitted SQL and can either be false (which will mean it will be captured by the internal debugging module under the namespace jsonApi:store:relationaldb:sequelize) or a user provided function (e.g. console.log) to which a string containing the information to be logged will be passed as the first argument.

Alternative Usage - Provide Sequelize instance

If you are already using sequelize or need to have access to the sequelize instance, you may provide an instance to the store to be used instead of having the store create a new instance from the given config.

var SQLStore = require("@jagql/store-sequelize");
var Sequelize = require("sequelize");

var sequelize = new Sequelize("jsonapi", "root", null, {dialect: "mysql"});

jsonApi.define({
  resource: "comments",
  handlers: new SQLStore({
    sequelize: sequelize
  })
});

Features

• Search, Find, Create, Delete, Update
• Efficient lookups via appropriate indexes
• Filtering happens at the database layer
• Transactional queries

Getting to Production

Getting this data store to production isn't too bad...

1. Bring up your relational database stack.
2. Create the database(s).
3. Create the database tables. You can call (new SQLStore()).populate() to have this module attempt to create the require tables. If you enable debugging via DEBUG=jsonApi:store:* you'll see the create-table statements - you can target a local database, call populate(), grab the queries, review them and finally run them against your production stack manually.
4. Deploy your code.
5. Celebrate.

When deploying schema changes, you'll need to correct your database schema - database migrations are left as an exercise for the user. If your schema are likely to change frequently, maybe consider using a different (less schema-driven) data store.

When changing columns in a production database, a typical approach might be to create a new table that is a clone of the table in production, copy all data from the production table into the new table, run an ALTER-TABLE command on the new table to adjust the columns (this may take a while and will lock the table), then run a RENAME-TABLES to swap the production table out for the new one.

Note: When populating database tables, you can use the force config option to DROP and CREATE tables. This is helpful in development stage, when your data doesn't matter and you want your Tables schemas to change according to the DAOs without having to manually write migrations.

(new SQLStore()).populate({force: true}, () => {
  //tables dropped and created
})

Gotchas

Relational databases don't differentiate between undefined and null values. Joi does differentiate between undefined and null values. Some undefined properties will pass validation, whilst null properties may not. For example, the default articles resource contains a created attribute of type "date" - this won't pass validation with a null value, so the Joi schema will need tweaking.

store-sequelize's Issues

SELECT "meta" FROM "my_table" AS "my_table"

Hi,

I am working with a postgreSQL database, I simply tried a Get request on 0.0.0.0/my_table and I get

sequelize:pool connection acquired +3s
  sequelize:sql:pg executing(default) : SELECT count(*) AS "count" FROM "my_table" AS "my_table"; +3s
  sequelize:sql:pg executed(default) : SELECT count(*) AS "count" FROM "my_table" AS "my_table"; +2ms
  sequelize:pool connection released +2ms
  sequelize:pool connection acquired +2ms
  sequelize:sql:pg executing(default) : SELECT "id", "meta", "name", "mobilenumber" FROM "my_table" AS "my_table"; +2ms
  sequelize:pool connection released +2ms
  sequelize:pool connection drain due to process exit +3s
  sequelize:pool connection destroy +1ms
...
...
  jagql:requestCounter 0 GET /my_table +0ms
  jagql:validation:input {"type":"my_table"} +0ms
  jagql:handler:search {"type":"my_table","page":{"offset":0,"limit":50}} [{"status":"500","code":"EUNKNOWN","title":"An unknown error has occured","detail":"Something broke when connecting to the database - column \"meta\" does not exist"},null,null] +0ms
  jagql:errors GET /my_table {"status":"500","code":"EUNKNOWN","title":"An unknown error has occured","detail":"Something broke when connecting to the database - column \"meta\" does not exist"} +0ms

However there is no "meta" column in my_table.

ps: Before to use jagql, I tested jsonapi-server and it jsonapi-store-relationaldb, with them, I have "type" and "meta" issues... with jagql, only "meta" issue. so I guess it is not a sequelize's node module's issue

Bad check for number vs UUID in SqlStore.create

The following code in sqlHandler attempts to do an ALTER SEQUENCE if the newResource.id is a number (I guess assuming it is an autoincrement?).

However, parseInt sucks and will give you back a number as long as the FIRST characters of the string are numbers. That means any UUID starting with a number gets pulled in here incorrectly.

lodash.toNumber() correctly returns NaN for any UUID.

SqlStore.prototype.create = function (request, newResource, finishedCallback) {
  var self = this
  self._dealWithTransaction(finishedCallback, function (t, finishTransaction) {
    if (newResource.id === 0) {
      delete newResource.id
    } else if (self.config.dialect === 'postgres') {
      if (!isNaN(parseInt(newResource.id))) {
        /*
        If parseInt(newResource.id) is NaN, it means
        it is 'DEFAULT', which is fine.
        If it **is** a number, then we need to
        update the sequence
         */
        self.sequelize.query(`ALTER SEQUENCE "${newResource.type}_id_seq" RESTART WITH ${parseInt(newResource.id) + 1};`)
          .asCallback(function (err5, result) {})

postgres: restart the sequence when inserting the seed data

we do it for insert queries, but we haven't done it for seed data

Pull in master from holiday extras?

Hi - my company (unchartedsoftware) got a PR pulled into holidayextra's master branch (though a new release hasn't been made). Thinking of switching to your forks, but I'd need that change too.

Thanks!