A relational database handler for jsonapi-server
Branch: master
Clone or download
pmcnr-hx Merge pull request #98 from unchartedsoftware/master
When creating a field, use Schema length and/or max validation fields as varchar lengths
Latest commit db4b197 Feb 17, 2018
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
example Standard.js for code style and linting. Oct 1, 2017
lib fix linter errors Feb 12, 2018
test Support latest `jsonapi-server` (including store managed ids). Dec 12, 2017
.coveralls.yml coveralls Nov 20, 2015
.editorconfig intial commit Nov 19, 2015
.eslintrc Standard.js for code style and linting. Oct 1, 2017
.gitignore
.npmignore intial commit Nov 19, 2015
.travis.yml Remove support for Node.js 4. Nov 20, 2017
CHANGELOG.md Update CHANGELOG and bump major version for release. Dec 12, 2017
CONTRIBUTING.md Standard.js for code style and linting. Oct 1, 2017
LICENSE intial commit Nov 19, 2015
README.md Fix column type for `string` properties. Dec 12, 2017
package-lock.json Update CHANGELOG and bump major version for release. Dec 12, 2017
package.json Update CHANGELOG and bump major version for release. Dec 12, 2017
setupDatabase.sh Automation and CI support for multiple dialects. Jul 17, 2016

README.md

Coverage Status Build Status npm version Code Climate Dependencies Status

jsonapi-store-relationaldb

⚠️ PLEASE NOTE: Version 4.x releases erroneously created columns for string properties as Sequelize text type. The behaviour in version 5.x has reverted back to creating columns with the Sequelize string type, which is the more adequate type and was the behaviour in versions 3.x and older. The text columns created by version 4.x will need to be manually migrated to string columns.

jsonapi-store-relationaldb is a relational database backed data store for jsonapi-server.

This project conforms to the specification laid out in the jsonapi-server handler documentation.

Supported Databases

  • Postgres
  • MySQL
  • MariaDB

Usage

var RelationalDbStore = require("jsonapi-store-relationaldb");

jsonApi.define({
  resource: "comments",
  handlers: new RelationalDbStore({
    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 RelationalDbStore = require("jsonapi-store-relationaldb");
var Sequelize = require("Sequelize");

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

jsonApi.define({
  resource: "comments",
  handlers: new RelationalDbStore({
    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 RelationalDbStore()).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 RelationalDbStore()).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.