Database

Learn how to work with databases in Svelar, including migrations, seeders, schema building, and raw queries.

Configuration

Database configuration happens in src/app.ts:

import { Connection } from '@beeblock/svelar/database';

Connection.configure({
  default: 'sqlite',
  connections: {
    sqlite: {
      driver: 'sqlite',
      filename: process.env.DB_PATH ?? 'database.db',
    },
    pgsql: {
      driver: 'postgresql',
      host: process.env.DB_HOST || 'localhost',
      port: parseInt(process.env.DB_PORT || '5432'),
      database: process.env.DB_NAME || 'svelar',
      user: process.env.DB_USER || 'postgres',
      password: process.env.DB_PASSWORD || '',
    },
    mysql: {
      driver: 'mysql2',
      host: process.env.DB_HOST || 'localhost',
      port: parseInt(process.env.DB_PORT || '3306'),
      database: process.env.DB_NAME || 'svelar',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
    },
  },
});

export { Connection };

Migrations

Migrations are version-controlled database schema changes. Each migration is a file that defines up() and down() methods.

Creating a Migration

npx svelar make:migration create_users_table

This creates src/lib/database/migrations/[timestamp]_create_users_table.ts:

import { Migration } from '@beeblock/svelar/database';

export default class CreateUsersTable extends Migration {
  async up() {
    await this.schema.createTable('users', (table) => {
      table.increments('id');
      table.string('name');
      table.string('email').unique();
      table.string('password');
      table.timestamps();
    });
  }

  async down() {
    await this.schema.dropTable('users');
  }
}

Running Migrations

Run all pending migrations:

npx svelar migrate

This runs migrations in chronological order and logs completed ones to the migrations table.

Migration Commands

All migration operations use the migrate command with flags:

npx svelar migrate                # Run pending migrations
npx svelar migrate --rollback     # Rollback the last batch
npx svelar migrate --reset        # Rollback ALL migrations (runs every down())
npx svelar migrate --refresh      # Reset + re-run all migrations
npx svelar migrate --fresh        # Drop ALL tables + re-run migrations (ignores down())
npx svelar migrate --status       # Show which migrations have run
npx svelar migrate --seed         # Run seeders after migrating

Production safety: --reset, --refresh, and --fresh are blocked in production unless you also pass --force. Svelar checks NODE_ENV and APP_ENV to detect production.

# This fails in production:
npx svelar migrate --fresh

# This works in production:
npx svelar migrate --fresh --force

fresh vs refresh: --refresh calls every migration's down() method in reverse order, then runs all up() methods again. --fresh ignores migration files entirely — it drops every table in the database, then runs all up() methods. Use --fresh when your down() methods are broken or incomplete.

Schema Builder

The Schema Builder provides a fluent API for defining tables and columns.

Creating Tables

await this.schema.createTable('posts', (table) => {
  table.increments('id');                    // Auto-incrementing primary key
  table.string('title', 255);                // VARCHAR(255)
  table.text('body');                        // TEXT
  table.string('slug').unique();             // VARCHAR with unique constraint
  table.boolean('published').default(false); // BOOLEAN with default
  table.integer('user_id');                  // INTEGER
  table.timestamps();                        // created_at, updated_at timestamps

  // Indexes
  table.index(['slug']);
  table.uniqueIndex(['email']);
  table.primary(['id']);

  // Foreign keys
  table.integer('user_id').references('id', 'users');
  table.integer('category_id').references('id', 'categories').onDelete('CASCADE');
});

Adding Columns

await this.schema.addColumn('users', (table) => {
  table.string('phone');
  table.string('avatar_url').nullable();
});

Dropping Columns

await this.schema.dropColumn('users', 'nickname');

Dropping Tables

await this.schema.dropTable('users');
await this.schema.dropTableIfExists('users'); // Safe drop

Column Types

// Numeric
table.increments('id');           // Auto-incrementing INTEGER primary key
table.bigIncrements('id');        // Auto-incrementing BIGINT primary key
table.integer('count');           // INTEGER
table.bigInteger('votes');        // BIGINT
table.decimal('price', 8, 2);    // DECIMAL(8, 2)
table.float('rating');            // FLOAT

// String
table.string('name', 100);       // VARCHAR(100), default 255
table.text('bio');                // TEXT
table.json('meta');               // JSON (→ JSONB on PostgreSQL automatically)
table.jsonb('settings');          // Explicit JSONB (PostgreSQL), JSON on MySQL, TEXT on SQLite

// Boolean
table.boolean('active');          // BOOLEAN

// Dates & Times
table.date('birthday');           // DATE
table.datetime('created_at');     // DATETIME
table.timestamp('logged_at');     // TIMESTAMP
table.timestamps();               // created_at + updated_at

// Identifiers
table.uuid('id');                 // UUID — UUID on Postgres, CHAR(36) on MySQL, TEXT on SQLite
table.ulid('id');                 // ULID — VARCHAR(26) on Postgres, CHAR(26) on MySQL, TEXT on SQLite

