# SQL API

The `ctx.majk.sql` API provides each plugin with an isolated SQLite database for structured relational data storage. Each plugin gets its own dedicated database file with full DDL and DML freedom within its isolated environment.

## Interface

```typescript
export type PluginSQLParams = unknown[] | Record<string, unknown>;

export interface PluginSQLMigration {
  version: number;
  up: string;
  down?: string;
}

export interface PluginSQLRunResult {
  changes: number;
  lastInsertRowid?: number;
}

export interface PluginSQLColumnInfo {
  name: string;
  type: string;
  nullable: boolean;
  defaultValue: string | null;
  primaryKey: boolean;
  autoIncrement: boolean;
}

export interface PluginSQLIndexInfo {
  name: string;
  columns: string[];
  unique: boolean;
}

export interface PluginSQLTx {
  query<T>(sql: string, params?: PluginSQLParams): Promise<T[]>;
  queryOne<T>(sql: string, params?: PluginSQLParams): Promise<T | null>;
  run(sql: string, params?: PluginSQLParams): Promise<PluginSQLRunResult>;
  batch(sql: string, paramRows: PluginSQLParams[]): Promise<PluginSQLRunResult[]>;
  tables(): Promise<string[]>;
  columns(table: string): Promise<PluginSQLColumnInfo[]>;
  indexes(table: string): Promise<PluginSQLIndexInfo[]>;
}

export interface PluginSQLAPI {
  migrate(migrations: PluginSQLMigration[]): Promise<void>;
  query<T>(sql: string, params?: PluginSQLParams): Promise<T[]>;
  queryOne<T>(sql: string, params?: PluginSQLParams): Promise<T | null>;
  run(sql: string, params?: PluginSQLParams): Promise<PluginSQLRunResult>;
  batch(sql: string, paramRows: PluginSQLParams[]): Promise<PluginSQLRunResult[]>;
  transaction<T>(fn: (tx: PluginSQLTx) => Promise<T>): Promise<T>;
  tables(): Promise<string[]>;
  columns(table: string): Promise<PluginSQLColumnInfo[]>;
  indexes(table: string): Promise<PluginSQLIndexInfo[]>;
}
```

## Quick Start

```typescript
// 1. Set up your schema with migrations
await ctx.majk.sql.migrate([
  {
    version: 1,
    up: 'CREATE TABLE events (id INTEGER PRIMARY KEY, name TEXT, ts INTEGER)'
  }
]);

// 2. Insert a row
const result = await ctx.majk.sql.run(
  'INSERT INTO events (name, ts) VALUES (?, ?)',
  ['click', Date.now()]
);

// 3. Query multiple rows
const rows = await ctx.majk.sql.query<{ id: number; name: string }>(
  'SELECT id, name FROM events WHERE ts > ?',
  [Date.now() - 86400000]
);

// 4. Get single row or null
const event = await ctx.majk.sql.queryOne<{ name: string }>(
  'SELECT name FROM events WHERE id = ?',
  [123]
);

// 5. Batch operations
const results = await ctx.majk.sql.batch(
  'INSERT INTO events (name, ts) VALUES (?, ?)',
  [['click', 1000], ['hover', 2000], ['scroll', 3000]]
);

// 6. Transactions with dedicated transaction object
await ctx.majk.sql.transaction(async (tx) => {
  await tx.run('INSERT INTO accounts (name) VALUES (?)', ['Alice']);
  await tx.run('INSERT INTO accounts (name) VALUES (?)', ['Bob']);
});
```

## Methods

### `migrate(migrations: PluginSQLMigration[]): Promise<void>`

Apply versioned schema migrations. Only migrations with versions higher than the last applied version are executed. Safe to call on every plugin startup.

```typescript
await ctx.majk.sql.migrate([
  {
    version: 1,
    up: 'CREATE TABLE items (id INTEGER PRIMARY KEY, label TEXT NOT NULL)'
  },
  {
    version: 2,
    up: 'ALTER TABLE items ADD COLUMN created_at INTEGER'
  },
  {
    version: 3,
    up: `
      CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
      CREATE INDEX idx_items_category ON items(category_id);
    `
  }
]);
```

### `query<T>(sql: string, params?: PluginSQLParams): Promise<T[]>`

Execute a SELECT query and return all matching rows. Returns empty array if no rows match.

