Prisma Next extension pack

prisma-ltree

PostgreSQL's ltree hierarchical-tree type for Prisma Next. Model category trees, org charts, taxonomies, and filesystem-like paths — then query them with type-safe, prisma-native operators. Ancestor/descendant checks, lquery/ltxtquery matching, path manipulation, and lowest-common-ancestor — without dropping to raw SQL.

Get startedView on GitHub
bash
pnpm add prisma-ltree

Setup

Three steps to a typed tree

The pack ships a baseline migration that runs CREATE EXTENSION IF NOT EXISTS ltree — applied automatically by prisma-next db init / db update. Requires Node >=24 and @prisma-next/*@0.14.0.

1. Register the pack

Add prisma-ltree/control to your config's extension packs.

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 an ltree column

Use the ltree() helper (or ltreeArray()) in your contract.

ts
// Add an ltree column to a model
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" }),
  },
});

Operations

Everything ltree, type-safe

Boolean operators return pg/bool@1; the rest return the codec shown. Each operator is verified against real Postgres via PGlite integration tests.

Hierarchy checks

Ancestor / descendant containment between two paths. Both lower to native ltree operators and return a boolean.

path.isAncestorOf(rhs)ltree @> ltree
path.isDescendantOf(rhs)ltree <@ ltree
ts
// Every category under "Top.Science"
const plan = sql
  .from(tables.category)
  .select({ id: tables.category.columns.id })
  .where(
    tables.category.columns.path.isDescendantOf(param("prefix")),
  )
  .build({ params: { prefix: "Top.Science" } });

Pattern matching

Match a path against an lquery, an array of lqueries, or a full-text ltxtquery. Patterns are validated string params.

path.matchesLquery(pattern)ltree ~ lquery
path.matchesLqueryArray(patterns)ltree ? lquery[]
path.matchesLtxtquery(query)ltree @ ltxtquery
ts
// Paths like "Top.*.Astronomy" at any depth
sql
  .from(tables.category)
  .where(
    tables.category.columns.path.matchesLquery(
      param("pattern"),
    ),
  )
  .build({ params: { pattern: "Top.*.Astronomy" } });

Scalar functions

Derive depth, slice subpaths, find a label's index, or compute the lowest common ancestor of two or more paths.

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, ...)
ts
// Project the depth of each path
sql
  .from(tables.category)
  .select({
    id: tables.category.columns.id,
    depth: tables.category.columns.path.nlevel(),
  })
  .build({ params: {} });

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)
ts
// Append a child label to an existing path
sql
  .from(tables.category)
  .select({
    child: tables.category.columns.path.concatText(
      param("label"),
    ),
  })
  .build({ params: { label: "Astronomy" } });

Array first-match

First-match operators over an ltree[] column (pg/ltree-array@1 codec, ADR-003). Each returns the first matching path.

paths.firstAncestorOf(rhs)ltree[] ?@> ltree
paths.firstDescendantOf(rhs)ltree[] ?<@ ltree
paths.firstMatchLquery(pattern)ltree[] ?~ lquery
paths.firstMatchLtxtquery(query)ltree[] ?@ ltxtquery
ts
// First stored path that is a descendant of the arg
sql
  .from(tables.node)
  .select({
    match: tables.node.columns.paths.firstDescendantOf(
      param("prefix"),
    ),
  })
  .build({ params: { prefix: "Top.Science" } });