Standard Model Methods

Hysteria ORM models provide a rich set of static methods for interacting with your database. These methods are fully type-safe and support advanced options. Standard methods are type safe since only model columns are allowed to be selected.

CRUD Methods

find

Fetch multiple records matching criteria.

const users = await User.find({ where: { status: 'active' } });

const usersWithNameAndEmail = await User.find({
  select: ["name", "email"],
}); // { name: string, email: string }[]

findOne

Fetch a single record matching criteria.

const user = await User.findOne({ where: { email: 'john@example.com' } });

const userWithNameAndEmail = await User.findOne({
  select: ["name", "email"],
}); // { name: string, email: string } | null

findOneOrFail

Fetch a single record or throw if not found.

const user = await User.findOneOrFail({ where: { email: 'john@example.com' } });

insert

Insert a new record.

const user = await User.insert({ name: 'John', email: 'john@example.com' });

insertMany

Insert multiple records.

const users = await User.insertMany([
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' },
]);

updateRecord

Update a record by primary key.

user.name = 'Johnny';
const updated = await User.updateRecord(user);

deleteRecord

Delete a record by primary key.

await User.deleteRecord(user);

firstOrInsert

Find a record or create it if it doesn't exist.

// First argument: search criteria
// Second argument: data to insert if not found
const user = await User.firstOrInsert(
  { email: 'john@example.com' },
  { name: 'John', email: 'john@example.com', status: 'active' }
);

upsert

Insert or update a record based on conflict keys (QueryBuilder only).

const [post] = await sql.query('posts').upsert(
  { id: uuid, title: 'Title', content: 'Content' },  // data
  { id: uuid },                                       // conflict keys
  { returning: ['id', 'title'] }                      // options
);

upsertMany

Insert or update multiple records (QueryBuilder only).

await sql.query('posts').upsertMany(
  ['id'],                                             // conflict columns
  ['title', 'content'],                               // columns to update on conflict
  [
    { id: uuid1, title: 'First', content: 'Content 1' },
    { id: uuid2, title: 'Second', content: 'Content 2' },
  ]
);

Where Clause Operations

The where clause in find, findOne, and findOneOrFail supports a rich set of operators for filtering data. You can use simple equality, comparison operators, pattern matching, and complex nested conditions.

Simple Equality

The simplest form is direct field-value matching:

// Simple equality
const users = await User.find({
  where: { email: 'john@example.com' }
});

// Multiple fields (AND logic)
const users = await User.find({
  where: { status: 'active', role: 'admin' }
});

Comparison Operators

Use the op property to specify comparison operations:

// Equal ($eq) - same as simple equality
const users = await User.find({
  where: { age: { op: '$eq', value: 25 } }
});

// Not equal ($ne)
const users = await User.find({
  where: { status: { op: '$ne', value: 'inactive' } }
});

// Greater than ($gt)
const users = await User.find({
  where: { age: { op: '$gt', value: 18 } }
});

// Greater than or equal ($gte)
const users = await User.find({
  where: { salary: { op: '$gte', value: 50000 } }
});

// Less than ($lt)
const users = await User.find({
  where: { age: { op: '$lt', value: 65 } }
});

// Less than or equal ($lte)
const users = await User.find({
  where: { balance: { op: '$lte', value: 1000 } }
});

Range Operators

// Between
const users = await User.find({
  where: { age: { op: '$between', value: [18, 30] } }
});

// Not between
const users = await User.find({
  where: { age: { op: '$not between', value: [18, 30] } }
});

Null Checks

// Is null
const users = await User.find({
  where: { deletedAt: { op: '$is null' } }
});

// Is not null
const users = await User.find({
  where: { email: { op: '$is not null' } }
});

Pattern Matching

// LIKE
const users = await User.find({
  where: { name: { op: '$like', value: 'John%' } }
});

// NOT LIKE
const users = await User.find({
  where: { email: { op: '$not like', value: '%spam%' } }
});

