Retrieving Models

Once you have defined your models, you can use them to retrieve data from your database.

Retrieving All Models

To retrieve all records from a table, you can use the query method followed by the get method.

const users = await User.query().get();

Retrieving Single Models

Find

If you know the primary key of the model you want to retrieve, you can use the find method. This method returns the model instance if found, or undefined otherwise.

// Returns User | undefined
const user = await User.find(1);

You can also pass an array of primary keys to retrieve multiple records.

// Returns User[]
const users = await User.find([1, 2, 3]);

The findOrFail method works similarly but throws an error if the model is not found.

// Returns User, Throws Error if not found
const user = await User.findOrFail(1);

First

To retrieve the first record matching your query constraints, use the first method.

const user = await User.where("active", true).first();

Just like find, there is a firstOrFail method that throws an error if no matching record is found.

// Returns User, Throws Error if no active user is found
const activeUser = await User.where("active", true).firstOrFail();

Filtering Results

Where Clauses

You can filter results using the where method.

The most basic call requires two arguments: the name of the column and the value to match against.

const user = await User.where("votes", 100).get();

You may also pass a comparison operator as the second argument and the value as the third argument.

const user = await User.where("votes", "=", 100).get();

const user = await User.where("votes", ">=", 100).get();

const user = await User.where("votes", "<>", 100).get();

Where In

You can use the whereIn method to verify that a given column's value is contained within the given array of values.

const users = await User.whereIn("id", [1, 2, 3]).get();

Where Not Null

The whereNotNull method verifies that the column's value is not NULL.

const users = await User.whereNotNull("updated_at").get();

Ordering

To sort the results of the query, you may use the orderBy method. The first argument should be the column you wish to sort by, while the second argument controls the direction of the sort and may be either asc or desc.

const user = await User.orderBy("name", "desc").get();

Pagination

Vasta's paginator is integrated with the query builder and provides a convenient, easy-to-use way of paginating database results. The paginate method accepts the number of items per page as its first argument and the current page number as its second argument.

const result = await User.paginate(15, 1);

The paginate method returns an object with the following properties:

• data: An array of model instances for the current page.
• total: The total number of records matching the query.
• perPage: The number of items per page.
• currentPage: The current page number.
• lastPage: The last page number.

Aggregates

The query builder also provides a variety of aggregate methods such as count, max, and sum.

const count = await User.count();

const max = await User.max("price");

Selecting Columns

You may not always want to select all columns from a database table. Using the select method, you can specify a custom "select" clause for the query:

const users = await User.select(["name", "email"]).get();

Expressions & Subqueries

Because Vasta is built on top of Kysely, it natively supports Kysely's expression and subquery system for advanced queries. You can pass an ExpressionBuilder callback—or standard Kysely Expression objects—directly into your select, where, whereIn, and orderBy clauses.

For detailed information on writing expressions, please refer to the official Kysely documentation about expressions.

Select Expressions

You can use the ExpressionBuilder callback in the select method to utilize database functions, aliasing, and raw values. Vasta provides strict typings for aliased selections right out of the box:

const users = await User.select((eb) => [
  "id",
  "email",
  eb.fn("upper", ["name"]).as("upper_name"), // SQL: upper("name") as "upper_name"
]).get();

// Typed access is natively supported on the returned model instance!
console.log(users[0].upper_name);
console.log(users[0].attributes.upper_name);

Where Expressions

You can use the same ExpressionBuilder context in where clauses to filter against database functions or computed columns without needing to drop down into raw SQL strings:

// Using database functions in constraints
const users = await User.where((eb) => eb.fn("upper", ["name"]), "=", "DAVID").get();

Subqueries

You can also pass Kysely subquery expressions directly into your constraints. This is highly useful for matching values against a separate query result:

import { db } from "./database";

// Create a subquery (resolves to a single value)
const latestPostSubquery = db.selectFrom("posts").select("user_id").orderBy("created_at", "desc").limit(1);

// Find the user who made the latest post matching the subquery
const user = await User.where("id", "=", latestPostSubquery).first();

You can use arrays and in clauses in exactly the same way to match multiple results from a subquery:

// Query returning an array of IDs
const activeUsersSubquery = db.selectFrom("sessions").select("user_id").where("is_active", "=", true);

// Pass the subquery properly to the "in" operator
const activeUsers = await User.whereIn("id", activeUsersSubquery).get();