Drizzle ORM

Storage

Source: packages/laikacms/src/impl/storage-drizzle

A StorageRepository implementation backed by any SQL database via Drizzle ORM. The repository is database-agnostic: you supply query-builder callbacks and it handles the Laika storage contract. Tested with SQLite (via better-sqlite3 and Cloudflare D1), PostgreSQL, and MySQL.

Usage

import { and, count, eq, like, lte } from 'drizzle-orm';
import { DrizzleStorageRepository } from 'laikacms/storage/drizzle';

import { db, storageTable } from './db'; // your Drizzle db instance and table

const repo = new DrizzleStorageRepository({
  queryBuilders: {
    keyEquals: value => eq(storageTable.key, value),
    keyStartsWith: prefix => like(storageTable.key, `${prefix}%`),
    depthLte: value => lte(storageTable.depth, value),
    and: (...conditions) => and(...conditions),
  },
  callbacks: {
    insert: ({ values }) => db.insert(storageTable).values(values).returning(),
    update: ({ where, values }) => db.update(storageTable).set(values).where(where).returning(),
    delete: ({ where }) => db.delete(storageTable).where(where).returning(),
    select: ({ where, limit, offset }) =>
      db.select().from(storageTable).where(where).limit(limit ?? 100).offset(offset ?? 0),
    // optional — enables correct meta.total for paginated responses
    count: ({ where }) =>
      db.select({ count: count() }).from(storageTable).where(where).then(([r]) => r?.count ?? 0),
  },
});

const stream = repo.listAtoms('posts/', { depth: 1, pagination: { offset: 0, limit: 50 } });

Constructor

new DrizzleStorageRepository(options: DrizzleStorageRepositoryOptions)

DrizzleStorageRepositoryOptions

Field	Type	Description
queryBuilders	DrizzleStorageQueryBuilders	Functions that produce SQL conditions for your specific Drizzle dialect.
callbacks	DrizzleStorageCallbacks	Async functions that execute each class of SQL statement.
logger	Pick<Console, 'error' | 'warn' | 'info' | 'debug'>	Optional logger; defaults to no logging.

DrizzleStorageQueryBuilders

The repository never imports Drizzle directly; instead it asks you to provide condition factories. Return whatever your Drizzle version produces — the types are unknown at the boundary.

Field	Signature	Description
keyEquals	(value: string) => unknown	Exact match on the key column.
keyStartsWith	(prefix: string) => unknown	Prefix scan on the key column (used for folder listings and child-existence checks).
depthLte	(value: number) => unknown	Upper bound on the depth column (controls recursion depth for listAtoms).
and	(...conditions: unknown[]) => unknown	Combines multiple conditions with logical AND.

DrizzleStorageCallbacks

Field	Signature	Description
insert	({ values: StorageModel }) => Promise<StorageModel[]>	Insert a single row; return the inserted rows.
update	({ where: unknown, values: Partial<StorageModel> }) => Promise<StorageModel[]>	Update rows matching where; return affected rows.
delete	({ where: unknown }) => Promise<StorageModel[]>	Delete rows matching where; return deleted rows.
select	({ where: unknown, limit?: number, offset?: number }) => Promise<StorageModel[]>	Query rows; apply LIMIT/OFFSET when provided.
count	({ where: unknown }) => Promise<number> (optional)	Return the total row count matching where (no LIMIT/OFFSET). When provided, listAtoms / listAtomSummaries populate done.total with the full matching count instead of the page count, enabling correct meta.total in JSON:API paginated responses.

StorageModel

The shape of a database row as the repository reads and writes it:

type StorageModel = {
  key: string, // slash-separated path, e.g. "posts/hello"
  type: string, // object type tag, e.g. "keep-file"
  content: string, // JSON-serialized StorageObjectContent
  depth: number, // number of path segments (used for depth-limited listings)
  createdAt: string, // ISO 8601
  updatedAt: string, // ISO 8601
};

Pagination

Both offset/limit and page-based (page/perPage) pagination styles are supported and translated to SQL OFFSET/LIMIT by the repository. Cursor pagination is not supported.

Valid pagination shapes for listAtoms / listAtomSummaries:

{ offset: 0, limit: 50 }     // offset style
{ page: 1, perPage: 50 }     // page style

Behaviour notes

