Database Testing

On this page 28

Stacks provides powerful utilities for testing database operations, including automatic transaction rollback, factories, seeders, and assertions for verifying database state.

Overview

Database testing helps you:

Test queries - Verify CRUD operations work correctly
Test relationships - Ensure model associations function
Test constraints - Verify uniqueness, foreign keys, etc.
Isolate tests - Each test gets a clean database state

Setup

Test Database Configuration

Configure a separate database for testing in .env.test:

# .env.test
DATABASE_URL=sqlite://./test.db
# Or use in-memory SQLite for speed
DATABASE_URL=sqlite://:memory:

Database Migrations

Ensure migrations run before tests:

// tests/setup.ts
import { beforeAll } from 'bun:test'
import { setupDatabase } from '@stacksjs/testing/database'

// Creates the test database where the driver needs one, then migrates it.
beforeAll(async () => {
  await setupDatabase()
})

Transaction Testing

Automatic Rollback

Use transactions to automatically rollback database changes after each test:

import { afterEach, beforeEach, describe, expect, it } from 'bun:test'
import { db } from '@stacksjs/database'
import { useTransactionalTests } from '@stacksjs/testing/database'

describe('User Model', () => {
  // Returns the two hooks, so you decide where they run. `useTransaction()`
  // below wires them for you.
  const { begin, rollback } = useTransactionalTests()

  beforeEach(begin)
  afterEach(rollback)

  it('creates a user', async () => {
    await db.insertInto('users').values({
      name: 'John Doe',
      email: 'john@example.com',
    }).execute()

    const user = await db.selectFrom('users')
      .where('email', '=', 'john@example.com')
      .selectAll()
      .executeTakeFirst()

    expect(user).toBeDefined()
    expect(user?.name).toBe('John Doe')
    // Changes are rolled back after test
  })
})

UseTransaction Helper

Stacks provides a helper that handles setup/teardown automatically:

import { describe, expect, it } from 'bun:test'
import { useTransaction } from '@stacksjs/testing/database'
import { db } from '@stacksjs/database'

describe('Order Model', () => {
  useTransaction()  // Handles beforeEach/afterEach

  it('creates an order', async () => {
    const order = await db.insertInto('orders')
      .values({ user_id: 1, total: 99.99 })
      .returning('_')
      .executeTakeFirst()

    expect(order?.total).toBe(99.99)
  })

  it('runs in isolation', async () => {
    // Previous test's order doesn't exist here
    const orders = await db.selectFrom('orders').selectAll().execute()
    expect(orders).toHaveLength(0)
  })
})

Factories

Defining Factories

Test data comes from the factory functions the model already declares, one per attribute:

// app/Models/User.ts
export default defineModel({
  name: 'User',
  attributes: {
    name: { fillable: true, factory: faker => faker.person.fullName() },
    email: { fillable: true, unique: true, factory: faker => faker.internet.email() },
    password: { fillable: true, factory: faker => faker.internet.password() },
    role: { fillable: true, default: 'user' },
    email_verified_at: { fillable: true, factory: () => null },
  },
})

There is no separate factory file and no second place to declare the same attributes. factory('User') reads that model:

import { describe, expect, it } from 'bun:test'
import { factory } from '@stacksjs/testing/database'

describe('User Posts', () => {
  it('creates user with posts', async () => {
    const user = await factory('User').create()

    const posts = await factory('Post').createMany(3, {
      user_id: user.id,
    })

    expect(posts).toHaveLength(3)
    expect(posts[0].user_id).toBe(user.id)
  })

  it('creates user without saving', async () => {
    const user = await factory('User').make({ name: 'Test User' })

    expect(user.name).toBe('Test User')
    expect(user.email).toBeDefined()
  })
})

Rows come out of the same generator buddy seed uses, which matters in three ways a hand-rolled factory tends to miss: password columns are hashed with the configured algorithm, so the row can actually be signed in as; columns marked unique are kept distinct across a batch, so createMany(50) does not collide; and belongsTo columns are filled with real parent ids.

States

A state is an override object, passed at the call site:

const admin = await factory('User').create({ role: 'admin' })
const verified = await factory('User').create({ email_verified_at: new Date() })
const verifiedAdmin = await factory('User').create({
  role: 'admin',
  email_verified_at: new Date(),
})

Overrides are applied last and taken verbatim, uniqueness handling included: a test asking for { email: 'known@example.com' } has already decided what the value is. Keys are converted to their column names, so emailVerifiedAt and email_verified_at both work.

