#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 main target is PostgreSQL, but SQLite are also supported.

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

1import { t } 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 t type system combined with db helpers for database-specific column types.

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

The name field sets the database table name. The schema field defines columns using TypeBox 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:

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

#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. Instead, use the with option in repository query methods to perform 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,asc" }, 3  { where: { price: { gt: 0 } } }, 4  { count: true }, 5); 6  7// page.data        -> T[] 8// page.page.size   -> number 9// page.page.totalElements -> number (when count: true)10// page.page.totalPages    -> number (when count: true)

#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);

#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  [{ name: "A", price: 1 }, { name: "B", price: 2 }],3  { batchSize: 500 },4);

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

#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.findById("some-uuid");2entity.name = "Updated Name";3await this.repo.save(entity);

If the version has changed since the entity was fetched, save throws DbVersionMismatchError.

#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.findById("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{ status: "active" }3 4// Explicit operator5{ status: { eq: "active" } }

#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}

You can also 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.

For transactions with built-in retry on version conflicts, use the $transaction primitive:

 1import { $transaction } from "alepha/orm"; 2  3class OrderService { 4  processOrder = $transaction({ 5    handler: async (tx, orderId: string) => { 6      const order = await this.orders.getById(orderId, { tx }); 7      await this.orders.updateById(orderId, { status: "processed" }, { tx }); 8      return order; 9    },10  });11}

$transaction automatically retries when a DbVersionMismatchError occurs.

#Repository.of

For inline repository creation without a separate entity variable:

1class App {2  users = $inject(Repository.of(userEntity));3}

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

#Events

Repository operations emit lifecycle events:

#Error Types