• Folders. The repository emulates folders by storing a .keep placeholder row (type: 'keep-file') when createFolder is called. getFolder returns a synthetic folder object if any child key exists under the given prefix.
• Depth. depth is stored as the number of slash-separated path segments. listAtoms filters rows with depth <= baseDepth + options.depth.
• Duplicate keys. createObject fails with EntryAlreadyExistsError if the key already exists. Use createOrUpdateObject for upsert semantics.
• Safe deletes. removeAtoms refuses to delete a key that is a folder prefix (i.e. has children) — the refusal is surfaced as a stream recoverableError, not a fatal error.
• Listings on missing folders. SQL cannot distinguish an empty folder from a missing one — both produce zero matching rows. listAtoms/listAtomSummaries return total: 0 with no data and no recoverableError for a missing folder key. This matches @laikacms/github behaviour but differs from GitLab, WebDAV, web, and web-fs, which surface a NotFoundError recoverableError so callers can detect stale or misspelled folder keys.
• Content serialization. StorageObjectContent is stored as a JSON string in the content column; invalid JSON on read surfaces as an InvalidData recoverableError.

Known limitations

• LCMS-179: Cursor pagination is advertised as unsupported in getCapabilities() and is not implemented. Pass offset/limit or page/perPage instead.

Documents

Source: packages/laikacms/src/impl/documents-drizzle

A DocumentsRepository implementation backed by any SQL database via Drizzle ORM. The repository is database-agnostic: you supply query-builder callbacks and it handles the full Laika documents contract — published records, unpublished drafts, and revision history.

Usage

import { and, count, eq, inArray, like, lte, ne } from 'drizzle-orm';
import { DrizzleDocumentsRepository } from 'laikacms/documents/drizzle';

import { db, documentsTable, revisionsTable } from './db';

const repo = new DrizzleDocumentsRepository({
  documentQueryBuilders: {
    keyEquals: v => eq(documentsTable.key, v),
    keyStartsWith: p => like(documentsTable.key, `${p}%`),
    statusEquals: v => eq(documentsTable.status, v),
    statusNotEquals: v => ne(documentsTable.status, v),
    statusIn: vs => inArray(documentsTable.status, vs),
    depthLte: v => lte(documentsTable.depth, v),
    and: (...conds) => and(...conds),
  },
  revisionQueryBuilders: {
    keyEquals: v => eq(revisionsTable.key, v),
    revisionEquals: v => eq(revisionsTable.revision, v),
    and: (...conds) => and(...conds),
  },
  callbacks: {
    documents: {
      insert: ({ values }) => db.insert(documentsTable).values(values).returning(),
      update: ({ where, values }) => db.update(documentsTable).set(values).where(where).returning(),
      delete: ({ where }) => db.delete(documentsTable).where(where).returning(),
      select: ({ where, limit, offset, excludeContent }) =>
        db
          .select(
            excludeContent
              ? {
                key: documentsTable.key,
                depth: documentsTable.depth,
                status: documentsTable.status,
                language: documentsTable.language,
                createdAt: documentsTable.createdAt,
                updatedAt: documentsTable.updatedAt,
              }
              : undefined,
          )
          .from(documentsTable)
          .where(where)
          .limit(limit ?? 100)
          .offset(offset ?? 0),
      // optional — enables correct meta.page.total for paginated responses
      count: ({ where }) =>
        db.select({ count: count() }).from(documentsTable).where(where).then(([r]) =>
          r?.count ?? 0
        ),
    },
    revisions: {
      insert: ({ values }) => db.insert(revisionsTable).values(values).returning(),
      update: ({ where, values }) => db.update(revisionsTable).set(values).where(where).returning(),
      delete: ({ where }) => db.delete(revisionsTable).where(where).returning(),
      select: ({ where, limit, offset = 0, excludeContent }) =>
        db
          .select(
            excludeContent
              ? {
                key: revisionsTable.key,
                depth: revisionsTable.depth,
                revision: revisionsTable.revision,
                language: revisionsTable.language,
                createdAt: revisionsTable.createdAt,
                updatedAt: revisionsTable.updatedAt,
              }
              : undefined,
          )
          .from(revisionsTable)
          .where(where)
          .limit(limit ?? 100)
          .offset(offset),
      count: ({ where }) =>
        db.select({ count: count() }).from(revisionsTable).where(where)
          .then(([r]) => r?.count ?? 0),
    },
  },
});

const { items } = await collectStream(
  repo.listRecords({
    folder: 'posts',
    type: 'published',
    depth: 1,
    pagination: { offset: 0, limit: 50 },
  }),
);

Constructor

new DrizzleDocumentsRepository(options: DrizzleDocumentsRepositoryOptions<...>)

The class is fully generic over your Drizzle condition types (CKE, CKSW, CSE, CSNE, CSI, CDLTE, CA, RKE, RE, RA). TypeScript infers these from the callbacks you pass, so in practice you never need to name the type parameters explicitly.

DrizzleDocumentsRepositoryOptions

Field	Type	Description
documentQueryBuilders	{ keyEquals, keyStartsWith, statusEquals, statusNotEquals, statusIn, depthLte, and }	Condition factories for the documents table.
revisionQueryBuilders	{ keyEquals, revisionEquals, and }	Condition factories for the revisions table.
callbacks	{ documents: DocumentCallbacks, revisions: RevisionCallbacks }	Async functions that execute each SQL statement.
logger	Pick<Console, 'error' | 'warn' | 'info' | 'debug'>	Optional logger; defaults to no logging.

documentQueryBuilders

Field	Signature	Description
keyEquals	(value: string) => CKE	Exact match on the key column.
keyStartsWith	(prefix: string) => CKSW	Prefix scan on the key column (folder listings).
statusEquals	(value: string) => CSE	Match a specific status value (e.g. "published").
statusNotEquals	(value: string) => CSNE	Exclude a specific status value (used to select drafts).
statusIn	(values: string[]) => CSI	Match any of several status values.
depthLte	(value: number) => CDLTE	Upper bound on the depth column.
and	(...conditions) => CA	Combines multiple conditions with logical AND.

revisionQueryBuilders

Field	Signature	Description
keyEquals	(value: string) => RKE	Exact match on the key column of the revisions table.
revisionEquals	(value: string) => RE	Match a specific revision identifier.
and	(...conditions) => RA	Combines revision conditions with logical AND.

callbacks.documents

Field	Signature	Description
insert	({ values: DocumentModelStrict }) => Promise<DocumentModel[]>	Insert a document row; return inserted rows.
update	({ where, values: Partial<DocumentModelStrict> }) => Promise<DocumentModel[]>	Update document rows matching where.
delete	({ where }) => Promise<DocumentModel[]>	Delete document rows matching where.
select	({ where, limit?, offset?, excludeContent? }) => Promise<DocumentModel[]>	Query documents; skip the content column when excludeContent is true (used for summary listings).
count	({ where }) => Promise<number> (optional)	Return total matching rows (no LIMIT/OFFSET). When provided, listRecords / listRecordSummaries populate done.total with the full matching count instead of the page count, enabling correct meta.page.total in the Documents JSON:API.

callbacks.revisions

Field	Signature	Description
insert	({ values: RevisionModelStrict }) => Promise<RevisionModel[]>	Insert a revision row.
update	({ where, values: Partial<RevisionModelStrict> }) => Promise<RevisionModel[]>	Update revision rows matching where.
delete	({ where }) => Promise<RevisionModel[]>	Delete revision rows matching where.
select	({ where, limit?, offset?, excludeContent? }) => Promise<RevisionModel[]>	Query revisions (DB should apply LIMIT/OFFSET).
count	({ where }) => Promise<number> (optional)	Return total matching rows (no LIMIT). When provided, listRevisions uses this for done.total instead of the page count.

Model types

DocumentModel — shape of a documents table row:

type DocumentModel = {
  key: string, // slash-separated path
  depth: number, // number of path segments
  status: string | null | undefined, // e.g. "published", "draft"
  language: string | null | undefined, // BCP 47 language tag
  content: string, // JSON-serialized StorageObjectContent
  createdAt: string, // ISO 8601
  updatedAt: string, // ISO 8601
};

RevisionModel — shape of a revisions table row:

type RevisionModel = {
  key: string,
  depth: number,
  revision: string, // revision identifier (e.g. a git SHA or UUID)
  language: string | null | undefined,
  content: string, // JSON-serialized StorageObjectContent
  createdAt: string,
  updatedAt: string,
};

DocumentModelStrict and RevisionModelStrict are the same shapes with status, language, and revision required (non-nullable) — used for inserts and strict updates.

Pagination

Both offset/limit and page-based (page/perPage) pagination styles are supported for listRecords, listRecordSummaries, and listRevisions. Cursor pagination is not supported.

Valid pagination shapes:

{ offset: 0, limit: 50 }     // offset style
{ page: 1, perPage: 50 }     // page style

Behaviour notes

• Published vs unpublished. Published records have status = "published". All other status values (e.g. "draft") are treated as unpublished. publish() and unpublish() update the status column in-place; there is no separate table for drafts.
• Revisions. Revisions are stored in a separate table keyed by (key, revision). The revision identifier is caller-supplied (any opaque string such as a UUID or git SHA).
• Depth. depth mirrors the number of slash-separated path segments and is stored at write time to enable efficient depth-limited queries without regex filtering.
• Content serialization. StorageObjectContent is stored as a JSON string; invalid JSON on read surfaces as an InvalidData error.
• Summary listings. listRecordSummaries and listRevisions both pass excludeContent: true to their respective select callbacks so the content column can be omitted from the SQL projection, reducing data transfer.