```typescript
// Positional parameters
const users = await ctx.majk.sql.query<{ id: number; name: string }>(
  'SELECT id, name FROM users WHERE active = ?',
  [1]
);

// Named parameters
const users = await ctx.majk.sql.query<{ id: number; name: string }>(
  'SELECT id, name FROM users WHERE active = :active',
  { active: 1 }
);

// No parameters
const allUsers = await ctx.majk.sql.query<{ id: number; name: string; email: string }>(
  'SELECT id, name, email FROM users ORDER BY created_at DESC'
);
```

### `queryOne<T>(sql: string, params?: PluginSQLParams): Promise<T | null>`

Execute a SELECT query and return the first row, or `null` if no rows match. Perfect for finding single records.

```typescript
const user = await ctx.majk.sql.queryOne<{ name: string; email: string }>(
  'SELECT name, email FROM users WHERE id = ?',
  [123]
);

if (user) {
  console.log('Found user:', user.name, user.email);
} else {
  console.log('User not found');
}

// Get single aggregate value
const count = await ctx.majk.sql.queryOne<{ total: number }>(
  'SELECT COUNT(*) as total FROM events'
);
console.log('Total events:', count?.total || 0);
```

### `run(sql: string, params?: PluginSQLParams): Promise<PluginSQLRunResult>`

Execute INSERT, UPDATE, or DELETE statements. Returns metadata about the operation.

```typescript
// Insert and get new row ID
const result = await ctx.majk.sql.run(
  'INSERT INTO events (name, ts) VALUES (?, ?)',
  ['startup', Date.now()]
);
console.log('Inserted row id:', result.lastInsertRowid);
console.log('Rows affected:', result.changes);

// Update with named parameters
const updateResult = await ctx.majk.sql.run(
  'UPDATE users SET last_login = :timestamp WHERE id = :userId',
  { timestamp: Date.now(), userId: 42 }
);

if (updateResult.changes === 0) {
  throw new Error('User not found');
}

// Delete
const deleteResult = await ctx.majk.sql.run(
  'DELETE FROM events WHERE ts < ?',
  [Date.now() - 30 * 24 * 60 * 60 * 1000] // 30 days ago
);
console.log(`Deleted ${deleteResult.changes} old events`);
```

### `batch(sql: string, paramRows: PluginSQLParams[]): Promise<PluginSQLRunResult[]>`

Execute the same SQL statement multiple times with different parameter sets. All operations are performed atomically.

```typescript
// Bulk insert events
const results = await ctx.majk.sql.batch(
  'INSERT INTO events (name, ts) VALUES (?, ?)',
  [
    ['click', 1000],
    ['hover', 2000],
    ['scroll', 3000],
    ['keypress', 4000]
  ]
);

console.log(`Inserted ${results.length} events`);
results.forEach((result, i) => {
  console.log(`Event ${i}: ID ${result.lastInsertRowid}, ${result.changes} changes`);
});

// Bulk update with named parameters
const updateResults = await ctx.majk.sql.batch(
  'UPDATE users SET status = :status WHERE id = :id',
  [
    { status: 'active', id: 1 },
    { status: 'inactive', id: 2 },
    { status: 'pending', id: 3 }
  ]
);
```

### `transaction<T>(fn: (tx: PluginSQLTx) => Promise<T>): Promise<T>`

Execute multiple operations atomically. All operations succeed together or fail together with automatic rollback.

**IMPORTANT**: All SQL operations within the transaction must use the `tx` parameter, not `ctx.majk.sql`.

```typescript
// Transfer money between accounts
const balances = await ctx.majk.sql.transaction(async (tx) => {
  // Create accounts
  const alice = await tx.run('INSERT INTO accounts (name, balance) VALUES (?, ?)', ['Alice', 100]);
  const bob = await tx.run('INSERT INTO accounts (name, balance) VALUES (?, ?)', ['Bob', 50]);

  // Transfer money
  await tx.run('UPDATE accounts SET balance = balance - ? WHERE name = ?', [25, 'Alice']);
  await tx.run('UPDATE accounts SET balance = balance + ? WHERE name = ?', [25, 'Bob']);

  // Return final balances
  return await tx.query<{ name: string; balance: number }>('SELECT name, balance FROM accounts');
});

console.log('Final balances:', balances);

// Complex transaction with error handling
try {
  const result = await ctx.majk.sql.transaction(async (tx) => {
    const user = await tx.queryOne<{ id: number }>(
      'SELECT id FROM users WHERE email = ?',
      ['user@example.com']
    );

    if (!user) {
      throw new Error('User not found');
    }

    await tx.run('INSERT INTO audit_log (user_id, action) VALUES (?, ?)', [user.id, 'login']);
    await tx.run('UPDATE users SET last_login = ? WHERE id = ?', [Date.now(), user.id]);

    return user.id;
  });

  console.log('Transaction completed for user:', result);
} catch (error) {
  console.error('Transaction failed:', error.message);
  // All changes are automatically rolled back
}
```

