Drizzle and PostgreSQL Guide

Package Boundary

Database code should live in:

packages/db

Recommended structure:

packages/db/src/
├── client.ts
├── index.ts
├── schema/
│   ├── entity-types.db.ts
│   ├── entities.db.ts
│   ├── relationships.db.ts
│   ├── properties.db.ts
│   ├── tags.db.ts
│   ├── groups.db.ts
│   ├── templates.db.ts
│   └── audit.db.ts
└── relations/
    └── index.ts

ltree Type In Drizzle

Drizzle may not have a built-in ltree type depending on your version and setup. Use a custom type.

Example:

import { customType } from "drizzle-orm/pg-core";

export const ltree = customType<{ data: string; driverData: string }>({
  dataType() {
    return "ltree";
  },
});

Then:

path: ltree("path").notNull(),

Entities Table Example

import {
  pgTable,
  uuid,
  text,
  timestamp,
  index,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import { ltree } from "../types/ltree";
import { entityTypeDefinitions } from "./entity-types.db";

export const entities = pgTable(
  "entities",
  {
    id: uuid("id").primaryKey().defaultRandom(),

    entityTypeId: uuid("entity_type_id")
      .notNull()
      .references(() => entityTypeDefinitions.id, { onDelete: "restrict" }),

    parentEntityId: uuid("parent_entity_id"),

    path: ltree("path").notNull(),

    pathLabel: text("path_label").notNull().unique(),

    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),

    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow(),

    deletedAt: timestamp("deleted_at", { withTimezone: true }),
  },
  (table) => ({
    parentIdx: index("idx_entities_parent").on(table.parentEntityId),
    pathGistIdx: index("idx_entities_path_gist")
      .using("gist", table.path),
    activeTypeIdx: index("idx_entities_active_type")
      .on(table.entityTypeId)
      .where(sql`${table.deletedAt} IS NULL`),
  }),
);

Self-referencing FK may need to be added carefully depending on Drizzle constraints. If awkward, add it through a migration.

ALTER TABLE entities
ADD CONSTRAINT fk_entities_parent
FOREIGN KEY (parent_entity_id)
REFERENCES entities(id)
ON DELETE RESTRICT;

Raw SQL For ltree Operators

Drizzle may require raw SQL for ltree operators.

Descendants:

const rows = await db.execute(sql`
  SELECT descendant.*
  FROM entities current
  JOIN entities descendant ON descendant.path <@ current.path
  WHERE current.id = ${entityId}
    AND current.deleted_at IS NULL
    AND descendant.deleted_at IS NULL
  ORDER BY descendant.path
`);

Ancestors:

const rows = await db.execute(sql`
  SELECT ancestor.*
  FROM entities current
  JOIN entities ancestor ON ancestor.path @> current.path
  WHERE current.id = ${entityId}
    AND current.deleted_at IS NULL
    AND ancestor.deleted_at IS NULL
  ORDER BY nlevel(ancestor.path) ASC
`);

Path Concatenation

Creating child path:

await tx.execute(sql`
  INSERT INTO entities (
    id,
    entity_type_id,
    parent_entity_id,
    path_label,
    path
  )
  SELECT
    ${id},
    ${entityTypeId},
    parent.id,
    ${pathLabel},
    parent.path || ${pathLabel}::ltree
  FROM entities parent
  WHERE parent.id = ${parentEntityId}
    AND parent.deleted_at IS NULL
`);

Root path:

await tx.insert(entities).values({
  id,
  entityTypeId,
  parentEntityId: null,
  pathLabel,
  path: pathLabel,
});

Generating Path Labels

Recommended approach:

export function createPathLabel(id: string) {
  return `e_${id.replaceAll("-", "_")}`;
}

Alternative:

export function createCompactPathLabel() {
  return `e_${ulid().toLowerCase()}`;
}

Rules:

• do not use display names,
• do not mutate labels,
• keep labels ltree-safe,
• keep labels compact.

Soft Delete Query Pattern

Drizzle queries should filter soft-deleted rows by default.

Example:

await db.query.entities.findMany({
  where: and(
    eq(entities.parentEntityId, parentId),
    isNull(entities.deletedAt),
  ),
});

For raw SQL:

WHERE deleted_at IS NULL

Transactions

Use transactions for:

• create entity with properties/tags,
• move entity,
• soft delete subtree,
• restore subtree,
• add relationship with audit,
• template application.

Example:

await db.transaction(async (tx) => {
  await assertEntityInUserScope(tx, input);
  const entity = await insertEntity(tx, input);
  await writeAuditEvent(tx, entity.id, "entity.created");
  return entity;
});

Avoid Over-Abstraction

Do not hide Drizzle behind generic repository interfaces.

Prefer:

use-case functions + Drizzle query helpers

This keeps:

• autocomplete,
• inferred return types,
• SQL composition,
• explicit transactions,
• relation loading.

Migrations

Migrations should be explicit and reviewed.

Important migrations:

001_enable_extensions.sql
002_entity_type_definitions.sql
003_entities.sql
004_relationships.sql
005_properties.sql
006_tags.sql
007_groups.sql
008_templates.sql
009_authorization_scopes.sql
010_audit_events.sql

Seed Data

Seed system entity types:

customer
site
building
room
asset
credential
tag
group
connection_method
vendor

Seed baseline property groups:

Device
Network
BACnet
Commissioning
Credentials

Seed baseline tag groups:

Criticality
Commissioning Status
Vendor
Environment

Testing Database Queries

Test ltree behavior against real PostgreSQL, not SQLite mocks.

Use integration tests for:

• child creation,
• path generation,
• descendant query,
• ancestor query,
• reparenting,
• cycle prevention,
• root-scope authorization,
• soft-delete subtree.