Database Migrations

Evolve your database schema with versioned migration files. Create collections, indexes, and manage schema changes safely.

Overview

Migrations are stored in db/migrations/ with timestamped filenames. Each migration contains up() and down() functions for applying and rolling back changes.

Note: Collections are now automatically created when you first use a Model. You can start using your models immediately without creating migrations. However, for production applications, we recommend using migrations to define indexes for better query performance, set collection options, and document your schema.

db/migrations/20260122143052_create_users.sl

def up(db: Any)
db.create_collection("users")
db.create_index("users", "idx_email", ["email"], { "unique": true })
end

def down(db: Any)
db.drop_index("users", "idx_email")
db.drop_collection("users")
end

CLI Commands

generate Create a Migration

soli db:migrate generate create_users_table

Creates: db/migrations/20260122143052_create_users_table.sl

up Run Migrations

# Apply all pending migrations
soli db:migrate

# Or explicitly
soli db:migrate up

down Rollback

# Rollback the last migration
soli db:migrate down

status Check Status

soli db:migrate status

  Database Migrations

  Version         Name                            Status
  --------------  ------------------------------  ----------
  20260122143052  create_users_table                 up
  20260122145201  add_posts_table                    up
  20260122151033  add_user_indexes                  down

  2 applied, 1 pending

Collection Helpers

db.create_collection(name, type?)

Create a collection. type is optional — one of "blob", "columnar", "timeseries", … (forwarded verbatim to SolidB). Default is a document collection.

blob — binary attachments; required for solidb_store_blob and the uploader DSL.
columnar — analytics workloads; column-oriented storage with fast aggregations.
timeseries — append-only time-indexed events (metrics, logs, telemetry).

db.drop_collection(name)

Remove a collection and all its data.

db.list_collections()

List all collections in the database.

db.collection_stats(name)

Get statistics for a collection.

Collection Examples

def up(db: Any)
  db.create_collection("users")                       # document
  db.create_collection("posts")
  db.create_collection("contact_documents", "blob")   # blob
  db.create_collection("page_views", "columnar")      # columnar
  db.create_collection("metrics", "timeseries")       # timeseries
end

def down(db: Any)
db.drop_collection("comments")
db.drop_collection("posts")
db.drop_collection("users")
end

Index Helpers

db.create_index(collection, name, fields, options)

Create an index on a collection.

fields - Array of field names: ["email"] or ["first_name", "last_name"]
options - Hash with unique: true and/or sparse: true

db.drop_index(collection, name)

Remove an index from a collection.

db.list_indexes(collection)

List all indexes for a collection.

Index Examples

def up(db: Any)    # Simple index
  db.create_index("users", "idx_email", ["email"], {})
  # Unique index
  db.create_index("users", "idx_username", ["username"], { "unique": true })
  # Sparse index (only indexes documents with the field)
  db.create_index("users", "idx_phone", ["phone"], { "sparse": true })
  # Compound index on multiple fields
  db.create_index("users", "idx_name", ["first_name", "last_name"], {})
  # Unique compound index
  db.create_index("posts", "idx_user_slug", ["user_id", "slug"], { "unique": true })
end

def down(db: Any)
db.drop_index("posts", "idx_user_slug")
db.drop_index("users", "idx_name")
db.drop_index("users", "idx_phone")
db.drop_index("users", "idx_username")
db.drop_index("users", "idx_email")
end

Raw Queries

For operations not covered by helpers, use raw SDBQL queries:

def up(db: Any)    # Insert seed data
  db.query("INSERT { name: 'Admin', role: 'admin' } INTO users")
  # Update existing data
  db.query("FOR u IN users FILTER u.role == 'guest' UPDATE u WITH { role: 'user' } IN users")

  # Bind variables (preferred for user data — avoids escaping issues)
  digest = bcrypt_hash("changeme")
  db.query(
    "INSERT { email: @e, name: @n, role: @r, password_digest: @d } INTO users",
    { "e": "[email protected]", "n": "Admin", "r": "admin", "d": digest }
  )

  # Bind variables work in FILTER / RETURN too
  db.query(
    "FOR doc IN users FILTER doc.status == @status RETURN doc",
    { "status": "active" }
  )
end

Complete Example

A migration for a blog application:

db/migrations/20260122143052_create_blog_schema.sl

def up(db: Any)    # Create collections
  db.create_collection("users")
db.create_collection("posts")
db.create_collection("comments")
db.create_collection("tags")
  # User indexes
  db.create_index("users", "idx_users_email", ["email"], { "unique": true })
db.create_index("users", "idx_users_username", ["username"], { "unique": true })
  # Post indexes
  db.create_index("posts", "idx_posts_author", ["author_id"], {})
db.create_index("posts", "idx_posts_slug", ["slug"], { "unique": true })
db.create_index("posts", "idx_posts_published", ["published_at"], { "sparse": true })
  # Comment indexes
  db.create_index("comments", "idx_comments_post", ["post_id"], {})
db.create_index("comments", "idx_comments_author", ["author_id"], {})
  # Tag indexes
  db.create_index("tags", "idx_tags_name", ["name"], { "unique": true })
end

def down(db: Any)    # Drop indexes first
  db.drop_index("tags", "idx_tags_name")
db.drop_index("comments", "idx_comments_author")
db.drop_index("comments", "idx_comments_post")
db.drop_index("posts", "idx_posts_published")
db.drop_index("posts", "idx_posts_slug")
db.drop_index("posts", "idx_posts_author")
db.drop_index("users", "idx_users_username")
db.drop_index("users", "idx_users_email")
  # Drop collections
  db.drop_collection("tags")
db.drop_collection("comments")
db.drop_collection("posts")
db.drop_collection("users")
end

Environment Configuration

Configure database connection via .env file:

.env

SOLIDB_HOST=http://localhost:6745
SOLIDB_DATABASE=myapp_development
SOLIDB_USERNAME=root
SOLIDB_PASSWORD=secret

Best Practices

Keep migrations small - One logical change per migration
Always write down() - Enable clean rollbacks
Test rollbacks - Run down then up to verify
Order matters in down() - Drop indexes before collections
Don't modify old migrations - Create new ones for changes

Helpers Reference

Method	Description
`db.create_collection(name, type?)`	Create a collection. `type` is optional — `"blob"`, `"columnar"`, `"timeseries"`, … default is document.
`db.drop_collection(name)`	Drop a collection
`db.list_collections()`	List all collections
`db.collection_stats(name)`	Get collection statistics
`db.create_index(collection, name, fields, options)`	Create an index
`db.drop_index(collection, name)`	Drop an index
`db.list_indexes(collection)`	List indexes for a collection
`db.query(sdbql, bind_vars?)`	Execute a raw SDBQL query, optionally with a hash of bind variables