### `tables(): Promise<string[]>`

Get list of user-created table names in the database, sorted alphabetically.

```typescript
const tables = await ctx.majk.sql.tables();
console.log('Plugin tables:', tables);

// Check if specific table exists
if (tables.includes('events')) {
  const events = await ctx.majk.sql.query('SELECT * FROM events LIMIT 5');
  console.log('Recent events:', events);
}
```

### `columns(table: string): Promise<PluginSQLColumnInfo[]>`

Get detailed information about columns in a specific table.

```typescript
const columns = await ctx.majk.sql.columns('users');
columns.forEach(col => {
  console.log(`${col.name}: ${col.type}, nullable: ${col.nullable}, PK: ${col.primaryKey}`);
  if (col.defaultValue) {
    console.log(`  Default: ${col.defaultValue}`);
  }
});

// Check if column exists before adding
const userColumns = await ctx.majk.sql.columns('users');
const hasPhoneColumn = userColumns.some(col => col.name === 'phone');
if (!hasPhoneColumn) {
  await ctx.majk.sql.run('ALTER TABLE users ADD COLUMN phone TEXT');
}
```

### `indexes(table: string): Promise<PluginSQLIndexInfo[]>`

Get information about indexes on a specific table.

```typescript
const indexes = await ctx.majk.sql.indexes('events');
indexes.forEach(idx => {
  console.log(`Index ${idx.name} on columns: ${idx.columns.join(', ')}, unique: ${idx.unique}`);
});

// Check if index exists before creating
const eventIndexes = await ctx.majk.sql.indexes('events');
const hasTimestampIndex = eventIndexes.some(idx => idx.columns.includes('ts'));
if (!hasTimestampIndex) {
  await ctx.majk.sql.run('CREATE INDEX idx_events_timestamp ON events(ts)');
}
```

## Named vs Positional Parameters

The SQL API supports both parameter binding styles:

### Positional Parameters (`?`)
```typescript
// Single parameter
await ctx.majk.sql.query('SELECT * FROM users WHERE id = ?', [123]);

// Multiple parameters
await ctx.majk.sql.run(
  'INSERT INTO events (name, category, ts) VALUES (?, ?, ?)',
  ['click', 'ui', Date.now()]
);
```

### Named Parameters (`:name` or `$name`)
```typescript
// Using :name syntax
await ctx.majk.sql.query(
  'SELECT * FROM users WHERE name = :name AND active = :active',
  { name: 'John', active: 1 }
);

// Using $name syntax
await ctx.majk.sql.run(
  'UPDATE users SET last_login = $timestamp WHERE id = $userId',
  { timestamp: Date.now(), userId: 42 }
);

// Mixed parameter names (both syntaxes work)
await ctx.majk.sql.query(
  'SELECT * FROM events WHERE ts BETWEEN :start AND $end',
  { start: 1000, end: 2000 }
);
```

## Working with Transactions

### Transaction Scoping Rules

**CRITICAL**: All SQL operations within a transaction must use the `tx` parameter:

```typescript
// ✅ Correct - use tx for all operations
await ctx.majk.sql.transaction(async (tx) => {
  await tx.run('INSERT INTO users (name) VALUES (?)', ['Alice']);
  await tx.run('INSERT INTO users (name) VALUES (?)', ['Bob']);
  const users = await tx.query('SELECT * FROM users');
  return users;
});

// ❌ Wrong - mixing tx and ctx.majk.sql breaks atomicity
await ctx.majk.sql.transaction(async (tx) => {
  await tx.run('INSERT INTO users (name) VALUES (?)', ['Alice']);
  await ctx.majk.sql.run('INSERT INTO users (name) VALUES (?)', ['Bob']); // Wrong!
  return await tx.query('SELECT * FROM users');
});
```