// ILIKE (case-insensitive, PostgreSQL)
const users = await User.find({
  where: { name: { op: '$ilike', value: '%john%' } }
});

// NOT ILIKE
const users = await User.find({
  where: { name: { op: '$not ilike', value: '%test%' } }
});

Array Operators

// IN - match any value in array
const users = await User.find({
  where: { status: { op: '$in', value: ['active', 'pending'] } }
});

// NOT IN - exclude values in array
const users = await User.find({
  where: { role: { op: '$nin', value: ['banned', 'suspended'] } }
});

Regular Expression

// REGEXP
const users = await User.find({
  where: { email: { op: '$regexp', value: /^[a-z]+@example\.com$/ } }
});

// NOT REGEXP
const users = await User.find({
  where: { name: { op: '$not regexp', value: /^test/i } }
});

Logical Operators

$and - Combine conditions with AND

const users = await User.find({
  where: {
    $and: [
      { status: 'active' },
      { age: { op: '$gte', value: 18 } }
    ]
  }
});

$or - Combine conditions with OR

const users = await User.find({
  where: {
    $or: [
      { role: 'admin' },
      { role: 'moderator' }
    ]
  }
});

Complex Nested Conditions

You can nest $and and $or operators to create sophisticated queries:

// Find users who are:
// (active AND age >= 18) OR (premium AND verified)
const users = await User.find({
  where: {
    $or: [
      {
        $and: [
          { status: 'active' },
          { age: { op: '$gte', value: 18 } }
        ]
      },
      {
        $and: [
          { isPremium: true },
          { isVerified: true }
        ]
      }
    ]
  }
});

// Combine top-level fields with $or
const users = await User.find({
  where: {
    status: 'active',  // AND
    $or: [
      { name: { op: '$like', value: 'John%' } },
      { name: { op: '$like', value: 'Jane%' } }
    ]
  }
});

// Deeply nested conditions
const users = await User.find({
  where: {
    $or: [
      {
        $and: [
          { status: 'active' },
          { age: { op: '$between', value: [20, 30] } }
        ]
      },
      {
        $and: [
          { status: 'inactive' },
          {
            $or: [
              { age: { op: '$gt', value: 50 } },
              { name: { op: '$like', value: 'VIP%' } }
            ]
          }
        ]
      }
    ]
  }
});

Operators Reference

Operator	Description	Example Value
$eq	Equal to	{ op: '$eq', value: 'active' }
$ne	Not equal to	{ op: '$ne', value: 'deleted' }
$gt	Greater than	{ op: '$gt', value: 18 }
$gte	Greater than or equal	{ op: '$gte', value: 0 }
$lt	Less than	{ op: '$lt', value: 100 }
$lte	Less than or equal	{ op: '$lte', value: 999 }
$between	Between two values	{ op: '$between', value: [10, 20] }
$not between	Not between two values	{ op: '$not between', value: [10, 20] }
$is null	Is NULL	{ op: '$is null' }
$is not null	Is not NULL	{ op: '$is not null' }
$like	LIKE pattern	{ op: '$like', value: '%test%' }
$not like	NOT LIKE pattern	{ op: '$not like', value: '%spam%' }
$ilike	Case-insensitive LIKE	{ op: '$ilike', value: '%John%' }
$not ilike	Case-insensitive NOT LIKE	{ op: '$not ilike', value: '%test%' }
$in	In array	{ op: '$in', value: [1, 2, 3] }
$nin	Not in array	{ op: '$nin', value: [4, 5, 6] }
$regexp	Regular expression match	{ op: '$regexp', value: /pattern/ }
$not regexp	Not matching regex	{ op: '$not regexp', value: /pattern/ }

Best Practices

• Always use the static methods for database operations.
• Use findOneOrFail for required lookups.
• Use firstOrInsert and upsert for idempotent operations.
• Prefer $and and $or for complex conditions to make your queries more readable.
• Use $in instead of multiple $or conditions when checking against a list of values.

---

Next: Query Builder