Test Database - 🌲 Sonamu

Learn about Sonamu’s test database system and how to configure it.

Test DB Overview

Isolated Environment

Separate from productionSafe testing

Auto Rollback

Transaction-basedAuto cleanup after tests

Fixture Support

Copy real dataConsistent test environment

Easy Initialization

Single command setupAuto schema copy

DB Structure

Sonamu uses multiple databases: Roles:

development_master: DB used during development
fixture: Stores common data for testing (team shareable)
test: DB where actual tests run (transaction-based)

DB Configuration

sonamu.config.json

{
  "db": {
    "development_master": {
      "client": "pg",
      "connection": {
        "host": "localhost",
        "port": 5432,
        "user": "postgres",
        "password": "postgres",
        "database": "myapp_dev"
      }
    },
    "fixture": {
      "client": "pg",
      "connection": {
        "host": "fixture-db.example.com",
        "port": 5432,
        "user": "postgres",
        "password": "postgres",
        "database": "myapp_fixture"
      }
    },
    "test": {
      "client": "pg",
      "connection": {
        "host": "localhost",
        "port": 5432,
        "user": "postgres",
        "password": "postgres",
        "database": "myapp_test"
      }
    }
  }
}

Warning:

test and production_master must never use the same DB
Sonamu automatically validates this during initialization

// sonamu/src/testing/fixture-manager.ts
if (Sonamu.dbConfig.test && Sonamu.dbConfig.production_master) {
  const tConn = Sonamu.dbConfig.test.connection;
  const pConn = Sonamu.dbConfig.production_master.connection;
  
  if (`${tConn.host}:${tConn.port}/${tConn.database}` ===
      `${pConn.host}:${pConn.port}/${pConn.database}`) {
    throw new Error('Test DB and Production DB use the same database.');
  }
}

Fixture Initialization

pnpm sonamu fixture init

Copies the development DB schema to Fixture DB and Test DB.

pnpm sonamu fixture init

Process:

Development DB Dump

# Dump schema only (no data)
pg_dump -d myapp_dev -s > /tmp/schema.sql

# Dump migration records
pg_dump myapp_dev knex_migrations knex_migrations_lock > /tmp/migrations.sql

Create Fixture DB

# Create DB
psql -c "DROP DATABASE IF EXISTS myapp_fixture"
psql -c "CREATE DATABASE myapp_fixture"

# Restore schema
psql myapp_fixture < /tmp/schema.sql
psql myapp_fixture < /tmp/migrations.sql

Create Test DB

# Create DB
psql -c "DROP DATABASE IF EXISTS myapp_test"
psql -c "CREATE DATABASE myapp_test"

# Restore schema
psql myapp_test < /tmp/schema.sql
psql myapp_test < /tmp/migrations.sql

Result:

Fixture DB: Empty schema (add data later)
Test DB: Empty schema (sync from Fixture per test)

Execution Example

$ pnpm sonamu fixture init

DUMP...
SYNC to (REMOTE) Fixture DB...
DROP DATABASE IF EXISTS `myapp_fixture`
CREATE DATABASE `myapp_fixture`
# Schema restore...

SYNC to (LOCAL) Testing DB...
DROP DATABASE IF EXISTS `myapp_test`
CREATE DATABASE `myapp_test`
# Schema restore...

✅ Fixture initialization complete!

Skip Condition

Skips Test DB creation if Fixture DB and Test DB are the same:

// CLI code
const toSkip = (() => {
  const remoteConn = Sonamu.dbConfig.fixture.connection;
  const localConn = Sonamu.dbConfig.test.connection;
  return remoteConn.host === localConn.host && 
         remoteConn.database === localConn.database;
})();

$ pnpm sonamu fixture init

SYNC to (REMOTE) Fixture DB...
✅ Complete

SYNC to (LOCAL) Testing DB...
⚠️  Skipped! (Same as Fixture DB)

Test DB Operation

Transaction-Based

Each test runs in an isolated Transaction:

// sonamu/src/testing/bootstrap.ts
export function bootstrap(vi: VitestUtils) {
  beforeEach(async () => {
    // Before each test: Start transaction
    await DB.createTestTransaction();
  });
  
  afterEach(async () => {
    // After each test: Auto rollback
    await DB.clearTestTransaction();
  });
}

Flow:

// Test 1 starts
BEGIN TRANSACTION;
  INSERT INTO users (...);  // Create test data
  SELECT * FROM users;      // Test verification
ROLLBACK;  // Auto rollback - data disappears

// Test 2 starts
BEGIN TRANSACTION;
  // Starts with clean DB state
  INSERT INTO posts (...);
ROLLBACK;

Benefits

Isolation:

test("create user", async () => {
  const userModel = new UserModel();
  await userModel.create({ username: "john" });
  // Auto deleted after test
});

test("next test has clean DB", async () => {
  const userModel = new UserModel();
  const { users } = await userModel.getUsers();
  expect(users).toHaveLength(0);  // john from previous test is gone
});

Fast execution:

No actual DELETE → just ROLLBACK
Saves DB cleanup time

Best Practices

1. DB Separation

// ✅ Correct: Separate DBs
{
  "development_master": { "database": "myapp_dev" },
  "fixture": { "database": "myapp_fixture" },
  "test": { "database": "myapp_test" }
}

// ❌ Wrong: Same DB
{
  "development_master": { "database": "myapp" },
  "test": { "database": "myapp" }  // Dangerous!
}

2. Shared Fixture DB

Team can maintain consistent test environment by using the same Fixture DB:

{
  "fixture": {
    "client": "pg",
    "connection": {
      "host": "fixture-db.company.com",  // Team shared server
      "database": "myapp_fixture"
    }
  },
  "test": {
    "client": "pg",
    "connection": {
      "host": "localhost",  // Local
      "database": "myapp_test"
    }
  }
}

3. Regular Initialization

Re-initialize Fixture when schema changes:

# After running migrations
pnpm sonamu migrate run

# Initialize Fixture
pnpm sonamu fixture init

Troubleshooting

Connection Failed

Error: connect ECONNREFUSED 127.0.0.1:5432

Solution:

Check if PostgreSQL server is running
Verify connection info in sonamu.config.json

Permission Error

Error: permission denied for database myapp_test

Solution:

-- Grant permissions to DB user
GRANT ALL PRIVILEGES ON DATABASE myapp_test TO postgres;

DB Already Exists

⚠️  Database "myapp_test" Already exists

Solution:

# Manually drop to force recreate
psql -c "DROP DATABASE myapp_test"
pnpm sonamu fixture init

Cautions

Cautions when using test DB:

Never use production DB: test DB must be completely separate from production
Schema sync: Run fixture init after migrations
Transaction-based: Isolation between tests is handled automatically
Shared Fixture: Recommend using same Fixture DB with team
Regular initialization: Re-initialize when dev DB schema changes

Next Steps

Creating Fixtures

Writing fixture.ts

Loading Fixtures

Import production data

Syncing Fixtures

Fixture → Test DB

Writing Tests

Understanding test structure

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

​Test DB Overview

Isolated Environment

Auto Rollback

Fixture Support

Easy Initialization

​DB Structure

​DB Configuration

​sonamu.config.json

​Fixture Initialization

​pnpm sonamu fixture init

​Execution Example

​Skip Condition

​Test DB Operation

​Transaction-Based

​Benefits

​Best Practices

​1. DB Separation

​2. Shared Fixture DB

​3. Regular Initialization

​Troubleshooting

​Connection Failed

​Permission Error

​DB Already Exists

​Cautions

​Next Steps

Creating Fixtures

Loading Fixtures

Syncing Fixtures

Writing Tests

Test DB Overview

DB Structure

DB Configuration

sonamu.config.json

Fixture Initialization

pnpm sonamu fixture init

Execution Example

Skip Condition

Test DB Operation

Transaction-Based

Benefits

Best Practices

1. DB Separation

2. Shared Fixture DB

3. Regular Initialization

Troubleshooting

Connection Failed

Permission Error

DB Already Exists

Cautions

Next Steps