// Other
table.blob('data');               // BLOB / binary data
table.enum('status', ['active', 'inactive']); // ENUM

Column Modifiers

table.string('email')
  .unique()                       // UNIQUE constraint
  .nullable()                     // Allow NULL
  .notNullable()                  // NOT NULL (default)
  .default('none')                // DEFAULT value
  .primary()                      // PRIMARY KEY
  .unsigned();                    // UNSIGNED (numeric columns)

Indexes

table.index('slug');                        // Simple index on one column
table.index(['slug', 'status']);            // Composite index
table.uniqueIndex('email');                // Unique index
table.uniqueIndex(['email', 'account_id']); // Composite unique index
table.primary(['id']);                      // Composite primary key

Foreign Keys

table.integer('user_id').references('id', 'users');
table.integer('category_id')
  .references('id', 'categories')
  .onDelete('CASCADE')    // CASCADE, SET NULL, RESTRICT, NO ACTION
  .onUpdate('CASCADE');   // CASCADE, SET NULL, RESTRICT, NO ACTION

Seeders

Seeders populate your database with demo or test data.

Creating a Seeder

npx svelar make:seeder DatabaseSeeder

This creates src/lib/database/seeders/DatabaseSeeder.ts:

import { Seeder } from '@beeblock/svelar/database';
import { User } from '../../models/User.js';
import { Post } from '../../models/Post.js';

export class DatabaseSeeder extends Seeder {
  async run(): Promise<void> {
    // Create 5 users
    for (let i = 1; i <= 5; i++) {
      await User.create({
        name: `User ${i}`,
        email: `user${i}@example.com`,
        password: await Hash.make('password'),
      });
    }

    // Create posts for each user
    const users = await User.all();
    for (const user of users) {
      for (let i = 1; i <= 3; i++) {
        await Post.create({
          title: `Post ${i} by ${user.name}`,
          slug: `post-${i}-${user.id}`,
          body: 'Lorem ipsum dolor sit amet...',
          user_id: user.id,
          published: true,
        });
      }
    }
  }
}

Running Seeders

npx svelar seed:run

This runs the run() method of the seeder, populating your database.

Raw Queries

For complex queries or when you need direct database access, use raw SQL:

import { Connection } from '@beeblock/svelar/database';

// Simple query
const users = await Connection.raw('SELECT * FROM users WHERE age > ?', [18]);

// Insert
await Connection.raw(
  'INSERT INTO users (name, email) VALUES (?, ?)',
  ['John Doe', 'john@example.com']
);

// Update
await Connection.raw(
  'UPDATE users SET email = ? WHERE id = ?',
  ['newemail@example.com', 1]
);

// Delete
await Connection.raw('DELETE FROM users WHERE id = ?', [1]);

// Use a specific named connection
const rows = await Connection.raw('SELECT * FROM logs', [], 'analytics');

Practical Example: Blog Database

Here's a complete example of setting up a blog database with users, posts, and comments.

Migrations

// 20260325000001_create_users_table.ts
import { Migration } from '@beeblock/svelar/database';

export default class CreateUsersTable extends Migration {
  async up() {
    await this.schema.createTable('users', (table) => {
      table.increments('id');
      table.string('name');
      table.string('email').unique();
      table.string('password');
      table.timestamps();
    });
  }

  async down() {
    await this.schema.dropTable('users');
  }
}

// 20260325000002_create_posts_table.ts
import { Migration } from '@beeblock/svelar/database';

export default class CreatePostsTable extends Migration {
  async up() {
    await this.schema.createTable('posts', (table) => {
      table.increments('id');
      table.string('title');
      table.string('slug').unique();
      table.text('body');
      table.boolean('published').default(false);
      table.integer('user_id').references('id', 'users').onDelete('cascade');
      table.timestamps();
    });
  }

  async down() {
    await this.schema.dropTable('posts');
  }
}

// 20260325000003_create_comments_table.ts
import { Migration } from '@beeblock/svelar/database';

export default class CreateCommentsTable extends Migration {
  async up() {
    await this.schema.createTable('comments', (table) => {
      table.increments('id');
      table.text('body');
      table.integer('user_id').references('id', 'users').onDelete('cascade');
      table.integer('post_id').references('id', 'posts').onDelete('cascade');
      table.timestamps();
    });
  }

  async down() {
    await this.schema.dropTable('comments');
  }
}

Run Migrations

npx svelar migrate

This creates the users, posts, and comments tables in your database.

Seed Data

// src/lib/database/seeders/DatabaseSeeder.ts
import { Seeder } from '@beeblock/svelar/database';
import { User } from '../../models/User.js';
import { Post } from '../../models/Post.js';
import { Hash } from '@beeblock/svelar/hashing';