For a state you use across many tests, name it where it belongs - in the test file, as an object:

const admin = { role: 'admin', email_verified_at: new Date() }

const one = await factory('User').create(admin)
const many = await factory('User').createMany(3, admin)

Database Assertions

assertDatabaseHas

Verify a record exists with specific attributes:

import { describe, expect, it } from 'bun:test'
import { assertDatabaseHas, assertDatabaseMissing } from '@stacksjs/testing/database'

describe('User Registration', () => {
  it('creates user in database', async () => {
    await registerUser({
      name: 'John Doe',
      email: 'john@example.com',
    })

    await assertDatabaseHas('users', {
      email: 'john@example.com',
      name: 'John Doe',
    })
  })

  it('does not create duplicate users', async () => {
    await registerUser({ email: 'existing@example.com' })

    await expect(
      registerUser({ email: 'existing@example.com' })
    ).rejects.toThrow()

    // Should only have one user with this email
    await assertDatabaseCount('users', 1, {
      email: 'existing@example.com',
    })
  })
})

assertDatabaseMissing

Verify a record does not exist:

import { assertDatabaseMissing } from '@stacksjs/testing/database'

it('deletes user from database', async () => {
  const user = await factory('User').create()

  await deleteUser(user.id)

  await assertDatabaseMissing('users', { id: user.id })
})

assertDatabaseCount

Verify the number of matching records:

import { assertDatabaseCount } from '@stacksjs/testing/database'

it('creates multiple orders', async () => {
  await createBulkOrders([
    { product_id: 1, quantity: 2 },
    { product_id: 2, quantity: 1 },
    { product_id: 1, quantity: 3 },
  ])

  await assertDatabaseCount('orders', 3)
  await assertDatabaseCount('orders', 2, { product_id: 1 })
})

assertSoftDeleted

For soft-deletable models:

import { assertSoftDeleted, assertNotSoftDeleted } from '@stacksjs/testing/database'

it('soft deletes user', async () => {
  const user = await factory('User').create()

  await softDeleteUser(user.id)

  await assertSoftDeleted('users', { id: user.id })
})

it('restores soft deleted user', async () => {
  const user = await factory('User').create()
  await softDeleteUser(user.id)

  await restoreUser(user.id)

  await assertNotSoftDeleted('users', { id: user.id })
})

Testing Queries

Testing Select Queries

import { describe, expect, it } from 'bun:test'
import { db } from '@stacksjs/database'
import { factory } from '@stacksjs/testing/database'

describe('User Queries', () => {
  it('finds users by role', async () => {
    await factory('User').create({ role: 'admin' })
    await factory('User').create()  // Regular user
    await factory('User').create({ role: 'admin' })

    const admins = await db.selectFrom('users')
      .where('role', '=', 'admin')
      .selectAll()
      .execute()

    expect(admins).toHaveLength(2)
    expect(admins.every(u => u.role === 'admin')).toBe(true)
  })

  it('orders users by created_at', async () => {
    const older = await factory('User').create({
      created_at: new Date('2024-01-01'),
    })
    const newer = await factory('User').create({
      created_at: new Date('2024-06-01'),
    })

    const users = await db.selectFrom('users')
      .orderBy('created_at', 'desc')
      .selectAll()
      .execute()

    expect(users[0].id).toBe(newer.id)
    expect(users[1].id).toBe(older.id)
  })
})

Testing Relationships

import { describe, expect, it } from 'bun:test'
import { db } from '@stacksjs/database'
import { factory } from '@stacksjs/testing/database'

describe('Post Relationships', () => {
  it('loads post with author', async () => {
    const user = await factory('User').create({ name: 'Jane Doe' })
    const post = await factory('Post').create({ user_id: user.id })

    const postWithAuthor = await db.selectFrom('posts')
      .innerJoin('users', 'users.id', 'posts.user_id')
      .where('posts.id', '=', post.id)
      .select([
        'posts.id',
        'posts.title',
        'users.name as author_name',
      ])
      .executeTakeFirst()

    expect(postWithAuthor?.author_name).toBe('Jane Doe')
  })

  it('counts post comments', async () => {
    const post = await factory('Post').create()
    await factory('Comment').createMany(5, { post_id: post.id })

    const postWithCount = await db.selectFrom('posts')
      .leftJoin('comments', 'comments.post_id', 'posts.id')
      .where('posts.id', '=', post.id)
      .groupBy('posts.id')
      .select([
        'posts.id',
        'posts.title',
        db.fn.count('comments.id').as('comment_count'),
      ])
      .executeTakeFirst()

    expect(Number(postWithCount?.comment_count)).toBe(5)
  })
})

