> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/stormkit-io/stormkit-io/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

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

<Note>
  The Database feature is currently available only for self-hosted Stormkit instances.
</Note>

## 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:

<Steps>
  <Step title="Creates an isolated schema">
    A dedicated PostgreSQL schema (e.g., `a123e456`) for your environment
  </Step>

  <Step title="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
  </Step>

  <Step title="Injects environment variables">
    Connection details are automatically available in your application
  </Step>

  <Step title="Runs migrations (optional)">
    Executes SQL migrations from your repository during deployment
  </Step>
</Steps>

<Frame>
  <img src="https://mintlify.s3.us-west-1.amazonaws.com/stormkit-io-stormkit-io/assets/docs/features/demo-database-attach.png" alt="Attach database to environment" />
</Frame>

## 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.

<Frame>
  <img src="https://mintlify.s3.us-west-1.amazonaws.com/stormkit-io-stormkit-io/assets/docs/features/demo-database-configure.png" alt="Configure database migrations" />
</Frame>

### Why SQL-Based Migrations?

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

<CardGroup cols={2}>
  <Card title="Fast Iteration" icon="bolt">
    Save a migration file and see database changes applied in milliseconds during deployment.
  </Card>

  <Card title="Roll-Forward Only" icon="arrow-right">
    No rollback complexity to maintain. If something breaks, fix it forward with a new migration.
  </Card>

  <Card title="No Learning Curve" icon="graduation-cap">
    Write plain PostgreSQL syntax, no custom DSL or ORM to learn.
  </Card>

  <Card title="Full PostgreSQL Power" icon="database">
    Direct SQL execution means access to all PostgreSQL features: triggers, functions, custom types, extensions, and more.
  </Card>
</CardGroup>

### Configuration

<Steps>
  <Step title="Enable migrations">
    Toggle **Enable schema migrations** in the database configuration
  </Step>

  <Step title="Set migrations path">
    Set the **Migrations path** (e.g., `/migrations`, `/db/migrations`)
  </Step>
</Steps>

### Migration Files

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

```bash theme={null}
/migrations
  ├── 001_create_users.sql
  ├── 002_add_posts.sql
  └── 003_add_comments.sql
```

<Frame>
  <img src="https://mintlify.s3.us-west-1.amazonaws.com/stormkit-io-stormkit-io/assets/docs/features/demo-database-migrations.png" alt="Preview migrations" />
</Frame>

<Warning>
  **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
</Warning>

### Example Migration File

```sql migrations/001_create_users.sql theme={null}
-- 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:

```bash theme={null}
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:

```typescript theme={null}
// 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:

<Steps>
  <Step title="Navigate to Database section">
    Go to your environment's Database page
  </Step>

  <Step title="Click Delete">
    Click the **Delete** button
  </Step>

  <Step title="Confirm deletion">
    Confirm the deletion in the dialog
  </Step>
</Steps>

<Warning>
  **This action:**

  * Drops the schema and **all data** permanently
  * Removes both migration and app users
  * Terminates active database connections
  * Cannot be undone
</Warning>

## 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

<CardGroup cols={2}>
  <Card title="Connection Pooling" icon="share-nodes">
    Use connection pooling in production to manage database connections efficiently.
  </Card>

  <Card title="Index Optimization" icon="magnifying-glass">
    Add indexes for frequently queried columns to improve performance.
  </Card>

  <Card title="Backup Strategy" icon="floppy-disk">
    Implement regular backups for production databases.
  </Card>

  <Card title="Monitor Queries" icon="chart-line">
    Monitor slow queries and optimize them to stay within timeout limits.
  </Card>
</CardGroup>

## 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

<AccordionGroup>
  <Accordion title="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
  </Accordion>

  <Accordion title="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
  </Accordion>

  <Accordion title="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
  </Accordion>
</AccordionGroup>