### Why Transaction Scoping Matters

```typescript
// This demonstrates the isolation
const result = await ctx.majk.sql.transaction(async (tx) => {
  await tx.run('INSERT INTO temp_data (value) VALUES (?)', ['test']);

  // This query sees the inserted data (within transaction)
  const fromTx = await tx.query('SELECT * FROM temp_data WHERE value = ?', ['test']);

  // This query might NOT see the data (outside transaction)
  const fromMain = await ctx.majk.sql.query('SELECT * FROM temp_data WHERE value = ?', ['test']);

  console.log('From tx:', fromTx.length);    // Will be 1
  console.log('From main:', fromMain.length); // Will be 0 (until commit)

  throw new Error('Rollback test'); // This will rollback the INSERT
});
```

### Transaction Best Practices

```typescript
// 1. Keep transactions short and focused
await ctx.majk.sql.transaction(async (tx) => {
  // Do only what needs to be atomic
  const userId = await tx.run('INSERT INTO users (name) VALUES (?)', ['Alice']);
  await tx.run('INSERT INTO user_preferences (user_id) VALUES (?)', [userId.lastInsertRowid]);
  return userId.lastInsertRowid;
});

// 2. Handle errors appropriately
async function transferFunds(fromId: number, toId: number, amount: number) {
  try {
    return await ctx.majk.sql.transaction(async (tx) => {
      // Check source balance
      const fromAccount = await tx.queryOne<{ balance: number }>(
        'SELECT balance FROM accounts WHERE id = ?', [fromId]
      );

      if (!fromAccount || fromAccount.balance < amount) {
        throw new Error('Insufficient funds');
      }

      // Perform transfer
      await tx.run('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId]);
      await tx.run('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId]);

      // Log transaction
      await tx.run('INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)',
        [fromId, toId, amount]);

      return { success: true, transferId: result.lastInsertRowid };
    });
  } catch (error) {
    console.error('Transfer failed:', error.message);
    return { success: false, error: error.message };
  }
}
```

## Common Patterns

### CRUD Service Class

```typescript
interface User {
  id: number;
  name: string;
  email: string;
  created_at: number;
}

class UserService {
  constructor(private sql: PluginSQLAPI) {}

  async init() {
    await this.sql.migrate([
      {
        version: 1,
        up: `CREATE TABLE users (
               id INTEGER PRIMARY KEY AUTOINCREMENT,
               name TEXT NOT NULL,
               email TEXT NOT NULL UNIQUE,
               created_at INTEGER NOT NULL DEFAULT (unixepoch())
             )`
      }
    ]);
  }

  async create(name: string, email: string): Promise<number> {
    const result = await this.sql.run(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [name, email]
    );
    return result.lastInsertRowid!;
  }

  async findById(id: number): Promise<User | null> {
    return await this.sql.queryOne<User>(
      'SELECT * FROM users WHERE id = ?',
      [id]
    );
  }

  async findByEmail(email: string): Promise<User | null> {
    return await this.sql.queryOne<User>(
      'SELECT * FROM users WHERE email = ?',
      [email]
    );
  }

  async update(id: number, updates: Partial<Pick<User, 'name' | 'email'>>): Promise<boolean> {
    const sets: string[] = [];
    const params: unknown[] = [];

    if (updates.name !== undefined) {
      sets.push('name = ?');
      params.push(updates.name);
    }
    if (updates.email !== undefined) {
      sets.push('email = ?');
      params.push(updates.email);
    }

    if (sets.length === 0) return true;

    params.push(id);
    const result = await this.sql.run(
      `UPDATE users SET ${sets.join(', ')} WHERE id = ?`,
      params
    );

    return result.changes > 0;
  }

  async delete(id: number): Promise<boolean> {
    const result = await this.sql.run('DELETE FROM users WHERE id = ?', [id]);
    return result.changes > 0;
  }

  async list(limit = 50, offset = 0): Promise<User[]> {
    return await this.sql.query<User>(
      'SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?',
      [limit, offset]
    );
  }

  async search(query: string): Promise<User[]> {
    return await this.sql.query<User>(
      'SELECT * FROM users WHERE name LIKE ? OR email LIKE ? ORDER BY name',
      [`%${query}%`, `%${query}%`]
    );
  }
}

// Usage
const userService = new UserService(ctx.majk.sql);
await userService.init();

const userId = await userService.create('John Doe', 'john@example.com');
const user = await userService.findById(userId);
```

