Getting started

From an empty directory to a first query in about five minutes. You need Node.js 22.12 or later, TypeScript 5.8 or later, and a Postgres server (14 or later).

Create the project

mkdir egon-start && cd egon-start
npm init -y
npm pkg set type=module
npm install egon-orm @egon-orm/postgres pg
npm install -D typescript tsx @types/node @types/pg

Egon ORM is published as ES modules, hence "type": "module". (A CommonJS project can still load it with require() on Node.js 22.12 or later.) For SQLite, install @egon-orm/sqlite with better-sqlite3; for MySQL, @egon-orm/mysql with mysql2. See Adapters.

Add a tsconfig.json:

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "NodeNext",
    "moduleResolution": "NodeNext",
    "strict": true,
    "noEmit": true
  }
}

tsx runs the TypeScript files, and npx tsc checks their types. @types/pg is there for tsc: the types of @egon-orm/postgres refer to pg's, which pg does not ship. (It is an optional peer dependency: with skipLibCheck: true you can leave it out.) A library that publishes its schema classes can emit declarations, with declaration: true in place of noEmit.

Start Postgres

If you have no Postgres at hand, Docker starts one:

docker run -d --name egon-pg -e POSTGRES_PASSWORD=egon -e POSTGRES_DB=egon_start -p 5432:5432 postgres:16
export DATABASE_URL=postgres://postgres:egon@localhost:5432/egon_start

POSTGRES_DB creates a database of its own for this guide, egon_start, so its tables do not land in the server's default postgres database. Any other Postgres 14 or later works too (a local install, or a hosted one): create an empty database there and point DATABASE_URL at it.

Define two schemas

A schema is a class that describes a table. Its instances are the rows you read. Create schemas.ts:

// schemas.ts
import { belongsTo, cast, hasMany, Schema, t, timestamps } from "egon-orm";

export class User extends Schema("users", {
  id: t.bigint({ mode: "number" }).primaryKey().dbGenerated(),
  email: t.string(),
  name: t.string().nullable(),
  ...timestamps(),
}) {
  static posts = hasMany(() => Post, { foreignKey: "authorId" });

  static changeset(user: User | typeof User, params: unknown) {
    return cast(user, params, ["email", "name"])
      .validateRequired(["email"])
      .validateFormat("email", /^[^@\s]+@[^@\s]+$/)
      .uniqueConstraint("email");
  }
}

export class Post extends Schema("posts", {
  id: t.bigint({ mode: "number" }).primaryKey().dbGenerated(),
  authorId: t.bigint({ mode: "number" }),
  title: t.string(),
  published: t.boolean().default(false),
  ...timestamps(),
}) {
  static author = belongsTo(() => User, { foreignKey: "authorId" });
}

• Fields are non-null unless they say .nullable().
• createdAt is stored in the column created_at: fields are camelCase, columns snake_case.
• The keys are bigserial columns (below), which are 64-bit, so they use t.bigint({ mode: "number" }), which gives a plain number. Declare a key with the type of its column: a t.integer() field over a bigint column throws CastError when a row is read (a 32-bit serial column is the one for t.integer()).
• User.changeset is a changeset function: it says which fields this form may set and how to check them.

Connect

Create repo.ts:

// repo.ts
import { postgres } from "@egon-orm/postgres";
import { createRepo } from "egon-orm";

export const repo = createRepo({
  adapter: postgres({ connectionString: process.env.DATABASE_URL }),
});

The repo is an ordinary value: export it and import it where you need it. Nothing connects until the first query.

Create the tables

A migration creates them. Create setup.ts, and run it once with npx tsx setup.ts:

// setup.ts: creates the two tables. Run it once: npx tsx setup.ts
import { postgres } from "@egon-orm/postgres";
import { createMigrator, defineMigration } from "egon-orm/migration";

const createTables = defineMigration({
  up(m) {
    m.createTable("users", (table) => {
      table.id(); //                                   "id" bigserial primary key
      table.text("email").notNull().unique(); //       users_email_key, which uniqueConstraint("email") expects
      table.text("name");
      table.timestamps(); //                           created_at, updated_at
    });
    m.createTable("posts", (table) => {
      table.id();
      table.references("author_id", "users").notNull(); // a bigint, with a foreign key to users
      table.text("title").notNull();
      table.boolean("published").notNull().default(false);
      table.timestamps();
    });
  },
  down(m) {
    m.dropTable("posts"); // the undo, in reverse order
    m.dropTable("users");
  },
});

const adapter = postgres({ connectionString: process.env.DATABASE_URL });
await createMigrator({
  adapter,
  migrations: [{ version: 20261020090000, name: "create_tables", migration: createTables }],
}).up();
await adapter.close();

Here the migration is defined and run from a script; in a project, each migration is a file in a migrations/ directory, which npx egon migrate create <name> writes and npx egon migrate applies (see Migrations). If you already manage your tables with another tool, skip this step.

The table callback names its builder table: t is already the field builder imported from egon-orm elsewhere, and the two are different things.

Insert, query, update

The rest goes in main.ts, which starts with its imports:

// main.ts: run it with npx tsx main.ts
import { count, from } from "egon-orm";
import { repo } from "./repo.js";
import { User } from "./schemas.js";

A // => comment in the examples gives the value of its line: these examples run in the documentation's tests, and those values are checked. A // type: comment gives the type TypeScript infers.

Insert

const result = await repo.insert(User.changeset(User, { email: "ada@example.com" }));

if (result.success) {
  console.log(result.data.id); // a User: id, createdAt and updatedAt are filled in
} else {
  console.log(result.error.errorMessages()); // { email: ["has already been taken"] }
}

insert returns a result. An invalid changeset comes back without touching the database, and a duplicate email comes back as a field error, because the changeset declared uniqueConstraint("email"). Every write has a throwing twin, insertOrThrow.

Query

const found = await repo.getBy(User, { email: "ada@example.com" }); // type: User | null
found?.email; // => "ada@example.com"

const counts = await repo.all(
  from(User, "u")
    .leftJoinAssoc("u", "posts", "p")
    .groupBy(({ u }) => u.id)
    .select(({ u, p }) => ({ email: u.email, posts: count(p.id) })),
); // type: { email: string; posts: number }[]
counts[0]; // => { email: "ada@example.com", posts: 0 }

const withPosts = await repo.all(from(User, "u").preload("posts"));
// The first user's posts, none yet. Their type is Post[]: the query preloaded them.
withPosts[0]?.posts; // => []

Queries are built with from and run by the repo. The result type follows from the query: rows of the schema, or the shape of the select. Preloading (.preload("posts")) is described in Associations.

Update and delete

const ada = await repo.getByOrThrow(User, { email: "ada@example.com" });
const renamed = await repo.updateOrThrow(User.changeset(ada, { name: "Ada" }));
renamed.name; // => "Ada"
await repo.deleteOrThrow(renamed);

An update takes a changeset of a row you loaded and writes only what changed.

Shut down

await repo.close(); // waits for running queries, then closes the pool

Run it with npx tsx main.ts, and check the types with npx tsc.

Good to know

• No new User(). New records are changesets (User.changeset(User, params)); a User object comes only from the database.
• Writes return a result. Check result.success, or use the OrThrow twin.
• Nothing is loaded lazily. user.posts exists only on rows loaded with preload("posts").

See also

• Tutorial: a small blog, step by step.
• Schemas for every field option.
• Changesets for validating and tracking changes.
• Queries for the query builder.
• Repo for every read and write method.