Prisma Next extension pack

prisma-ltree

PostgreSQL's ltree hierarchical-tree type for Prisma Next. Model category trees, org charts, and taxonomies, then query them with typed operators. No raw SQL.

Get startedView on GitHub
pnpm add prisma-ltree

See 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.

Quickstart

Three steps to your first query

Requires Node >=24 and @prisma-next/*@0.14.0. Full setup in the Getting Started guide.

1. Register the pack

Add prisma-ltree/control to your config.

ts
// prisma-next.config.ts
import { defineConfig } from "@prisma-next/cli/config-types";
import postgresAdapter from "@prisma-next/adapter-postgres/control";
import sql from "@prisma-next/family-sql/control";
import postgres from "@prisma-next/target-postgres/control";
import ltree from "prisma-ltree/control";

export default defineConfig({
  family: sql,
  target: postgres,
  adapter: postgresAdapter,
  extensionPacks: [ltree],
});

2. Declare ltree columns

Use ltree() in TypeScript or ltree.Ltree() in PSL. See both lanes.

ts
// TypeScript lane — PSL uses ltree.Ltree() in contract.prisma
import { int4Column, textColumn } from "@prisma-next/adapter-postgres/column-types";
import { defineContract, field, model } from "@prisma-next/sql-contract-ts/contract-builder";
import { ltree } from "prisma-ltree/column-types";
import ltreePack from "prisma-ltree/pack";

export const contract = defineContract({
  family: sqlFamily,
  target: postgres,
  extensionPacks: { ltree: ltreePack },
  models: {
    Category: model("Category", {
      fields: {
        id: field.column(int4Column).id(),
        name: field.column(textColumn),
        path: field.column(ltree()),
      },
    }).sql({ table: "category" }),
  },
});

3. Query the tree

Operators attach to ltree column references in the query builder.

ts
// Find every category under "electronics"
import { param } from "@prisma-next/sql-query/param";
import { db } from "./prisma/db";

const category = db.schema.tables.category;

const plan = db.sql
  .from(category)
  .select({ id: category.columns.id, path: category.columns.path })
  .where(category.columns.path.isDescendantOf(param("prefix")))
  .build({ params: { prefix: "electronics" } });

const rows = await db.runtime().execute(plan);

Operations

Every ltree operator, type-safe

Each method maps to a native PostgreSQL operator or function. Full reference in the docs.

Hierarchy checks

Filter by branch or subtree with ancestor and descendant checks.

path.isAncestorOf(rhs)ltree @> ltree
path.isDescendantOf(rhs)ltree <@ ltree

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