Queries & Filters

Every query in Atscript's DB layer follows the same shape: a filter that selects which records to return, and a controls object that determines how they come back (sorting, pagination, projection). This syntax is consistent across all adapters.

const results = await table.findMany({
  filter: { /* which records */ },
  controls: { /* how to return them */ },
})

Filter Syntax

Filters use a MongoDB-inspired expression language. At its simplest, you pass an object whose keys are field names and whose values are the conditions to match.

Equality

The most common filter is a direct equality check:

// Shorthand — value is the match target
{ filter: { status: 'active' } }

// Explicit operator form
{ filter: { status: { $eq: 'active' } } }

Multiple fields in the same object are combined with AND:

{ filter: { status: 'active', role: 'admin' } }
// WHERE status = 'active' AND role = 'admin'

Not Equal

{ filter: { status: { $ne: 'done' } } }
// WHERE status != 'done'

Comparisons

{ filter: { age: { $gt: 18 } } }       // greater than
{ filter: { age: { $gte: 18 } } }      // greater than or equal
{ filter: { age: { $lt: 65 } } }       // less than
{ filter: { age: { $lte: 65 } } }      // less than or equal

These operators are available on number, string, and Date fields.

Set Operators

Check whether a value belongs (or does not belong) to a set:

{ filter: { role: { $in: ['admin', 'editor'] } } }
// WHERE role IN ('admin', 'editor')

{ filter: { status: { $nin: ['archived', 'deleted'] } } }
// WHERE status NOT IN ('archived', 'deleted')

Pattern Matching

{ filter: { name: { $regex: '^Al' } } }
// SQLite/PostgreSQL: WHERE name LIKE 'Al%' (or REGEXP)
// MongoDB: WHERE name matches /^Al/

$regex is available on string fields and accepts a RegExp or string.

Existence

Test whether a field is present (non-null) or absent (null):

{ filter: { email: { $exists: true } } }   // WHERE email IS NOT NULL
{ filter: { email: { $exists: false } } }   // WHERE email IS NULL

Null Values

You can also filter for null directly:

{ filter: { assigneeId: null } }
// WHERE assigneeId IS NULL

Logical Operators

Implicit AND

When you put multiple fields in a single filter object, they are ANDed together automatically:

{ filter: { status: 'active', role: 'admin' } }

Explicit AND

Use $and when you need multiple conditions on the same field, or just prefer being explicit:

{ filter: { $and: [
  { age: { $gte: 18 } },
  { age: { $lt: 65 } },
] } }

OR

{ filter: { $or: [
  { status: 'active' },
  { role: 'admin' },
] } }

NOT

Negate a set of conditions:

{ filter: { $not: { status: 'archived' } } }

Nested Combinations

Logical operators compose naturally:

{ filter: {
  $and: [
    { $or: [
      { priority: 'high' },
      { priority: 'critical' },
    ] },
    { $not: { status: 'done' } },
  ],
} }

Nested Field Filters

Atscript automatically flattens nested objects into __-separated column names (e.g., a contact.email field becomes the contact__email column). When filtering, use dot notation — the adapter translates it to the physical column name:

{ filter: { 'contact.email': 'alice@example.com' } }
// SQL: WHERE contact__email = 'alice@example.com'

{ filter: { 'address.city': { $in: ['Berlin', 'Paris'] } } }
// SQL: WHERE address__city IN ('Berlin', 'Paris')

This works with all operators — comparisons, $regex, $exists, and logical combinators.

Query Controls

The controls object determines how the result set is shaped.

Sorting

Use $sort with 1 for ascending and -1 for descending:

controls: { $sort: { name: 1 } }          // A → Z
controls: { $sort: { createdAt: -1 } }    // newest first

Multiple sort keys are applied in order:

controls: { $sort: { status: 1, name: -1 } }
// ORDER BY status ASC, name DESC

Pagination

controls: {
  $limit: 10,   // return at most 10 records
  $skip: 20,    // skip the first 20 records
}

