Migrate to Database Storage

This tutorial walks you through migrating an existing Blocks project from YAML-only configuration to database-backed storage. You'll learn how to set up a database, migrate your existing config, and adopt a team collaboration workflow.

Prerequisites

Existing Blocks project with blocks.yml
Node.js 20+
PostgreSQL server (or use SQLite for simpler setup)
20 minutes

What You'll Achieve

By the end of this tutorial:

Your configuration will be stored in a database
Team members can access shared configuration
You can manage blocks programmatically via API
Local YAML file becomes a minimal override layer

Step 1: Install Dependencies

Install the store package:

pnpm add @blocksai/store

For PostgreSQL support, also install:

pnpm add pg

For SQLite (simpler option), no additional dependencies needed.

Step 2: Choose Your Database

Option A: SQLite (Recommended for Getting Started)

SQLite is file-based and requires zero configuration:

# Initialize a local database
blocks store init sqlite:///blocks.db

This creates blocks.db in your project root.

Option B: PostgreSQL (Recommended for Teams)

First, create a PostgreSQL database:

# Using PostgreSQL CLI
createdb blocks-shared

# Or via SQL client
# CREATE DATABASE blocks_shared;

Then initialize the schema:

blocks store init postgres://username:password@localhost:5432/blocks-shared

Store the database URL in an environment variable for security:

export BLOCKS_DB_URL="postgres://username:password@localhost:5432/blocks-shared"
blocks store init $BLOCKS_DB_URL

Step 3: Push Existing Configuration

Upload your current blocks.yml to the database:

For SQLite

blocks store push sqlite:///blocks.db

For PostgreSQL

blocks store push postgres://username:password@localhost:5432/blocks-shared

# Or with environment variable
blocks store push $BLOCKS_DB_URL

This uploads:

Project metadata
Philosophy statements
All domain definitions (entities, semantics, signals, measures)
All block definitions
Validator and pipeline configuration
AI and cache settings

Verify the upload:

# Pull from database and display
blocks store pull sqlite:///blocks.db

# Or save to a file to review
blocks store pull sqlite:///blocks.db --output db-config.yml

Step 4: Update blocks.yml with Sources

Now configure your blocks.yml to load from the database:

blocks.yml

# Add sources at the top
sources:
  - type: database
    url: sqlite:///blocks.db
    mode: pull

# Keep minimal local overrides
project:
  name: "my-project"
  domain: "myproject.local"

# You can remove blocks, entities, etc. since they're in the database
# Or keep them here as local overrides

For teams using PostgreSQL:

blocks.yml

sources:
  - type: database
    url: postgres://username:password@db.company.com:5432/blocks-shared
    mode: pull

project:
  name: "my-project"
  domain: "myproject.local"

If you keep block definitions in both the database and local YAML, the local YAML values will override the database values. This is useful for local development but can be confusing. Consider moving all shared blocks to the database and keeping only local customizations in YAML.

Step 5: Test the Migration

Verify everything works:

blocks run --all

This should:

Load configuration from the database
Merge with your local blocks.yml
Run validation on all blocks

If you see errors, check:

Database connection (is the database running?)
URL format (correct username, password, host, port?)
Source configuration (correct type and mode?)

Verify specific blocks:

# Check a specific block still works
blocks run user_validator

# Inspect loaded configuration (add --verbose flag if available)
blocks config show

Step 6: Clean Up Local YAML (Optional)

Once you've verified the migration works, you can simplify your local blocks.yml:

Before (all config in YAML)

blocks.yml

project:
  name: "user-service"
  domain: "users.api"

philosophy:
  - "All user data must be validated"
  - "Privacy is paramount"

domain:
  entities:
    user:
      fields: [id, email, name]
    # ... many more entities

blocks:
  user_validator:
    description: "Validates user input"
    # ... many more blocks

validators:
  - schema
  - shape
  - domain

# ... more configuration

After (minimal local YAML)

blocks.yml

sources:
  - type: database
    url: sqlite:///blocks.db
    mode: pull

# Only local overrides
project:
  name: "user-service-dev"

Everything else is in the database!

Step 7: Team Collaboration Setup

If you're working with a team, set up shared database access:

Database admin creates and initializes:

createdb blocks-team
blocks store init postgres://team:password@db.company.com/blocks-team
blocks store push postgres://team:password@db.company.com/blocks-team

Team members update their blocks.yml:

blocks.yml

sources:
  - type: database
    url: postgres://team:password@db.company.com/blocks-team
    mode: pull

# Each person can have local customizations
project:
  name: "my-local-name"

Team members run blocks normally:

blocks run --all

They automatically get the latest shared configuration from the database.

Making Changes to Shared Config

Option 1: Pull, Edit, Push (Recommended)

# Pull current config
blocks store pull $BLOCKS_DB_URL > shared.yml

# Edit shared.yml
# ... make changes ...

# Push back
blocks store push $BLOCKS_DB_URL --file shared.yml

