search

overlookmotel / sequelize-hierarchy

Nested hierarchies for Sequelize
MIT License
299 stars 92 forks source link

readme

sequelize-hierarchy.js

Nested hierarchies for Sequelize

NPM version Build Status Dependency Status Dev dependency Status Greenkeeper badge Coverage Status

What's it for?

Relational databases aren't very good at dealing with nested hierarchies.

Examples of hierarchies are:

To store a hierarchy in a database, the usual method is to give each record a ParentID field which says which is the record one level above it.

Fetching the parent or children of any record is easy, but if you want to retrieve an entire tree/hierarchy structure from the database, it requires multiple queries, recursively getting each level of the hierarchy. For a big tree structure, this is a lengthy process, and annoying to code.

This plugin for Sequelize solves this problem.

Current status

API is stable. All features and options are fairly well tested. Works with all dialects of SQL supported by Sequelize (MySQL, Postgres, SQLite) except for Microsoft SQL Server.

Requires Sequelize v2.x.x, v3.x.x, v4.x.x or v5.x.x. Supports only Node v8 or higher.

Usage

Loading module

To load module:

const Sequelize = require('sequelize-hierarchy')();
// NB Sequelize must also be present in `node_modules`

or, a more verbose form useful if chaining multiple Sequelize plugins:

const Sequelize = require('sequelize');
require('sequelize-hierarchy')(Sequelize);

Initializing hierarchy

Model#isHierarchy( [options] )

const sequelize = new Sequelize('database', 'user', 'password');

const Folder = sequelize.define('folder', { name: Sequelize.STRING });
Folder.isHierarchy();

Folder.isHierarchy() does the following:

The column and table names etc can be modified by passing options to .isHierarchy(). See below for details.

via Sequelize#define() options

Hierarchies can also be created in define():

const Folder = sequelize.define('folder', {
  name: Sequelize.STRING
}, {
  hierarchy: true
});

or on an attribute in define():

const Folder = sequelize.define('folder', {
  name: Sequelize.STRING,
  parentId: {
    type: Sequelize.INTEGER,
    hierarchy: true
  }
});

If defining the hierarchy via model options, do not also call .isHierarchy(). The two methods are equivalent - only use one or the other.

Creating database tables

Defining the hierarchy sets up the models in Sequelize, not the database tables. You will need to create or modify the tables in the database.

If table already exists, add the following columns:

If the table does not already exist, you can ask Sequelize to create it:

await Folder.sync();

NB Call .sync() after .isHierarchy().

The ancestry model (FolderAncestor in the above example) also needs its database table created:

await sequelize.models.FolderAncestor.sync();

Retrieving hierarchies

Examples of getting a hierarchy structure:

// Get entire hierarchy as a flat list
const folders = await Folder.findAll();
// [
//   { id: 1, parentId: null, name: 'a' },
//   { id: 2, parentId: 1, name: 'ab' },
//   { id: 3, parentId: 2, name: 'abc' }
// ]

// Get entire hierarchy as a nested tree
const folders = await Folder.findAll({ hierarchy: true });
// [
//   { id: 1, parentId: null, name: 'a', children: [
//     { id: 2, parentId: 1, name: 'ab', children: [
//       { id: 3, parentId: 2, name: 'abc' }
//     ] }
//   ] }
// ]

// Get all the descendents of a particular item
const folder = await Folder.findOne({
  where: { name: 'a' },
  include: {
    model: Folder,
    as: 'descendents',
    hierarchy: true
  }
});
// { id: 1, parentId: null, name: 'a', children: [
//   { id: 2, parentId: 1, name: 'ab', children: [
//     { id: 3, parentId: 2, name: 'abc' }
//   ] }
// ] }

// Get all the ancestors (i.e. parent and parent's parent and so on)
const folder = await Folder.findOne({
  where: { name: 'abc' },
  include: [ { model: Folder, as: 'ancestors' } ],
  order: [ [ { model: Folder, as: 'ancestors' }, 'hierarchyLevel' ] ]
});
// { id: 3, parentId: 2, name: 'abc', ancestors: [
//   { id: 1, parentId: null, name: 'a' },
//   { id: 2, parentId: 1, name: 'ab' }
// ] }

The forms with { hierarchy: true } are equivalent to using Folder.findAll({ include: { model: Folder, as: 'children' } }) except that the include is recursed however deeply the tree structure goes.

Accessors

Accessors are also supported:

folder.getParent()
folder.getChildren()
folder.getAncestors()
folder.getDescendents()

Setters work as usual e.g. folder.setParent(), folder.addChild().

Options

The following options can be passed to Model#isHierarchy( { /* options */ } ) or in a model definition:

const Folder = sequelize.define('folder', {
  name: Sequelize.STRING
}, {
  hierarchy: { /* options */ }
});

Defaults are inherited from sequelize.options.hierarchy if defined in call to new Sequelize().

Examples:

Folder.isHierarchy( { as: 'above' } );

const Folder = sequelize.define('folder', {
  name: Sequelize.STRING
}, {
  hierarchy: { as: 'above' }
});

Aliases for relations

These affect the naming of accessors e.g. instance.getParent()

Fields

Hierarchy (through) table

All auto-created field names respect the setting of model.options.underscored and the through table name respects sequelize.options.define.underscoredAll.

Cascading deletions

Misc

Rebuilding the hierarchy

Model#rebuildHierarchy( [options] )

To build the hierarchy data on an existing table, or if hierarchy data gets corrupted in some way (e.g. by changes to parentId being made directly in the database not through Sequelize), you can rebuild it with:

await Folder.rebuildHierarchy()

NB: In normal circumstances, you should never need to use this method. It is only intended for the above two use cases.

Bulk creation

You can use .bulkCreate() method in the usual way. Ensure that parents are created before their children.

Errors

Errors thrown by the plugin are of type HierarchyError. The error class can be accessed at Sequelize.HierarchyError.

Tests

Use npm test to run the tests. Use npm run cover to check coverage.

To run tests on a particular database, use npm run test-mysql, npm run test-postgres, npm run test-postgres-native, npm run test-sqlite or npm run test-mssql.

Requires a database called 'sequelize_test' and a db user 'sequelize_test' with no password.

Changelog

See changelog.md

Issues

If you discover a bug, please raise an issue on Github. https://github.com/overlookmotel/sequelize-hierarchy/issues

Contribution

Pull requests are very welcome. Please: