#Repository

Alepha ORM is built on top of Drizzle ORM and Drizzle Kit.

$entity defines a database table. $repository creates a type-safe data access layer for that table.

Alepha's main target is PostgreSQL, but SQLite is also supported.

The API is mostly database-agnostic, but some features (e.g. certain column types or operators) may be database-specific.

1import { z } from "alepha";2import { $entity, $repository, db } from "alepha/orm";

#Defining an Entity

An entity maps directly to a database table. The schema uses Alepha's Zod schema layer (z) combined with db helpers for database-specific column types.

 1import { z } from "alepha"; 2import { $entity, db } from "alepha/orm"; 3  4const product = $entity({ 5  name: "products", 6  schema: z.object({ 7    id: db.primaryKey(z.uuid()), 8    sku: z.text(), 9    name: z.text(),10    price: z.number(),11    createdAt: db.createdAt(),12    updatedAt: db.updatedAt(),13  }),14  indexes: [{ column: "name", unique: true }],15});

The name field sets the database table name. The schema field defines columns using Zod schemas. The indexes field configures database indexes for query optimization.

#Index Options

Indexes accept several forms:

1indexes: [2  "name",                                    // simple index on one column3  { column: "email", unique: true },         // unique index on one column4  { columns: ["tenantId", "name"], unique: true }, // composite unique index5  { column: "status", name: "idx_status" },  // index with custom name6],

#Constraints

Entities support unique constraints and check constraints at the table level:

 1import { $entity, db, sql } from "alepha/orm"; 2  3const user = $entity({ 4  name: "users", 5  schema: z.object({ 6    id: db.primaryKey(z.uuid()), 7    tenantId: z.uuid(), 8    username: z.text(), 9    age: z.integer(),10  }),11  constraints: [12    { columns: ["tenantId", "username"], unique: true },13    { columns: ["age"], check: sql`age >= 0 AND age <= 150` },14  ],15});

#Foreign Keys

Explicit foreign key constraints can be declared at the entity level:

1foreignKeys: [2  {3    columns: ["authorId"],4    foreignColumns: [() => user.cols.id],5  },6],

For single-column foreign keys, prefer db.ref() on the column itself (see Special Columns).

#Creating a Repository

Use $repository as a class property to get a fully typed repository:

1class ProductService {2  repo = $repository(product);3}

Relations between tables are NOT handled by $entity. Declare them separately with $relations and read them with include - see Relations. For a one-off SQL join written per query, the with option is still there; see Joins.

#Query Methods

#findMany

Find multiple records. Supports where, limit, offset, orderBy, groupBy, distinct, columns, and with (joins).

1const items = await this.repo.findMany({2  where: { price: { gte: 10 } },3  orderBy: { column: "name", direction: "asc" },4  limit: 20,5  offset: 0,6});

#findOne

Find a single record. Returns undefined if not found.

1const item = await this.repo.findOne({2  where: { name: { eq: "Widget" } },3});

#getOne

Find a single record. Throws DbEntityNotFoundError if not found.

1const item = await this.repo.getOne({2  where: { name: { eq: "Widget" } },3});

#findById / getById

Look up a record by primary key. findById returns undefined if not found, getById throws DbEntityNotFoundError.

1const item = await this.repo.findById("some-uuid");2const item = await this.repo.getById("some-uuid"); // throws if missing

#paginate

Returns paginated results with metadata.

 1const page = await this.repo.paginate( 2  { page: 0, size: 10, sort: "name" }, 3  { where: { price: { gt: 0 } } }, 4  { count: true }, 5); 6  7// page.content     -> T[] 8// page.page.size   -> number 9// page.page.totalElements -> number (when count: true)10// page.page.totalPages    -> number (when count: true)

The sort string is a comma-separated column list; prefix a column with - for descending order: "name", "-createdAt", "role,-name".

#count

Count matching records.

1const total = await this.repo.count({ status: { eq: "active" } });

#query

Execute raw SQL using Drizzle's sql tagged template. Returns decoded entities.

1import { sql } from "alepha/orm";2 3const results = await this.repo.query(4  (table) => sql`SELECT * FROM ${table} WHERE ${table.price} > ${100}`,5);

#aggregate

Grouped aggregations (sum, avg, min, max, count) without writing raw SQL - see Joins for the aggregation pipeline it powers.

A select key is normally a column, and true selects it as-is (which is what a groupBy key wants):

1await this.repo.aggregate({2  select: { category: true, amount: { sum: true, avg: true } },3  groupBy: ["category"],4});5// -> [{ category: "books", amount: { sum: 500, avg: 25 } }]

Conditional buckets. An operation can instead take { column, where }, which compiles to COUNT(CASE WHEN <where> THEN <column> END). Supplying column turns the key into an alias, so several differently-conditioned aggregates over the same column can sit side by side in one pass:

 1await this.repo.aggregate({ 2  select: { 3    epicId: true, 4    id: { count: true }, 5    completed: { 6      count: { column: "id", where: { completedAt: { isNotNull: true } } }, 7    }, 8    open: { 9      count: {10        column: "id",11        where: {12          acceptedAt: { isNotNull: true },13          completedAt: { isNull: true },14        },15      },16    },17  },18  where: { epicId: { inArray: epicIds } },19  groupBy: ["epicId"],20});

Four things to know about that form:

• The per-aggregate where narrows, never widens. It is ANDed inside the CASE, while the query's own where and the soft-delete filter still decide which rows the aggregate sees at all.
• A key is either a column name or an alias: a column key may not carry column, an alias key must carry it on every operation, and an alias may not be spelled like one of the entity's columns. Anything else is refused with the key named, because a misspelt column and an intended alias are indistinguishable.
• COUNT(CASE WHEN c THEN col END) skips NULLs of col as well as rows failing c. For "how many rows match", point column at a column that is never null, such as the primary key. Pointing it at a nullable one asks a different (also useful) question.
• A bucket that matches nothing is 0 for count / sum / avg, and null for min / max - the same empty-set answers as an unconditioned aggregate.

#Create Methods

#create

Create a single entity. Returns the full created entity.

1const created = await this.repo.create({2  name: "Widget",3  price: 9.99,4});

#createMany

Batch-create entities. Inserts are batched in chunks of 1000 by default.

1const items = await this.repo.createMany(2  [3    { name: "A", price: 1 },4    { name: "B", price: 2 },5  ],6  { batchSize: 500 },7);

#upsert

Insert a new entity or update an existing one if a conflict is detected. Works on both PostgreSQL and SQLite.

 1// Simple upsert on primary key 2const product = await this.repo.upsert({ 3  id: "some-uuid", 4  name: "Widget", 5  price: 9.99, 6}); 7  8// Upsert on a unique column 9const product = await this.repo.upsert(10  { id: "some-uuid", sku: "WIDGET-1", name: "Widget", price: 9.99 },11  { target: ["sku"] },12);13 14// Upsert with custom update fields (only update price on conflict)15const product = await this.repo.upsert(16  { id: "some-uuid", sku: "WIDGET-1", name: "Widget", price: 19.99 },17  { target: ["sku"], set: { price: 19.99 } },18);

• target: column(s) to detect conflicts on. Defaults to the primary key.
• set: fields to update on conflict. Defaults to the insert data minus the target and primary key columns.

If the entity has an updatedAt column, it is automatically set on conflict.

#upsertMany

Batch upsert. Same options as upsert, with two batch-only rules: every row must resolve to the same conflict target, and a counter-style set must read from excluded (the incoming row) rather than the table, or every row after the first sees stale values.

It sends one statement when the rows fit, and otherwise splits them the way createMany does, so no statement binds more values than the driver accepts (100 on Cloudflare D1). Like createMany's, the batches are not one atomic unit unless the call runs inside $transactional.

#Update Methods

#updateOne

Find a single entity by where clause and update it. Throws DbEntityNotFoundError if not found. Returns the updated entity.

1const updated = await this.repo.updateOne(2  { name: { eq: "Widget" } },3  { price: 12.99 },4);

#updateById

Update by primary key. Returns the updated entity.

1const updated = await this.repo.updateById("some-uuid", { price: 12.99 });

#updateMany

Update multiple records matching a where clause. Returns an array of updated entity IDs.

1const ids = await this.repo.updateMany(2  { status: { eq: "draft" } },3  { status: "published" },4);

#save

Save a previously fetched entity. Uses optimistic locking when a version column is present. Unlike updateOne/updateById, save expects the full entity object and sets undefined fields to null.

1const entity = await this.repo.getById("some-uuid"); // getById throws if missing2entity.name = "Updated Name";3await this.repo.save(entity);

If the row was written since the entity was fetched (by save or any other update, all of which bump the version), save throws DbVersionMismatchError, a 409 over HTTP, and leaves the entity as it was loaded.

#Delete Methods

#deleteOne

Delete a single entity matching the where clause. Returns an array of deleted IDs.

1await this.repo.deleteOne({ name: { eq: "Widget" } });

#deleteById

Delete by primary key. Throws DbEntityNotFoundError if not found.

1await this.repo.deleteById("some-uuid");

#deleteMany

Delete multiple records matching a where clause. Returns an array of deleted IDs.

1const ids = await this.repo.deleteMany({ status: { eq: "archived" } });

#destroy

Delete a previously fetched entity by its primary key.

1const entity = await this.repo.getById("some-uuid");2await this.repo.destroy(entity);

#clear

Delete all records in the table.

1await this.repo.clear();

#Soft Delete

If the entity schema includes a db.deletedAt() column, all delete operations automatically perform a soft delete by setting the deletedAt timestamp instead of removing the row. All query operations automatically filter out soft-deleted records.

To perform a hard delete on a soft-deletable entity, pass { force: true }:

1await this.repo.deleteById("some-uuid", { force: true });

To include soft-deleted records in queries, also use { force: true }:

1const all = await this.repo.findMany({}, { force: true });

#Where Clause Operators

Where clauses accept either a direct value (shorthand for eq) or an object with filter operators:

 1// Direct value (shorthand for eq) 2{ 3  status: "active"; 4} 5  6// Explicit operator 7{ 8  status: { 9    eq: "active";10  }11}

Never pass undefined into a where-filter. where: { col: undefined } throws AlephaError - it used to be dropped silently, which produced a query with no WHERE clause at all. For optional filters, omit the key entirely:

1const where: Record<string, unknown> = {};2if (status) where.status = status;

#Comparison Operators

#Array Operators

#Null Operators

#Range Operators

#String Operators

#PostgreSQL Array Operators

#Logical Operators

Combine conditions with and, or, or negate with not:

1{2  and: [3    { status: { eq: "active" } },4    { or: [5      { role: { eq: "admin" } },6      { role: { eq: "moderator" } },7    ]},8  ],9}

Where clauses also support exists / notExists subquery conditions at the top level, and you can pass a raw Drizzle SQLWrapper as the where clause for full SQL control.

#Transactions

Use the transaction method to execute multiple operations atomically:

1await this.repo.transaction(async (tx) => {2  const user = await this.users.create({ name: "Alice" }, { tx });3  await this.orders.create({ userId: user.id, total: 50 }, { tx });4});

All repository methods accept { tx } in their options parameter to participate in the transaction. Beyond tx, that options parameter also takes force (include soft-deleted rows), for (row locks, e.g. { for: "update" }), now (override the timestamp used for updatedAt), and cache (per-statement cache control).

On drivers without interactive transaction support - Cloudflare D1 - transaction() throws and tells you to use $transactional() instead.

To wrap a whole handler in a transaction without drilling { tx } through every call, use the $transactional middleware:

 1import { $action } from "alepha/server"; 2import { $transactional } from "alepha/orm"; 3  4class OrderService { 5  processOrder = $action({ 6    use: [$transactional()], 7    handler: async ({ body }) => { 8      await this.orders.create(body);       // auto-uses the transaction 9      await this.audit.create({ ... });     // auto-uses the transaction10      // throw → rollback, return → commit11    },12  });13}

Every repository operation inside the handler automatically participates in the transaction. Nesting is safe - a nested $transactional reuses the outer transaction.

#No transactions on D1

Cloudflare D1 (and PGlite) has no transactions, and $transactional() is a no-op there. The handler runs in place, a throw rolls back nothing, and two requests interleave freely. Each $transactional() primitive logs one warning the first time it runs on such a driver. Code that must be correct on D1 does not rely on a rollback:

• Lost updates: db.version() with save(), which answers 409 when the row changed underneath.
• Check-then-act: put the precondition in the write's WHERE (updateOne({ id, status: "ready" }, …)), and treat a miss as "someone else won".
• Several writes: validate before the first one, and order them so that a failure halfway leaves harmless state, or compensate by hand.
• Unique names: claim the name first; a UNIQUE violation is an ordinary error without a transaction.

Specs can run the same way on SQLite with DATABASE_TRANSACTIONS: false (see the testing guide).

Concurrency is safe too: each transactional() call runs in its own context, so two blocks started at the same time - Promise.all, two requests, a job racing a handler - never read or write through each other's transaction.

#After the commit

Side effects that must only happen once the data is durable - emitting a domain event, sending an email - do not belong inside the transaction: subscribers would read uncommitted rows and every lock the transaction holds stays held while they run. Register them with DatabaseProvider.afterCommit() instead:

 1import { $inject } from "alepha"; 2import { DatabaseProvider } from "alepha/orm"; 3  4class OrderService { 5  protected readonly db = $inject(DatabaseProvider); 6  7  async markPaid(id: string) { 8    return this.db.transactional(async () => { 9      const order = await this.orders.updateById(id, { status: "paid" });10 11      await this.db.afterCommit(() =>12        this.alepha.events.emit("commerce:order:paid", { orderId: order.id }),13      );14 15      return order;16    });17  }18}

Because nested transactional() blocks join the outermost transaction, the callback waits for the outermost commit - even when the method is called from inside someone else's transaction. Callbacks run in registration order and are discarded if the transaction rolls back. Outside any transaction, afterCommit runs its callback immediately.

#Repository.of

For inline repository creation without a separate entity variable:

1import { $inject } from "alepha";2import { Repository } from "alepha/orm";3 4class App {5  users = $inject(Repository.of(user)); // user: the $entity from above6}

This creates a Repository subclass bound to the given entity, suitable for use with $inject.

#Events

Repository operations emit lifecycle events:

#Error Types