Database

Learn how to attach a PostgreSQL schema to your environment and run automatic migrations on deployment.

The Database feature is currently available only for self-hosted Stormkit instances.

Overview

Stormkit’s Database feature provides each environment with an isolated PostgreSQL schema, complete with automatic schema migrations and secure credential management. This allows you to develop and deploy database-backed applications with confidence.

How It Works

When you attach a database to an environment, Stormkit:

1. Creates an isolated schema

   A dedicated PostgreSQL schema (e.g., a123e456) for your environment

2. Generates secure credentials

   Two separate database users with different permission levels:

   • Migration user - Has DDL permissions (CREATE, ALTER, DROP tables) with strict resource limits
   • App user - Has DML permissions only (SELECT, INSERT, UPDATE, DELETE) for runtime operations
3. Injects environment variables

   Connection details are automatically available in your application

4. Runs migrations (optional)

   Executes SQL migrations from your repository during deployment

Attaching a Database

Navigate to your environment’s Database section and click Attach Database.

Automatic Migrations

Enable schema migrations to automatically apply SQL migration files during deployment.

Why SQL-Based Migrations?

Stormkit’s migration system is designed for simplicity and power:

Fast Iteration

Save a migration file and see database changes applied in milliseconds during deployment.

Roll-Forward Only

No rollback complexity to maintain. If something breaks, fix it forward with a new migration.

No Learning Curve

Write plain PostgreSQL syntax, no custom DSL or ORM to learn.

Full PostgreSQL Power

Direct SQL execution means access to all PostgreSQL features: triggers, functions, custom types, extensions, and more.

Configuration

1. Enable migrations

   Toggle Enable schema migrations in the database configuration

2. Set migrations path

   Set the Migrations path (e.g., /migrations, /db/migrations)

Migration Files

Place your SQL migration files in the configured path and deploy your application:

/migrations
  ├── 001_create_users.sql
  ├── 002_add_posts.sql
  └── 003_add_comments.sql

Important:

• Files are executed in alphabetical order - use numeric prefixes (001, 002, etc.)
• Each file is executed once per deployment
• Failed migrations abort the deployment
• If the content of a previously executed file changes, it is re-executed
• The migrations are executed only when environment’s default branch is updated

Example Migration File

migrations/001_create_users.sql

-- migrations/001_create_users.sql
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

Environment Variables

The following environment variables are automatically injected into your application:

DATABASE_URL=postgresql://user_name:secure_password@db_host:5432/db?options=-csearch_path=my_schema
POSTGRES_USER=user_name
POSTGRES_PASSWORD=secure_password
POSTGRES_HOST=db_host
POSTGRES_PORT=5432
POSTGRES_DB=db
POSTGRES_SCHEMA=my_schema

You can use these in your application:

// Next.js, Remix, etc.
const db = new Client({
  connectionString: process.env.DATABASE_URL,
});

Security & Permissions

Migration User

Used only during deployments with resource limits:

Configuration Option	Value
statement_timeout	30s
lock_timeout	10s
temp_file_limit	100MB
work_mem	4MB
idle_in_transaction_session_timeout	60s
connection limit	1

Can do: CREATE/ALTER/DROP tables, indexes, and sequences within the schema

Cannot do: Access other schemas, create databases, modify roles, or access the file system

App User

Used by your running application with runtime limits:

Configuration Option	Value
statement_timeout	15s
lock_timeout	5s
temp_file_limit	100MB
work_mem	8MB
idle_in_transaction_session_timeout	60s
connection limit	10

Can do: SELECT, INSERT, UPDATE, DELETE on tables and sequences

Cannot do: ALTER/DROP tables, CREATE tables, or access other schemas

Deleting a Schema

To delete a schema:

1. Navigate to Database section

   Go to your environment’s Database page

2. Click Delete

   Click the Delete button

3. Confirm deletion

   Confirm the deletion in the dialog

This action:

• Drops the schema and all data permanently
• Removes both migration and app users
• Terminates active database connections
• Cannot be undone

Best Practices

Migration Files

• Use numeric prefixes or timestamps for ordering: 001_, 002_, 003_
• Make migrations idempotent when possible: Use IF NOT EXISTS, IF EXISTS
• Keep migrations small and focused on one change
• Test migrations locally before deploying
• Never modify existing migrations - create new ones to fix issues

Database Management

Connection Pooling

Use connection pooling in production to manage database connections efficiently.

Index Optimization

Add indexes for frequently queried columns to improve performance.

Backup Strategy

Implement regular backups for production databases.

Monitor Queries

Monitor slow queries and optimize them to stay within timeout limits.

Limitations

• PostgreSQL only - Other databases are not supported
• Single schema per environment - Each environment gets one schema
• Migration rollback - Rollbacks must be handled with new migration files
• Timeout limits - Queries must complete within configured timeout limits

Troubleshooting

Migration fails during deployment

• Check migration file syntax for PostgreSQL compatibility
• Ensure migration files are named with proper numeric prefixes
• Verify the migration doesn’t exceed timeout limits (30s)
• Check deployment logs for specific error messages

Cannot connect to database from application

• Verify DATABASE_URL environment variable is set
• Check that the app user has appropriate permissions
• Ensure the schema name is correct
• Verify network connectivity to the database

Query timeout errors

• Optimize slow queries with indexes
• Reduce query complexity
• Check if queries exceed the 15s statement timeout
• Consider breaking large operations into smaller chunks