Option 2: Programmatic Updates

import { BlocksStore } from '@blocksai/store';

const store = new BlocksStore(process.env.BLOCKS_DB_URL);
await store.initialize();

// Add new block
await store.putBlock('new_validator', {
  description: 'New validation block',
  inputs: [{ name: 'data', type: 'string' }],
  outputs: [{ name: 'valid', type: 'boolean' }],
});

// Update entity
await store.putEntity('user', {
  fields: ['id', 'email', 'name', 'verified'],
});

await store.close();

Rollback Procedure

If you need to rollback to YAML-only configuration:

Export current database config:

blocks store pull sqlite:///blocks.db --output blocks-backup.yml

Remove sources from blocks.yml:

blocks.yml

# Remove or comment out sources
# sources:
#   - type: database
#     url: sqlite:///blocks.db

# Keep or restore full configuration here
project:
  name: "my-project"

blocks:
  # ... all blocks

Verify it works:

blocks run --all

Keep database backup for potential future use.

PostgreSQL Production Setup

For production deployments, follow these best practices:

Secure Connection String

Store credentials securely:

# In .env file (DO NOT COMMIT)
BLOCKS_DB_URL="postgres://user:password@db.company.com:5432/blocks"

# In blocks.yml
sources:
  - type: database
    url: ${BLOCKS_DB_URL}
    mode: pull

Read-Only Access for Most Users

Create separate database users:

-- Read-write user (for admins)
CREATE USER blocks_admin WITH PASSWORD 'secure-password';
GRANT ALL PRIVILEGES ON DATABASE blocks TO blocks_admin;

-- Read-only user (for developers)
CREATE USER blocks_readonly WITH PASSWORD 'readonly-password';
GRANT CONNECT ON DATABASE blocks TO blocks_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO blocks_readonly;

Team members use read-only connection:

blocks.yml

sources:
  - type: database
    url: postgres://blocks_readonly:readonly-password@db.company.com/blocks
    mode: pull  # Only pull, cannot push

Backup Strategy

# Daily backup cron job
0 2 * * * pg_dump blocks > /backups/blocks-$(date +\%Y\%m\%d).sql

# Or export to YAML
0 2 * * * blocks store pull $BLOCKS_DB_URL > /backups/blocks-$(date +\%Y\%m\%d).yml

Advanced: Multi-Environment Setup

Use different databases for different environments:

blocks.yml

sources:
  # Base configuration
  - type: database
    url: postgres://readonly@db.company.com/blocks-base
    mode: pull

  # Environment-specific overrides
  - type: database
    url: postgres://readonly@db.company.com/blocks-${DEPLOY_ENV}
    mode: pull

project:
  name: "my-project-${DEPLOY_ENV}"

Set environment before running:

export DEPLOY_ENV="production"
blocks run --all

export DEPLOY_ENV="staging"
blocks run --all

Troubleshooting

"Connection refused" error

Error: Connection refused: postgres://localhost:5432/blocks

Solution: Ensure PostgreSQL is running:

# Check if running
pg_isready

# Start PostgreSQL (macOS with Homebrew)
brew services start postgresql

# Start PostgreSQL (Linux systemd)
sudo systemctl start postgresql

"Database does not exist" error

Error: database "blocks" does not exist

Solution: Create the database first:

createdb blocks

"Permission denied" error

Error: permission denied for table blocks

Solution: Grant proper permissions:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user;

Blocks not loading from database

Check source configuration:

# Make sure mode is 'pull'
sources:
  - type: database
    url: sqlite:///blocks.db
    mode: pull  # Not 'push' or 'sync'

Verify database has data:

blocks store pull sqlite:///blocks.db
# Should show configuration, not empty

What You Learned

Database initialization - Set up SQLite or PostgreSQL storage
Configuration migration - Push existing YAML to database
Sources configuration - Load from database with local overrides
Team collaboration - Share configuration via shared database
Rollback procedure - Return to YAML-only if needed
Production setup - Secure connections and access control

Migrate to Database Storage

Prerequisites

What You'll Achieve

Step 1: Install Dependencies

Step 2: Choose Your Database

Option A: SQLite (Recommended for Getting Started)

Option B: PostgreSQL (Recommended for Teams)

Step 3: Push Existing Configuration

For SQLite

For PostgreSQL

Step 4: Update blocks.yml with Sources

Step 5: Test the Migration

Step 6: Clean Up Local YAML (Optional)

Before (all config in YAML)

After (minimal local YAML)

Step 7: Team Collaboration Setup

Making Changes to Shared Config

Rollback Procedure

PostgreSQL Production Setup

Secure Connection String

Read-Only Access for Most Users

Backup Strategy

Advanced: Multi-Environment Setup

Troubleshooting

"Connection refused" error

"Database does not exist" error

"Permission denied" error

Blocks not loading from database

What You Learned

Next Steps

Sources Configuration

Store API

Database Storage