Database

VentureKit provides database support through @venturekit/data with RDS configuration, a built-in pure-SQL migration runner, query utilities, and transaction management.

Setup

1. Install the Package

npm install @venturekit/data@dev

2. Declare a Database Intent

Add a database definition directly on defineVenture() in vk.config.ts:

import { defineVenture } from '@venturekit/infra';
import { base } from './config/base';
import { security } from './config/security';
import { dev } from './config/dev';
import { prod } from './config/prod';

export default defineVenture({
  base,
  security,
  databases: [
    { id: 'main', type: 'postgres', name: 'app' },
  ],
  envs: { dev, prod },
});

Then override sizing per environment in config/prod.ts:

import type { EnvConfigInput } from '@venturekit/core';

export const prod: EnvConfigInput = {
  preset: 'medium',
  databases: [
    { id: 'main', size: 'large', highAvailability: true, backups: true, encrypted: true },
  ],
};

VentureKit auto-prefixes resource names with {project}-{stage} at deploy time, so you declare the logical name once.

3. Write a Migration

Drop a .sql file into db/migrations/. Files are applied in lexical order, one per transaction — use a numeric prefix so the order is unambiguous:

-- db/migrations/0001_init.sql
CREATE TABLE tasks (
  id          SERIAL PRIMARY KEY,
  title       TEXT NOT NULL,
  completed   BOOLEAN NOT NULL DEFAULT FALSE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The runner records each applied file in the __vk_migrations tracking table along with its content hash, so once a migration ships you write a follow-up file rather than editing the original.

4. Run Migrations

# Apply pending migrations from db/migrations/
vk migrate

# Apply migrations and seeds (db/seeds/)
vk migrate --seed

# Show what's applied vs. pending (read-only)
vk migrate status

Querying

Simple Queries

import { query, mapResults } from '@venturekit/data';

const result = await query('SELECT * FROM tasks WHERE completed = $1', [false]);

const tasks = mapResults(result, row => ({
  id: row.id,
  title: row.title,
  completed: row.completed,
}));

Connection Pool

import { getPool } from '@venturekit/data';

const pool = getPool();
const client = await pool.connect();
try {
  const result = await client.query('SELECT NOW()');
  return result.rows[0];
} finally {
  client.release();
}

Transactions

Transactions ensure that multiple database operations either all succeed or all fail. VentureKit offers three approaches, from simplest to most flexible.

1. Transactional Handlers (Recommended)

Set transactional: true on your handler config. VentureKit manages the full lifecycle:

Opens a transaction before your handler runs
Auto-commits on success
Auto-rolls back on any error

import { handler } from '@venturekit/runtime';

export const main = handler(async (body, ctx, logger) => {
  // ctx.tx is a transaction — all queries run in the same transaction
  await ctx.tx!.query('INSERT INTO tasks (title) VALUES ($1)', [body.title]);
  await ctx.tx!.query('INSERT INTO audit_log (action, details) VALUES ($1, $2)', [
    'task_created',
    JSON.stringify({ title: body.title }),
  ]);
  // If either query fails, both are rolled back
  return { created: true };
}, { scopes: ['tasks.write'], transactional: true });

This also works with taskHandler for queue consumers and cron handlers:

import { taskHandler } from '@venturekit/runtime';

export const main = taskHandler<OrderEvent>(
  async (event, ctx, logger) => {
    await ctx.tx!.query('INSERT INTO orders ...', [event.orderId]);
    await ctx.tx!.query('UPDATE inventory SET quantity = quantity - $1 ...', [event.quantity]);
    return { processed: true };
  },
  { transactional: true },
);

2. Scoped Transactions (`withTransaction`)

Use withTransaction when you need a transaction outside of a handler, or want a transaction for a subset of operations:

import { withTransaction } from '@venturekit/data';

const result = await withTransaction(async (tx) => {
  await tx.query('INSERT INTO tasks (title) VALUES ($1)', ['New task']);
  await tx.query('UPDATE counters SET count = count + 1 WHERE name = $1', ['tasks']);
  return { created: true };
});
// Auto-committed on success, rolled back on error

3. Manual Transactions

For full control over commit and rollback:

import { beginTransaction } from '@venturekit/data';

const tx = await beginTransaction();
try {
  await tx.query('INSERT INTO tasks (title) VALUES ($1)', ['Task 1']);
  await tx.query('INSERT INTO tasks (title) VALUES ($1)', ['Task 2']);
  await tx.commit();
} catch (error) {
  await tx.rollback();
  throw error;
}

Transaction Isolation

By default, PostgreSQL uses READ COMMITTED isolation. For stricter isolation:

const tx = await beginTransaction();
await tx.query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
// ... your queries
await tx.commit();

Database Sizes

Size	Instance	Use Case
`small`	db.t3.small	Development, low traffic
`medium`	db.r6g.large	Moderate traffic
`large`	db.r6g.xlarge	High traffic
`xlarge`	db.r6g.2xlarge	Enterprise

Local Development

During vk dev, VentureKit starts a shared PostgreSQL Docker container automatically at ~/.vk/. All projects share the same Postgres instance on port 5432 — databases are isolated by project-prefixed names (e.g. my_app_main).

Environment variables are injected automatically:

Variable	Example
`DATABASE_URL`	`postgresql://postgres:postgres@localhost:5432/my_app_main`

Use vk open db to open a web-based database viewer (pgweb) connected to the current project’s database.

Migration Commands

Command	Description
`vk migrate`	Apply pending `.sql` files from `db/migrations/`
`vk migrate --seed`	Apply pending migrations, then seeds from `db/seeds/`
`vk migrate --env <env>`	Run with `VENTURE_STAGE` set (e.g. `prod`)
`vk migrate status`	Show applied vs. pending migrations and seeds

Infrastructure Intents — declaring databases
Queues — transactional queue consumers
Data Safety — deletion protection and backups