Database

Knex-based SQL database with migrations, seeds, transactions, and schema introspection

Access

Database access uses the db property on ctx — a full Knex instance with read-write access:

export const GET = {
  handler: async (ctx) => {
    const { db } = ctx;
    const users = await db('users').select('*');
    return { data: users };
  },
};

// Or destructure in the parameter list:
export const GET = {
  handler: async ({ db, req }) => {
    const user = await db('users').where('id', req.query.id).first();
    return { data: user };
  },
};

Queries

ctx.db is a standard Knex instance. All Knex query builder methods are available:

// Select with conditions
const activeUsers = await ctx.db('users')
  .where('active', true)
  .orderBy('created_at', 'desc')
  .limit(20);

// Join
const posts = await ctx.db('posts')
  .join('users', 'posts.author_id', 'users.id')
  .select('posts.*', 'users.name as author_name')
  .where('posts.published', true);

// Insert and get ID
const [id] = await ctx.db('users').insert({
  name: 'Alice',
  email: 'alice@example.com',
});

// Update
const count = await ctx.db('users')
  .where('id', id)
  .update({ name: 'Alice Smith' });

// Delete
await ctx.db('users').where('id', id).delete();

// Upsert (insert or update on conflict)
await ctx.db('users')
  .insert({ email: 'alice@example.com', name: 'Alice' })
  .onConflict('email')
  .merge();

// Raw SQL
const result = await ctx.db.raw('SELECT COUNT(*) as total FROM users WHERE active = ?', [true]);

// Aggregates
const { count } = await ctx.db('users').where('active', true).count('* as count').first();

Transactions

Use transactions when multiple queries must succeed or fail together:

export const POST = {
  handler: async (ctx) => {
    const { db } = ctx;
    const result = await db.transaction(async (trx) => {
      const [userId] = await trx('users').insert({
        name: ctx.req.body.name,
        email: ctx.req.body.email,
      });

      await trx('profiles').insert({
        user_id: userId,
        bio: '',
      });

      await trx('billing_accounts').insert({
        user_id: userId,
        plan: 'free',
      });

      return userId;
    });
    // If any query fails, all are rolled back

    return { status: 201, data: { id: result } };
  },
};

Migrations

Place Knex migration files in migrations/:

// migrations/20260101000000-create-users.js
export async function up(knex) {
  await knex.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('name').notNullable();
    table.string('email').notNullable().unique();
    table.boolean('active').defaultTo(true);
    table.timestamps(true, true);
  });
}

export async function down(knex) {
  await knex.schema.dropTableIfExists('users');
}

Use timestamp-based filenames (YYYYMMDDHHMM-description.js) to ensure correct ordering. Migrations run automatically during boot (both arcway dev and arcway start).

Common schema operations:

export async function up(knex) {
  // Add columns to existing table
  await knex.schema.alterTable('users', (table) => {
    table.string('avatar_url');
    table.integer('login_count').defaultTo(0);
    table.index('email'); // Add index
  });

  // Create a table with foreign keys
  await knex.schema.createTable('posts', (table) => {
    table.increments('id').primary();
    table.integer('author_id').unsigned().notNullable()
      .references('id').inTable('users').onDelete('CASCADE');
    table.string('title').notNullable();
    table.text('body');
    table.boolean('published').defaultTo(false);
    table.timestamps(true, true);
  });
}

Schema Introspection

Arcway can introspect the current database schema:

const schema = await ctx.db.introspectSchema();
// Returns table definitions with columns, types, and constraints

const markdown = ctx.db.generateSchemaMarkdown(schema);
// Generates markdown documentation of the schema

SQLite Optimizations

When using better-sqlite3, Arcway automatically applies these PRAGMAs after each connection:

PRAGMA	Value	Effect
`journal_mode`	`WAL`	Write-Ahead Logging — allows concurrent reads alongside a single writer, eliminates exclusive locks
`busy_timeout`	`5000`	Waits up to 5 seconds before returning a "database is locked" error, instead of failing immediately
`synchronous`	`NORMAL`	Flushes at safe checkpoints rather than every write — significantly faster on high-latency storage (NAS, RAID) with minimal durability trade-off

These settings are applied unconditionally to all SQLite connections and are not configurable. They make SQLite safe and performant for concurrent API request handling. If you need PostgreSQL-level concurrency, switch to pg.

If you want SQLite to enforce foreign keys and ON DELETE CASCADE, enable:

database: {
  client: 'better-sqlite3',
  connection: { filename: '.build/db.sqlite3' },
  sqlite: {
    foreignKeys: true,
  },
}

Arcway will then run:

PRAGMA foreign_keys = ON;

Supported Databases

Configure in arcway.config.js:

export default {
  database: {
    client: 'sqlite3',                    // Default
    connection: { filename: '.build/db.sqlite3' },
    sqlite: {
      useNullAsDefault: true,             // SQLite only. Default: true
      foreignKeys: false,                 // SQLite only. Enables FK enforcement
    },
  },
  // Or PostgreSQL:
  database: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
  },
  // Or MySQL:
  database: {
    client: 'mysql2',
    connection: {
      host: 'localhost',
      user: 'root',
      password: '',
      database: 'myapp',
    },
  },
};