### Upsert Pattern

```typescript
// Insert or update on conflict
async function upsertSetting(key: string, value: string) {
  await ctx.majk.sql.run(`
    INSERT INTO settings (key, value, updated_at) VALUES (?, ?, unixepoch())
    ON CONFLICT(key) DO UPDATE SET
      value = excluded.value,
      updated_at = excluded.updated_at
  `, [key, value]);
}

// Bulk upsert
async function upsertSettings(settings: Record<string, string>) {
  const params = Object.entries(settings).map(([key, value]) => [key, value]);

  await ctx.majk.sql.batch(`
    INSERT INTO settings (key, value, updated_at) VALUES (?, ?, unixepoch())
    ON CONFLICT(key) DO UPDATE SET
      value = excluded.value,
      updated_at = excluded.updated_at
  `, params);
}
```

### Pagination

```typescript
interface PaginatedResult<T> {
  items: T[];
  total: number;
  page: number;
  pageSize: number;
  totalPages: number;
  hasNext: boolean;
  hasPrev: boolean;
}

async function paginate<T>(
  query: string,
  params: PluginSQLParams,
  page: number = 1,
  pageSize: number = 20
): Promise<PaginatedResult<T>> {
  const offset = (page - 1) * pageSize;

  // Get total count (modify query to count)
  const countQuery = query.replace(/SELECT .+ FROM/, 'SELECT COUNT(*) as count FROM');
  const countResult = await ctx.majk.sql.queryOne<{ count: number }>(countQuery, params);
  const total = countResult?.count || 0;

  // Get items for current page
  const items = await ctx.majk.sql.query<T>(
    `${query} LIMIT ? OFFSET ?`,
    [...(Array.isArray(params) ? params : [params]), pageSize, offset]
  );

  const totalPages = Math.ceil(total / pageSize);

  return {
    items,
    total,
    page,
    pageSize,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1
  };
}

// Usage
const result = await paginate<User>(
  'SELECT * FROM users WHERE active = ? ORDER BY created_at DESC',
  [1],
  2, // page 2
  10 // 10 items per page
);

console.log(`Showing ${result.items.length} of ${result.total} users (page ${result.page}/${result.totalPages})`);
```

### Full-Text Search with FTS5

```typescript
// Set up FTS5 virtual table
await ctx.majk.sql.migrate([
  {
    version: 1,
    up: `
      CREATE TABLE documents (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        created_at INTEGER NOT NULL DEFAULT (unixepoch())
      )
    `
  },
  {
    version: 2,
    up: `
      CREATE VIRTUAL TABLE documents_fts USING fts5(
        title,
        content,
        content='documents',
        content_rowid='id'
      );

      -- Triggers to keep FTS in sync
      CREATE TRIGGER documents_ai AFTER INSERT ON documents BEGIN
        INSERT INTO documents_fts(rowid, title, content) VALUES (new.id, new.title, new.content);
      END;

      CREATE TRIGGER documents_ad AFTER DELETE ON documents BEGIN
        INSERT INTO documents_fts(documents_fts, rowid, title, content)
        VALUES ('delete', old.id, old.title, old.content);
      END;

      CREATE TRIGGER documents_au AFTER UPDATE ON documents BEGIN
        INSERT INTO documents_fts(documents_fts, rowid, title, content)
        VALUES ('delete', old.id, old.title, old.content);
        INSERT INTO documents_fts(rowid, title, content) VALUES (new.id, new.title, new.content);
      END;
    `
  }
]);

// Insert documents
await ctx.majk.sql.batch(
  'INSERT INTO documents (title, content) VALUES (?, ?)',
  [
    ['Getting Started', 'SQLite FTS5 provides full-text search capabilities'],
    ['Advanced Usage', 'Full text search with ranking and highlighting'],
    ['Performance Tips', 'Optimize your FTS queries for better speed']
  ]
);

// Search documents
async function searchDocuments(query: string, limit = 10) {
  return await ctx.majk.sql.query<{
    id: number;
    title: string;
    content: string;
    rank: number;
  }>(`
    SELECT d.id, d.title, d.content, bm25(fts) as rank
    FROM documents_fts fts
    JOIN documents d ON d.id = fts.rowid
    WHERE documents_fts MATCH ?
    ORDER BY rank
    LIMIT ?
  `, [query, limit]);
}

// Usage
const results = await searchDocuments('SQLite performance');
results.forEach(doc => {
  console.log(`${doc.title} (relevance: ${doc.rank.toFixed(2)})`);
});
```

