Defining SQL Models

Models represent tables in your database. Define them using the defineModel function with the col and rel namespaces. For the full API reference including nullable-aware types, generics, and typed serialize/prepare, see Programmatic Models (defineModel).

Example: User Model

import { defineModel, col } from "hysteria-orm";

const Post = defineModel("posts", {
  columns: {
    id: col.increment(),
    title: col.string(),
    body: col.text(),
    userId: col.integer(),
    createdAt: col.datetime({ autoCreate: true }),
    updatedAt: col.datetime({ autoCreate: true, autoUpdate: true }),
  },
});

const Address = defineModel("addresses", {
  columns: {
    id: col.increment(),
    street: col.string(),
    city: col.string(),
  },
});

const UserAddress = defineModel("user_addresses", {
  columns: {
    id: col.increment(),
    userId: col.integer(),
    addressId: col.integer(),
  },
});

const User = defineModel("users", {
  columns: {
    id: col.increment(),
    name: col.string(),
    email: col.string({ nullable: false }),
    password: col.string({ hidden: true }),
    status: col.enum(["active", "inactive"] as const),
    isActive: col.boolean(),
    balance: col.decimal({ precision: 10, scale: 2 }),
    metadata: col.json(),
    createdAt: col.datetime({ autoCreate: true }),
    updatedAt: col.datetime({ autoCreate: true, autoUpdate: true }),
  },
});

Relations are defined in a separate schema file — see Relations below.

Column Types

All column types are available via the col namespace. Each returns a ColumnDef with full TypeScript inference.

Method	Base Type	Description
`col()`	any (user-defined via generic)	Generic column. Accepts options like `primaryKey`, `hidden`, etc.
`col.primary()`	`string \| number`	Generic primary key column.
`col.increment()`	`number`	Auto-incrementing integer primary key (always non-nullable).
`col.bigIncrement()`	`number`	Auto-incrementing bigint primary key (always non-nullable).
`col.integer()`	`number`	Integer column.
`col.bigInteger()`	`number`	Big integer column. Handles Postgres string-to-Number conversion.
`col.float()`	`number`	Float column.
`col.decimal()`	`number`	Decimal column with optional `precision` and `scale`.
`col.string()`	`string`	VARCHAR column with optional `length`.
`col.text()`	`string`	LONGTEXT column for longer text content.
`col.boolean()`	`boolean`	Boolean column, handles DB-specific formats (e.g., MySQL tinyint).
`col.json()`	`unknown` (customizable via generic)	JSON/JSONB column.
`col.date()`	`Date`	DATE column (YYYY-MM-DD).
`col.datetime()`	`Date`	DATETIME column (YYYY-MM-DD HH:mm:ss), with auto-creation and auto-update.
`col.timestamp()`	`Date`	Unix timestamp column, with auto-creation and auto-update.
`col.time()`	`Date`	TIME column (HH:mm:ss), with auto-creation and auto-update.
`col.uuid()`	`string`	Auto-generates a UUID if not provided.
`col.ulid()`	`string`	Auto-generates a ULID if not provided.
`col.binary()`	`Buffer \| Uint8Array \| string`	Binary/blob column.
`col.enum(values)`	`values[number]`	Enum column constrained to the provided values array.
`col.encryption.symmetric(opts)`	`string`	Encrypts/decrypts value using a symmetric key.
`col.encryption.asymmetric(opts)`	`string`	Encrypts/decrypts value using asymmetric keys.

Nullable-Aware Type Inference

Primary key columns (col.increment(), col.bigIncrement(), col.primary(), col.uuid({ primaryKey: true })) are non-nullable.
Non-PK columns are nullable by default — the type includes | null | undefined. Use { nullable: false } to make them required.

const Product = defineModel("products", {
  columns: {
    id: col.increment(), // number (non-nullable)
    name: col.string({ nullable: false }), // string (required)
    description: col.string(), // string | null | undefined
    price: col.decimal({ precision: 10, scale: 2, nullable: false }), // number (required)
    status: col.enum(["draft", "published"] as const), // "draft" | "published" | null | undefined
  },
});

Date/Time Column Types

Choose the appropriate date/time column type based on your database column:

Method	Database Column Type	Format	Use Case	Migration Example
`col.date()`	`DATE`	YYYY-MM-DD	Birth dates, event dates (no time)	`table.date('birth_date')`
`col.datetime()`	`DATETIME`, `DATETIME2`	YYYY-MM-DD HH:mm:ss	Created/updated timestamps	`table.timestamp('created_at')`
`col.timestamp()`	`TIMESTAMP` (as integer)	Unix timestamp (e.g., 1766152251)	High-performance timestamps as integers	`table.integer('last_login')`
`col.time()`	`TIME`	HH:mm:ss	Start/end times, durations (no date)	`table.time('start_time')`

Important Notes:

