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=testuserPGHOST=localhostPGPASS=passwordPGDB=testuserPGPORT=5432
Install the package with: npm i postgres-tree
Next, you can start growing trees as follows:
const PostgresTree = default; async { /* construct the table in the DB and expose an API */ const tree = await "nodetree"; await tree; await tree; await tree; /* * . * └── 1 * ├── 2 * └── 3 */ await tree; /* * . * └── 1 * ├── 4 ── 2 * └── 3 */ await tree; /* * . * └── 1 * └── 5 * ├── 4 ── 2 * └── 3 */ await tree; /* * . * └── 1 * └── 5 * ├── 4 ── 2 * └── 3 */ const leaves = await tree; console; /* [ { 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; console; /* [ { id: 1, name: 'Node1', ofset: 10 }, { id: 5, name: 'Node5', ofset: 0 } ] */ await tree; const view = await tree; console; /* [ { 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;};
DSL Interface
{ thisclient = user: processenvPGUSER host: processenvPGHOST database: processenvPGDB password: processenvPGPASS port: processenvPGPORT ; thistable = table; }
/** * Get the postgres Pool client * * @returns the pg Pool object * @memberof PostgresTree */;
/** * Build (CREATE) the table in the database * * @returns the PostgresTree class * @memberof PostgresTree */;
/** * Destroy (DELETE) the table in the database * * @returns * @memberof PostgresTree */;
/** * Given a the id of a node, get all its descendants * * @param * @returns {Array[Node]} the array of Node objects * @memberof PostgresTree */async
/** * Given a the id of a node, get all its ancestors * * @param * @returns {Array[Node]} the array of Node objects * @memberof PostgresTree */async
/** * Add a Node to the tree given its id, parentId, name and offset* * * @param * @returns * @memberof PostgresTree */async
/** * Move a node and its descendants to a newParentId * * @param * @param * @returns * @memberof PostgresTree */async
/** * Move a nodeId's descendants to a newParentId * * @param * @param * @returns * @memberof PostgresTree */async
/** * Remove a node and transfer its children to its parent * * @param * @returns * @memberof PostgresTree */async
/** * Remove a node and its descendants * * @param * @returns * @memberof PostgresTree */async
/** * Insert a node between node X and Y * * @param * @param * @param * @returns * @memberof PostgresTree */ async
/** * Insert a node and inherit its parents children * * @param * @returns * @memberof PostgresTree */ async
/** * Get all leaves of the tree * * @returns {Array[Node]} the array of Node objects * @memberof PostgresTree */ async
/** * Get all roots of the tree * * @returns {Array[Node]} the array of Node objects * @memberof PostgresTree */ async
/** * Create an ancestral view of the tree * * @returns {Array[{id, [NodeId], depth}]} * @memberof PostgresTree */ async
/** * Get the ancestral view of the tree * * @returns {Array[{id, [NodeId], depth}]} * @memberof PostgresTree */ async
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.