export class DatabaseSeeder extends Seeder {
  async run(): Promise<void> {
    // Create users
    const user1 = await User.create({
      name: 'Jane Doe',
      email: 'jane@example.com',
      password: await Hash.make('password'),
    });

    const user2 = await User.create({
      name: 'John Smith',
      email: 'john@example.com',
      password: await Hash.make('password'),
    });

    // Create posts
    await Post.create({
      title: 'Getting Started with Svelar',
      slug: 'getting-started-with-svelar',
      body: 'Svelar is a Laravel-inspired framework for SvelteKit...',
      user_id: user1.id,
      published: true,
    });

    await Post.create({
      title: 'Building APIs with Svelar',
      slug: 'building-apis-with-svelar',
      body: 'Learn how to build RESTful APIs with Svelar...',
      user_id: user2.id,
      published: true,
    });
  }
}

npx svelar seed:run

Working with the Connection

The Connection class manages database drivers and provides raw SQL access:

import { Connection } from '@beeblock/svelar/database';

// Run raw queries with parameterized bindings (safe from SQL injection)
const results = await Connection.raw('SELECT * FROM users WHERE active = ?', [true]);

// Use a named connection
const logs = await Connection.raw('SELECT * FROM logs', [], 'analytics');

// Check the current driver
const driver = Connection.getDriver();         // 'sqlite' | 'postgres' | 'mysql'
const config = Connection.getConfig();          // full connection config object
const connected = Connection.isConnected();     // boolean

// Get the raw driver client (for advanced usage)
const client = await Connection.rawClient();

// Close connections when shutting down
await Connection.disconnect();                  // close default
await Connection.disconnect('analytics');       // close named connection

Multiple Connections

Svelar supports multiple database connections simultaneously. Configure them all in src/app.ts, then tell each model which connection to use.

Configuring Multiple Connections

// src/app.ts
import { Connection } from '@beeblock/svelar/database';

Connection.configure({
  default: 'sqlite',
  connections: {
    sqlite: {
      driver: 'sqlite',
      filename: process.env.DB_PATH ?? 'database.db',
    },
    analytics: {
      driver: 'postgres',
      host: process.env.ANALYTICS_DB_HOST ?? 'localhost',
      port: parseInt(process.env.ANALYTICS_DB_PORT ?? '5432'),
      database: process.env.ANALYTICS_DB_NAME ?? 'analytics',
      user: process.env.ANALYTICS_DB_USER ?? 'postgres',
      password: process.env.ANALYTICS_DB_PASSWORD ?? '',
    },
    legacy: {
      driver: 'mysql',
      host: process.env.LEGACY_DB_HOST ?? 'localhost',
      port: parseInt(process.env.LEGACY_DB_PORT ?? '3306'),
      database: process.env.LEGACY_DB_NAME ?? 'legacy_app',
      user: process.env.LEGACY_DB_USER ?? 'root',
      password: process.env.LEGACY_DB_PASSWORD ?? '',
    },
  },
});

Connections are lazily initialized — Svelar only connects to a database when it's first used, and caches the connection for subsequent queries.

Per-Model Connections

Set static connection on any model to route its queries to a specific database:

import { Model } from '@beeblock/svelar/orm';

// Uses the default connection (sqlite)
export class User extends Model {
  static table = 'users';
}

// Uses the 'analytics' connection (postgres)
export class AnalyticsEvent extends Model {
  static table = 'events';
  static connection = 'analytics';
}

// Uses the 'legacy' connection (mysql)
export class LegacyOrder extends Model {
  static table = 'orders';
  static connection = 'legacy';
}

Queries automatically route to the correct database:

const users = await User.all();                 // → sqlite
const events = await AnalyticsEvent.all();      // → postgres
const orders = await LegacyOrder.where('status', 'pending').get(); // → mysql

Raw Queries on a Specific Connection

Pass the connection name as the third argument to Connection.raw():

const rows = await Connection.raw('SELECT * FROM logs', [], 'analytics');

Migrations for Other Connections

By default, migrations run on the default connection. To run a migration against a specific connection, create a Schema with the connection name:

import { Migration, Schema } from '@beeblock/svelar/database';

export default class CreateEventsTable extends Migration {
  private analyticsSchema = new Schema('analytics');

  async up() {
    await this.analyticsSchema.createTable('events', (table) => {
      table.increments('id');
      table.string('event_type');
      table.json('payload');
      table.timestamps();
    });
  }

  async down() {
    await this.analyticsSchema.dropTable('events');
  }
}

Each connection maintains its own svelar_migrations table, so migration history is tracked independently per database.

Best Practices

1. Always use migrations - Never modify the database schema manually. Use migrations to version control your schema.

2. One change per migration - Keep migrations small and focused. Don't create multiple tables in one migration.

3. Use meaningful names - Migration names should describe what they do: create_users_table, add_email_to_users, create_posts_comments_relationship.

4. Test seeders - Seeders should be idempotent and work on a fresh database. Test them regularly.

5. Use foreign keys - Enforce referential integrity with foreign keys and cascade deletes when appropriate.

6. Document schema - Add comments to complex columns and tables to help team members understand your schema.

7. Use transactions for complex operations - When you need multiple operations to succeed or fail together, use database transactions.

Next Steps

• Learn Models & ORM to query your data
• Explore Validation to validate data before storing
• Check Controllers & Routing to handle HTTP requests

Svelar Database Guide © 2026