When using table.timestamp() in migrations, it creates DATETIME/DATETIME2 columns (not Unix timestamp integers). Use col.datetime() for these columns.
For actual Unix timestamp integers, define the column as table.integer() or table.bigint() and use col.timestamp() in your model.
All date/time column types support autoCreate and autoUpdate options for automatic timestamp management. For autoUpdate, MySQL/MariaDB use the native ON UPDATE CURRENT_TIMESTAMP; on other databases, migrations automatically generate an update trigger (trigger name: trg_{table}_{column}_auto_update).
All date/time column types support the timezone option ('UTC' or 'LOCAL').

const Event = defineModel("events", {
  columns: {
    id: col.increment(),
    birthDate: col.date(), // DATE (YYYY-MM-DD)
    createdAt: col.datetime({ autoCreate: true }), // DATETIME (auto-set on create)
    lastModified: col.timestamp({ autoCreate: true, autoUpdate: true }), // Unix timestamp
    businessHoursStart: col.time(), // TIME (HH:mm:ss)
  },
});

Column Options

Pass options to any col method to control column behavior:

Option	Type	Default	Description
`primaryKey`	boolean	false	Marks this column as the primary key. Only one primary key is allowed per model.
`serialize`	function	undefined	Function to transform the value after reading from the database (e.g., parse, convert type).
`prepare`	function	undefined	Function to transform the value before writing to the database (e.g., format, encrypt).
`hidden`	boolean	false	If true, this column will not appear in serialized output (e.g., API responses).
`autoUpdate`	boolean	false	If true, `prepare` will always be called on update, even if the value is not in the payload.
`databaseName`	string	property name (case-converted)	Custom name for the column in the database.
`nullable`	boolean	true (false for PK columns)	If false, the column cannot be null.
`default`	string \| number \| null \| boolean	undefined	The default value for the column in the database.

const User = defineModel("users", {
  columns: {
    id: col.integer({
      primaryKey: true,
      hidden: true,
      databaseName: "user_id",
    }),
    name: col.string({
      prepare: (value) => value.trim(),
      serialize: (value) => value.toUpperCase(),
    }),
    status: col.string({ default: "active" }),
  },
});

Notes:

primaryKey: Composite primary keys are not supported. Defining more than one will throw an error.
hidden: Useful for sensitive fields like passwords.
prepare/serialize: Use for custom transformations, e.g., encryption, formatting, or type conversion.
databaseName: Use if your DB column name differs from your property name or case convention.

Relations

Relations are not defined inside defineModel. Use defineRelations + createSchema in a dedicated schema file instead. This approach works for all project sizes and eliminates circular import issues entirely.

See Defining Relations for the full API and step-by-step example.

Helper	Description	Foreign Key Location
`hasOne`	One-to-one relationship	On the related model
`hasMany`	One-to-many relationship	On the related model
`belongsTo`	Inverse of hasOne/hasMany	On the current model
`manyToMany`	Many-to-many via join table	On the join/pivot table

import { createSchema, defineRelations } from "hysteria-orm";
import { User } from "./user";
import { Post } from "./post";

const UserRelations = defineRelations(User, ({ hasMany }) => ({
  posts: hasMany(Post, { foreignKey: "userId" }),
}));

const PostRelations = defineRelations(Post, ({ belongsTo }) => ({
  user: belongsTo(User, { foreignKey: "userId" }),
}));

export const schema = createSchema(
  { users: User, posts: Post },
  { users: UserRelations, posts: PostRelations },
);

export const UserModel = schema.users;
export const PostModel = schema.posts;

Table Constraints

Pass indexes, uniques, and checks arrays to defineModel. Column references are type-checked against your columns definition.

const User = defineModel("users", {
  columns: {
    id: col.increment(),
    name: col.string(),
    email: col.string({ nullable: false }),
    age: col.integer(),
    status: col.string(),
  },
  indexes: [
    ["email"], // simple array form
    { columns: ["name", "email"], name: "idx_name_email" }, // object form with custom name
  ],
  uniques: [["email"], { columns: ["email"], name: "uq_users_email" }],
  checks: [
    "age >= 18", // string form
    {
      expression: "status IN ('active', 'inactive', 'banned')",
      name: "chk_status",
    }, // object form
  ],
});

Constraint names

Always provide explicit constraint names for production models. Auto-generated names are deterministic but less readable. Explicit names make migration history and database debugging easier.

Model Options

Customize model behavior via the options key in defineModel:

Option	Description	Default
`softDeleteColumn`	Column name used for soft deletes.	`"deletedAt"`
`modelCaseConvention`	Case convention for model properties.	Auto-detected
`databaseCaseConvention`	Case convention for database columns.	Auto-detected

See Case Conventions for detailed information on case conversion.

Example: User Model​

Column Types​

Nullable-Aware Type Inference​

Date/Time Column Types​

Column Options​

Relations​

Table Constraints​

Model Options​