Hierarchy checks
Filter by branch or subtree with ancestor and descendant checks.
| path.isAncestorOf(rhs) | ltree @> ltree |
| path.isDescendantOf(rhs) | ltree <@ ltree |
Prisma 8 extension pack
prisma-ltree
PostgreSQL's ltree hierarchical-tree type for Prisma 8. Model category trees, org charts, and taxonomies, then query them with typed operators. No raw SQL.
pnpm add prisma-ltreeSee it work
A query is a shape over the tree
Each operator selects a different slice of the tree. Pick one below to see which nodes match, the typed call, and the SQL it runs.
Operations
Filter a whole branch with one path.
typescript
// All categories in the Top.Science branch
sql.from(tables.category)
.where(tables.category.columns.path.isDescendantOf("Top.Science"));path.isDescendantOf("Top.Science")path <@ 'Top.Science'Descendants: path.isDescendantOf("Top.Science"). 4 of 8 nodes match.
Setup
Three steps to your first query
Requires Node >=24 and @prisma/orm-postgres@8.0.0-rc.8 (exact SPI pin). Install prisma-ltree with a caret. Full setup in the Get started guide.
1. Register the pack
Add prisma-ltree/control to your config.
typescript
// prisma.config.ts
import { definePrismaConfig } from "prisma/config";
import { defineConfig as ormConfig } from "@prisma/orm-postgres/config";
import ltree from "prisma-ltree/control";
export default definePrismaConfig({
orm: ormConfig({
contract: "./src/prisma/contract.ts",
extensions: [ltree],
db: {
connection: process.env.DATABASE_URL!,
},
}),
});2. Declare ltree columns
Use ltree() in TypeScript or ltree.Ltree() in PSL. See both lanes.
typescript
// TypeScript lane. PSL uses ltree.Ltree() in contract.prisma
import { defineContract } from "@prisma/orm-postgres/contract-builder";
import { ltree } from "prisma-ltree/column-types";
import ltreePack from "prisma-ltree/pack";
export const contract = defineContract(
{
extensions: { ltree: ltreePack },
},
({ field, model }) => ({
models: {
Category: model("Category", {
fields: {
id: field.id.int(),
name: field.string(),
path: field.column(ltree()),
},
}).sql({ table: "category" }),
},
}),
);3. Query the tree
Operators attach to ltree column references in the query builder.
typescript
// Find every category under "electronics"
import { db } from "./prisma/db";
const rows = await db.orm.Category.where((c) =>
c.path.isDescendantOf("electronics"),
)
.select("id", "path")
.all();Operations
Every ltree operator, type-safe
Each method maps to a native PostgreSQL operator or function. Full reference in the docs.
Pattern matching
Match paths with lquery wildcards, multiple lquery patterns, or full-text ltxtquery expressions.
| path.matchesLquery(pattern) | ltree ~ lquery |
| path.matchesLqueryArray(patterns) | ltree ? lquery[] |
| path.matchesLtxtquery(query) | ltree @ ltxtquery |
Scalar functions
Derive depth, slice subpaths, find a label's index, or compute the lowest common ancestor.
| path.nlevel() | nlevel(ltree) |
| path.subltree(start, end) | subltree(ltree, start, end) |
| path.subpath(offset, len?) | subpath(ltree, offset, len) |
| path.indexOf(other, off?) | index(ltree, ltree, off) |
| path.lca(other, ...rest) | lca(ltree, ltree, ...) |
Concatenation & conversion
Build new paths by concatenating ltrees or text labels, and convert between ltree and text in either direction.
| path.concat(rhs) | ltree || ltree |
| path.concatText(label) | ltree || text |
| path.prependText(label) | text || ltree |
| path.toText() | ltree2text(ltree) |
| text.toLtree() | text2ltree(text) |
Array first-match
Store multiple paths in an ltree[] column, then pick the first match for containment or patterns.
| paths.firstAncestorOf(rhs) | ltree[] ?@> ltree |
| paths.firstDescendantOf(rhs) | ltree[] ?<@ ltree |
| paths.firstMatchLquery(pattern) | ltree[] ?~ lquery |
| paths.firstMatchLtxtquery(query) | ltree[] ?@ ltxtquery |