Veap uses Knex as the query engine, with two client targets: better-sqlite3 for SQLite and pg for PostgreSQL. On top of Knex it provides an ActiveRecord-style ORM (the Model class), a schema builder for migrations, and a scoped transaction mechanism.
Connecting#
DatabaseServiceProvider (registered by .withDatabase()) creates the Knex instance from DATABASE_URL at boot:
- URLs starting with
sqlite:/file:or ending in.sqlite/.dbselectbetter-sqlite3. All prefix forms (sqlite:./storage/veap.sqlite,sqlite://...,file:...) are understood, and the parent directory is created automatically. - Anything else selects
pg, with SSL enabled automatically in production (unless the URL containssslmode=disable).
The instance is registered in the container under the token DATABASE (and string token "Knex") and also set as the global instance used by the ORM.
# development
DATABASE_URL="sqlite:./storage/veap.sqlite"
# production
DATABASE_URL="postgresql://user:pass@host:5432/veap?sslmode=require"You can also initialize a connection manually with initDatabase({ client, connection }) from @veap/core/database, which accepts driver shortcuts ("postgres" | "sqlite" | "mysql"). Note that mysql support in initDatabase maps to mysql2, but migrations and tests target SQLite and PostgreSQL.
The three layers#
| Layer | Import | Use for |
|---|---|---|
| ORM models | Model, User, ... from @veap/core/database and model packages | 95% of data access; ActiveRecord API with relations, casts, scopes, and traits |
| Query builder | Model.query() returns ModelQueryBuilder (Knex underneath) | everything the static helpers do not cover; toKnex() for raw escape hatch |
| Raw access | dbClient("users").where(...), dbClient.raw(sql) | migrations, exotic queries |
Transactions#
All writes that must be atomic go through transaction(), which uses AsyncLocalStorage so any ORM call made inside the callback automatically joins the active transaction, including nested transaction() calls:
import { transaction } from "@veap/core/database";
await transaction(async (trx) => {
const user = await User.create({ email, name });
await Profile.create({ userId: user.id });
// any model query in this async context uses trx automatically
});Details and pitfalls in Transactions.
Migrations#
Migrations are plain objects { name, up(db, schema), down? }, tracked per scope (core, app, or a plugin id) in a migrations table, executed in a transaction with a Postgres advisory lock. The CLI generates files and the runner executes them at boot; see Migrations.
Where data lives#
- Core owns
users,sessions,roles,permissions, pivots,password_reset_sessions,email_verification_sessions,plugins,templates,settings,user_widgets. - Plugins own their own tables (created by their migrations) and their own models.
- Models for core entities are exported from the models entries:
@veap/core/auth/models(User,Session,Role,Permission,PasswordResetSession,EmailVerification),@veap/core/plugins/models(SystemPlugin,SystemUserWidget),@veap/core/settings/models(Setting).