Field Selection

Include specific fields using array form:

controls: { $select: ['id', 'name', 'email'] }

Or exclude fields with an object where 0 means exclude:

controls: { $select: { password: 0, internalNotes: 0 } }

When selecting a nested object parent, all its child fields are included:

controls: { $select: ['id', 'contact'] }
// Includes contact.email, contact.phone, etc.

FK Fields Auto-Included

When using $select with relation loading ($with), foreign key fields needed for relation resolution (e.g., assigneeId for an assignee relation) are automatically included even if not listed in $select.

Paginated Results

Use findManyWithCount() to get both data and total count in one call — see CRUD Operations — Find Many with Count for the API and examples.

Type-Safe Generics

Queries are fully typed. findOne and findMany accept a Uniquery<OwnProps, NavType> that constrains filter fields to own (non-navigation) properties. The return type DbResponse automatically strips navigation properties from the result unless you request them via $with.

When the query type is a literal (not widened), TypeScript infers exactly which navigation properties are returned:

// result type includes `assignee` but not other nav props
const tasks = await taskTable.findMany({
  controls: { $with: [{ name: 'assignee' }] },
})

Query Expressions

Query expressions are a compile-time syntax used inside .as files to define view filters, join conditions, and relation filters. They are not used in runtime TypeScript queries — they are embedded in annotations and compiled into the schema.

Syntax

Expressions are wrapped in backticks inside .as files:

@db.view.filter `Task.status != 'done'`

Field References

Reference fields using TableName.fieldName:

@db.view.filter `Task.priority = 'high'`
@db.view.joins Project, `Project.id = Task.projectId`

Operators

Operator	Meaning	Example
=	equals	`Task.status = 'active'`
!=	not equals	`Task.status != 'done'`
>	greater than	`Task.priority > 3`
>=	greater than or equal	`Task.priority >= 3`
<	less than	`Task.age < 65`
<=	less than or equal	`Task.age <= 65`
~=	regex match	`User.name ~= '^Al'`
?	exists (non-null)	`Task.assigneeId ?`
!?	not exists (null)	`Task.deletedAt !?`

Set Membership

Use curly braces for IN / NOT IN:

@db.view.filter `Task.status {active, pending}`
@db.view.filter `Task.role !{guest, bot}`

Logical Combinators

Combine conditions with && (and), || (or), and !() (not). Use parentheses for grouping:

@db.view.filter `Task.status != 'done' && Task.priority >= 3`
@db.view.filter `(Task.status = 'active' || Task.status = 'pending') && Task.assigneeId ?`
@db.view.filter `!(Task.status = 'archived')`

Where They Are Used

Query expressions appear in these annotations:

• @db.view.filter — row-level filter for a view
• @db.view.joins — join condition between tables in a view
• @db.view.having — having clause for aggregation views
• @db.rel.filter — static filter applied when loading a relation

Example in a view definition:

@db.view
@db.view.for Task
@db.view.joins Project, `Project.id = Task.projectId`
@db.view.filter `Task.status != 'done' && Task.priority >= 3`
type ActiveHighPriorityTasks {
  taskId: Task.id
  title: Task.title
  projectName: Project.name
}

Combining It All

A practical example that brings filters, sorting, pagination, and field selection together:

const tasks = await taskTable.findMany({
  filter: {
    status: { $ne: 'done' },
    priority: { $in: ['high', 'critical'] },
    'project.active': true,
  },
  controls: {
    $sort: { priority: -1, createdAt: 1 },
    $limit: 20,
    $skip: 0,
    $select: ['id', 'title', 'status', 'priority'],
  },
})

This returns the first 20 non-done tasks with high or critical priority from active projects, sorted by priority descending then creation date ascending, with only the selected fields.

Next Steps

• CRUD Operations — Insert, read, update, delete
• Update & Patch — Embedded array and object patch operators
• Views — Managed, external, and materialized views
• Relations — Navigation property loading and deep operations