Postgres Tree

An implementation of flexible trees in Postgres

Hands-on example

In your root you must have the following environment variables in a .env
This way node-postgres will set itself up automatically.

PGUSER=testuser
PGHOST=localhost
PGPASS=password
PGDB=testuser
PGPORT=5432

Install the package with: npm i postgres-tree

Next, you can start growing trees as follows:

const PostgresTree = require("postgres-tree").default;

(async () => {
  /* construct the table in the DB and expose an API */
  const tree = await new PostgresTree("nodetree").build();

  await tree.addNode({
    id: 1,
    parent: null,
    name: "Node1",
    offset: 10
  });
  await tree.addNode({
    id: 2,
    parent: 1,
    name: "Node2",
    offset: 0
  });
  await tree.addNode({
    id: 3,
    parent: 1,
    name: "Node3",
    offset: 0
  });
  /*
   * .
   * └── 1
   *     ├── 2
   *     └── 3
   */

  await tree.insertNode(
    {
      id: 4,
      name: "Node4",
      offset: 0
    },
    1,
    2
  );
  /*
   * .
   * └── 1
   *     ├── 4 ── 2
   *     └── 3
   */

  await tree.replacingInsertNode({
    id: 5,
    parent: 1,
    name: "Node5",
    offset: 0
  });
  /*
   * .
   * └── 1
   *     └── 5
   *         ├── 4 ── 2
   *         └── 3
   */

  await tree.moveSubtree(5, null);
  /*
   * .
   * └── 1
   * └── 5
   *     ├── 4 ── 2
   *     └── 3
   */

  const leaves = await tree.getLeaves();
  console.log(leaves);
  /*
  [
    { id: 1, parent_id: null, name: 'Node1', ofset: 10 },
    { id: 2, parent_id: 4, name: 'Node2', ofset: 0 },
    { id: 3, parent_id: 5, name: 'Node3', ofset: 0 }
  ]
  */

  const roots = await tree.getRoots();
  console.log(roots);
  /*
  [
    { id: 1, name: 'Node1', ofset: 10 },
    { id: 5, name: 'Node5', ofset: 0 }
  ]
  */

  await tree.createView();

  const view = await tree.view();
  console.log(view);
  /*
  [
    { id: 1, ancestors: [], depth: 0 },
    { id: 5, ancestors: [], depth: 0 },
    { id: 3, ancestors: [ 5 ], depth: 1 },
    { id: 4, ancestors: [ 5 ], depth: 1 },
    { id: 2, ancestors: [ 5, 4 ], depth: 2 }
  ]
  */

  await tree.destroy();
})().catch(err => {
  console.error(err);
});

DSL Interface

class PostgresTree {
  constructor(table) {
    this.client = new Pool({
      user: process.env.PGUSER,
      host: process.env.PGHOST,
      database: process.env.PGDB,
      password: process.env.PGPASS,
      port: process.env.PGPORT
    });
    this.table = table;
  }
}

/**
 * Get the postgres Pool client
 *
 * @returns the pg Pool object
 * @memberof PostgresTree
 */
client();

/**
 * Build (CREATE) the table in the database
 *
 * @returns the PostgresTree class
 * @memberof PostgresTree
 */
build();

/**
 * Destroy (DELETE) the table in the database
 *
 * @returns {Bool} true when deleted, false otherwise
 * @memberof PostgresTree
 */
destroy();

/**
 * Given a the id of a node, get all its descendants
 *
 * @param {Int} id
 * @returns {Array[Node]} the array of Node objects
 * @memberof PostgresTree
 */
async getDescendants(id)

/**
 * Given a the id of a node, get all its ancestors
 *
 * @param {Int} id
 * @returns {Array[Node]} the array of Node objects
 * @memberof PostgresTree
 */
async getAncestors(id)

/**
 * Add a Node to the tree given its id, parentId, name and offset*
 *
 * @param {Object} { id, parent, name, offset } (= Node)
 * @returns
 * @memberof PostgresTree
 */
async addNode({ id, parent, name, offset })

/**
 * Move a node and its descendants to a newParentId
 *
 * @param {Int} nodeId
 * @param {Int} newParentId
 * @returns {Bool} true when succeeded, false otherwise
 * @memberof PostgresTree
 */
async moveSubtree(nodeId, newParentId)

/**
 * Move a nodeId's descendants to a newParentId
 *
 * @param {Int} nodeId
 * @param {Int} newParentId
 * @returns {Bool} true when succeeded, false otherwise
 * @memberof PostgresTree
 */
async moveDescendants(nodeId, newParentId)

/**
 * Remove a node and transfer its children to its parent
 *
 * @param {Int} id
 * @returns {Node} the removed Node
 * @memberof PostgresTree
 */
async removeNode(id)

/**
 * Remove a node and its descendants
 *
 * @param {Int} id
 * @returns {Bool} true when succeeded, false otherwise
 * @memberof PostgresTree
 */
async removeNode(id)

  /**
   * Insert a node between node X and Y
   *
   * @param {Node} { id, name, offset }
   * @param {Int} x
   * @param {Int} y
   * @returns {Bool} true when succeeded, false otherwise
   * @memberof PostgresTree
   */
  async insertNode({ id, name, offset }, x, y)

  /**
   * Insert a node and inherit its parents children
   *
   * @param {Node} { id, parent, name, offset }
   * @returns {Bool} true when succeeded, false otherwise
   * @memberof PostgresTree
   */
  async replacingInsertNode({ id, parent, name, offset })

/**
 * Get all leaves of the tree
 *
 * @returns {Array[Node]} the array of Node objects
 * @memberof PostgresTree
 */
  async getLeaves()

/**
 * Get all roots of the tree
 *
 * @returns {Array[Node]} the array of Node objects
 * @memberof PostgresTree
 */
  async getRoots()

/**
 * Create an ancestral view of the tree
 *
 * @returns {Array[{id, [NodeId], depth}]}
 * @memberof PostgresTree
 */
  async createView()

/**
 * Get the ancestral view of the tree
 *
 * @returns {Array[{id, [NodeId], depth}]}
 * @memberof PostgresTree
 */
  async view()

I've implemented the tree methods with Common Table Expressions (CTEs) and the classical Adjacency List / Closure Table approach. It serves as an alternative for the ltree datatype which Postgres natively supports.

Offset is a reserved keyword in SQL so I opted for ofset as the column-name instead.