Testing Transactions

import { describe, expect, it } from 'bun:test'
import { db } from '@stacksjs/database'
import { factory } from '@stacksjs/testing/database'

describe('Money Transfer', () => {
  it('transfers money atomically', async () => {
    const sender = await factory('Account').create({ balance: 100 })
    const receiver = await factory('Account').create({ balance: 50 })

    await transferMoney(sender.id, receiver.id, 30)

    const updatedSender = await db.selectFrom('accounts')
      .where('id', '=', sender.id)
      .select('balance')
      .executeTakeFirst()

    const updatedReceiver = await db.selectFrom('accounts')
      .where('id', '=', receiver.id)
      .select('balance')
      .executeTakeFirst()

    expect(updatedSender?.balance).toBe(70)
    expect(updatedReceiver?.balance).toBe(80)
  })

  it('rolls back on failure', async () => {
    const sender = await factory('Account').create({ balance: 100 })
    const receiver = await factory('Account').create({ balance: 50 })

    // Try to transfer more than available
    await expect(
      transferMoney(sender.id, receiver.id, 150)
    ).rejects.toThrow('Insufficient funds')

    // Balances should be unchanged
    const updatedSender = await db.selectFrom('accounts')
      .where('id', '=', sender.id)
      .select('balance')
      .executeTakeFirst()

    expect(updatedSender?.balance).toBe(100)  // Unchanged
  })
})

Seeding Test Data

Using Seeders

// tests/seeders/TestSeeder.ts
import { Seeder } from '@stacksjs/database'
import { factory } from '@stacksjs/testing/database'

export class TestSeeder extends Seeder {
  async run() {
    // Create admin user
    const admin = await factory('User').create({
      role: 'admin',
      email: 'admin@example.com',
    })

    // Create regular users with posts
    const users = await factory('User').createMany(5)

    for (const user of users) {
      await factory('Post').createMany(3, { user_id: user.id })
    }
  }
}

Running Seeders in Tests

import { beforeAll, describe, it } from 'bun:test'
import { TestSeeder } from '../seeders/TestSeeder'

describe('Dashboard', () => {
  beforeAll(async () => {
    await new TestSeeder().run()
  })

  it('displays user statistics', async () => {
    const stats = await getDashboardStats()

    expect(stats.userCount).toBe(6)  // 5 users + 1 admin
    expect(stats.postCount).toBe(15) // 5 users * 3 posts
  })
})

Testing Migrations

Migrations are derived from models, so a migration test is really a schema test: run them, then assert on the tables they produced.

import { describe, expect, it } from 'bun:test'
import { db, runDatabaseMigration } from '@stacksjs/database'

describe('Migrations', () => {
  it('creates the users table with the columns the model declares', async () => {
    await runDatabaseMigration()

    const tables = await db.introspection.getTables()
    const users = tables.find(table => table.name === 'users')

    expect(users).toBeDefined()
    for (const column of ['id', 'email', 'name', 'created_at'])
      expect(users?.columns.some(c => c.name === column)).toBe(true)
  })
})

There is no rollback(). Migrations run forward from the models, so the way back is buddy migrate:fresh, which drops everything and re-migrates - which is also why a migration test wants its own throwaway database rather than the development one.

Running Database Tests

# Run all tests (uses test database)
NODE_ENV=test buddy test

# Run with fresh database
buddy test --fresh-db

# Run specific test file
bun test tests/Feature/UserTest.ts

# Run with verbose SQL logging
DEBUG=sql buddy test

Best Practices

DO

Use transactions - Rollback after each test for isolation
Use factories - Generate realistic test data
Test edge cases - NULL values, empty strings, boundaries
Test constraints - Unique, foreign keys, check constraints
Clean up properly - Don't leave test data around

DON'T

Don't share database state - Each test should be independent
Don't test ORM internals - Test your application logic
Don't use production data - Use factories with fake data
Don't skip migrations - Test with the same schema as production

Testing Overview - Getting started with testing
Unit Tests - Testing isolated functions
Database Guide - Database queries and models
Migrations - Database schema management