### Audit Logging

```typescript
// Set up audit table
await ctx.majk.sql.migrate([
  {
    version: 1,
    up: `
      CREATE TABLE audit_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        table_name TEXT NOT NULL,
        record_id INTEGER NOT NULL,
        action TEXT NOT NULL, -- 'insert', 'update', 'delete'
        old_values TEXT, -- JSON
        new_values TEXT, -- JSON
        user_id INTEGER,
        timestamp INTEGER NOT NULL DEFAULT (unixepoch())
      )
    `
  }
]);

// Audit helper function
async function auditChange(
  tx: PluginSQLTx,
  tableName: string,
  recordId: number,
  action: 'insert' | 'update' | 'delete',
  oldValues?: any,
  newValues?: any,
  userId?: number
) {
  await tx.run(
    'INSERT INTO audit_log (table_name, record_id, action, old_values, new_values, user_id) VALUES (?, ?, ?, ?, ?, ?)',
    [
      tableName,
      recordId,
      action,
      oldValues ? JSON.stringify(oldValues) : null,
      newValues ? JSON.stringify(newValues) : null,
      userId || null
    ]
  );
}

// Usage in service methods
async function updateUserWithAudit(userId: number, updates: Partial<User>, currentUserId: number) {
  return await ctx.majk.sql.transaction(async (tx) => {
    // Get old values
    const oldUser = await tx.queryOne<User>('SELECT * FROM users WHERE id = ?', [userId]);
    if (!oldUser) throw new Error('User not found');

    // Update user
    const sets: string[] = [];
    const params: unknown[] = [];

    if (updates.name !== undefined) {
      sets.push('name = ?');
      params.push(updates.name);
    }
    if (updates.email !== undefined) {
      sets.push('email = ?');
      params.push(updates.email);
    }

    if (sets.length === 0) return true;

    params.push(userId);
    const result = await tx.run(
      `UPDATE users SET ${sets.join(', ')} WHERE id = ?`,
      params
    );

    if (result.changes > 0) {
      // Log the change
      await auditChange(tx, 'users', userId, 'update', oldUser, updates, currentUserId);
    }

    return result.changes > 0;
  });
}
```

## Important Notes

### Database Location
- Each plugin gets its own isolated SQLite file: `~/.majk/plugin-data/{pluginId}/plugin-sql.db`
- No access to other plugins' databases or the main Majk database
- WAL mode enabled for concurrent read/write performance
- Foreign keys enabled automatically

### Parameter Binding
- **Always use parameter binding** to prevent SQL injection
- Both positional (`?`) and named (`:name`, `$name`) parameters supported
- Parameters are type-safe and automatically escaped

### Transaction Guidelines
- **ALL** SQL within a transaction must use the `tx` parameter
- Keep transactions short and focused
- Transactions are automatically rolled back on errors
- Nested transactions are not supported

### Migration Best Practices
- **Never modify existing migrations** - always add new versions
- Use incremental version numbers (1, 2, 3...)
- Each migration runs in its own transaction
- Safe to call `migrate()` on every plugin startup

### Performance Considerations
- Create indexes for frequently queried columns
- Use `queryOne()` when you only need the first result
- Consider using `batch()` for bulk operations
- Use transactions for related operations that should be atomic

### Error Handling
```typescript
try {
  const user = await ctx.majk.sql.queryOne<User>('SELECT * FROM users WHERE id = ?', [userId]);
  if (!user) {
    throw new Error('User not found');
  }
  // Use user safely
} catch (error) {
  console.error('Database error:', error.message);
  // Handle error appropriately
}
```

The SQL API provides a powerful, isolated database environment for each plugin with full SQL capabilities while maintaining security and data isolation between plugins.