Skip to content
GitHub

Strutvane 0.2 is still being built. These pages describe how it will work; anything marked "Coming in 0.2" doesn't exist yet. Release notes and support policy.

Database Testing

View as Markdownllms.txt

Most bugs live where your code meets the database: a row that should be soft-deleted but is gone, a slug that isn’t unique per team, a query that leaks another customer’s data. Database tests create rows, run your code, and check what’s stored. Strutvane runs them against a real database engine, never a mock, because a mock can’t tell you that MySQL compares '1abc' = 1 as true.

tests/feature/post-store.test.ts
import { randomBytes } from 'node:crypto'
import { beforeEach, expect, it } from 'vitest'
import type { Application } from '@strutvane/framework'
import { client, refreshDatabase, seed } from '@strutvane/framework/testing'
import { createApp } from '../../bootstrap/app.ts'
import { Post } from '../../app/models/post.ts'
import { demoTokens } from '../../database/seeders/database.seeder.ts'
let app: Application
beforeEach(async () => {
app = createApp({ APP_ENV: 'testing', APP_KEY: 'base64:' + randomBytes(32).toString('base64'), DB_CONNECTION: 'sqlite', DB_DATABASE: ':memory:' })
await refreshDatabase(app)
await seed(app)
})
it('stores a new post as a draft owned by its author', async () => {
await client(app).withToken(demoTokens.alice).postJson('/api/posts', { title: 'Database testing' })
const post = await app.run(() => Post.query().where('slug', 'database-testing').first())
expect(post?.status).toBe('draft')
expect(post?.author_id).toBe(1)
})

What you’ll see:

✓ tests/feature/post-store.test.ts (1 test) 22ms

The ORM is used inside app.run(…), because models read the current app’s connection.

refreshDatabase(app) creates every table in the test database from .strutvane/sql/schema.<engine>.sql, the schema your migrations produce. It doesn’t replay each migration file, so it takes milliseconds even with a hundred tables.

How Speed When
refreshDatabase(app) in beforeEach (works today in the prototype) fast with SQLite in memory; slower on a server small apps, SQLite
a transaction per test (0.2) the fastest on every engine the default in 0.2

A transaction per test works like this: the schema is built once per worker, each test runs inside a database transaction, and the transaction is rolled back at the end. Every test starts from the same rows, and nothing is ever deleted by hand.

A database per worker: Vitest runs test files in parallel, in several workers. In 0.2 each worker gets its own copy of the test database (my_app_test_1, my_app_test_2…), so parallel files never see each other’s rows.

Your app uses Tests use Why
MySQL the MySQL test database (DB_TEST_DATABASE=<app>_test) MySQL’s type coercion, collations and NULLS LAST behave differently from SQLite
PostgreSQL the PostgreSQL test database row-level security (tenancy) only exists there
SQLite a SQLite file or :memory: the same engine

Running MySQL-app tests on SQLite would pass tests that fail in production. With Docker, the test database is already running next to your dev database; without Docker, create it on your server once and put its name in DB_TEST_DATABASE.

A factory makes realistic rows for tests: you say what matters, and it fills in the rest. node strut make:migration create_posts_table --seeder already wrote one for you.

The generated file, database/factories/post.factory.ts, describes one typical post (see Factories for what it looks like today). Use it in tests:

const alice = await User.factory().create({ name: 'Alice' })
const one = await Post.factory().for(alice, 'author').create() // one draft by Alice
const many = await Post.factory().count(10).state({ status: 'published' }).for(alice, 'author').create()
const titled = await Post.factory().create({ title: 'Exactly this title' }) // override a field
const unsaved = Post.factory().make() // built, not stored

What you’ll see: rows in the test database, returned as Post models with ids, slugs and timestamps filled in. Slugs follow the real rules (-2, -3 on a clash), because factories go through the model.

await Post.factory().count(3).sequence({ status: 'draft' }, { status: 'published' }).create()
// → draft, published, draft

Children and parents: has, for and recycle

Section titled “Children and parents: has, for and recycle”
// A user with five posts
const author = await User.factory().has(Post.factory().count(5), 'posts').create()
// Ten posts that all share one author, instead of creating ten authors
const posts = await Post.factory().count(10).recycle(author).create()

Without recycle, a factory that needs a parent makes a new parent for every row: ten posts, ten authors. That’s rarely what you meant (it was a real bug in the 0.1 demo app).

Run the seeders when a test needs the app’s standard data:

await seed(app) // database/seeders/database.seeder.ts, which runs every registered seeder
await seed(app, 'PostSeeder') // one seeder, like node strut db:seed --class=PostSeeder

A seeder is a defineSeeder({ after?, async run({ db, factory, env }) { … } }) file in database/seeders/; see Seeding. seed() is the testing kit’s planned helper (names may change).

Seeders are tracked once per database in development, but tests start from an empty database each time, so they always run here. Prefer factories for data a test depends on: a test that says Post.factory().create({ title: 'X' }) explains itself; a test that relies on “the third seeded post” doesn’t.

const count = await app.run(() => Post.query().where('status', 'published').count())
expect(count).toBe(3)
await assertDatabaseHas(app, 'posts', { slug: 'hello-world', status: 'published' })
await assertDatabaseMissing(app, 'posts', { slug: 'alice-draft' })
await assertDatabaseCount(app, 'posts', 3)
await assertSoftDeleted(app, 'posts', { slug: 'old-news' })

A failure prints the rows that are in the table, so you see what’s there instead of guessing.

it('soft deletes a post', async () => {
await client(app).withToken(demoTokens.alice).deleteJson('/api/posts/hello-world')
const visible = await app.run(() => Post.query().where('slug', 'hello-world').first())
const trashed = await app.run(() => Post.query().withTrashed().where('slug', 'hello-world').first())
expect(visible).toBeNull()
expect(trashed?.deleted_at).not.toBeNull()
})

The row still exists with deleted_at set; normal queries don’t see it. See Soft Deletes.

With teams, every query is scoped to the current team. Database tests must set one, or the ORM refuses (fail closed):

TenantRequiredError: "projects" is tenant-scoped, but no tenant is active. Wrap the work in runWithTenant(tenantId, () => …) (the HTTP layer does this for you per request). Deliberate cross-tenant jobs use .unsafeWithoutTenancy(reason), which is audited.

The error code is tenant_required (answered as 403 over HTTP). Set the team for a block of code with the ORM’s runWithTenant() (proven in spike 11):

import { runWithTenant } from '@strutvane/framework'
await app.run(() => runWithTenant('acme', () => Project.create({ name: 'Secret launch' })))
const seenByGlobex = await app.run(() => runWithTenant('globex', () => Project.query().count()))
expect(seenByGlobex).toBe(0) // globex sees none of acme's projects

The testing kit’s planned asTenant(team) does the same for HTTP test clients.

On PostgreSQL the database’s row-level security checks this again underneath, so even a raw query can’t cross teams. Test it on PostgreSQL if that’s your production engine. See Tenancy.

When your code uses transaction(), it works the same inside the test’s own transaction: Strutvane uses savepoints for nested transactions, so your code’s rollback undoes only its own part. Parallel savepoints on one connection are refused (a real bug the ORM prototype found), so don’t Promise.all two transactions inside a test.

  • With Docker: MySQL or PostgreSQL run in the db container, and the test database was created on its first start. npm test runs inside the app container.
  • Without Docker, SQLite: nothing to set up; the test file is database/testing.sqlite.
  • Without Docker, a server database: create the test database once and give your user rights on it, then set DB_TEST_DATABASE.
  • Windows: SQLite test files can stay locked by an open database tool. Close it if refreshDatabase fails with EBUSY.
SqliteError: no such table: posts

Why: the test didn’t call refreshDatabase(app), or .strutvane/sql/schema.sqlite.sql is stale because you added a migration and didn’t run node strut migrate. Fix: call refreshDatabase(app) in beforeEach, and run node strut migrate (or node strut sync) after changing migrations.

Tests pass locally on SQLite but fail in CI on MySQL

Section titled “Tests pass locally on SQLite but fail in CI on MySQL”

Why: the engines differ: MySQL compares strings and numbers loosely, sorts NULL differently, and its collation decides case sensitivity. Fix: run tests locally on the same engine as CI and production.

Why: the app (or the database) is shared across tests and nothing cleans it. Fix: build the app and refresh the database in beforeEach, as above. In 0.2 the per-test transaction does this for you.

Why: each post needed an author and the factory made one per row. Fix: Post.factory().count(10).recycle(author).create().

“Model.query() was called outside a request or console command”

Section titled ““Model.query() was called outside a request or console command””

Why: the ORM needs the current app to find its connection. Fix: wrap it: await app.run(() => Post.query().count()).