Database: Getting Started

View raw .md

Magic provides a simple, fluent abstraction over SQLite for running raw queries, using the query builder, and managing transactions.

On this page

Introduction#

Magic provides a simple, elegant abstraction layer for working with SQLite databases. Using the DB facade, you can build queries using a fluent syntax that feels natural to Laravel developers.

The database layer is designed with schema-awareness in mind: when inserting or updating data, any columns that don't exist in your database are automatically filtered out, preventing crashes from unexpected fields.

Configuration#

Enabling Database Support#

Add DatabaseServiceProvider to your providers in config/app.dart:

dart

'providers': [
  (app) => DatabaseServiceProvider(app),
  // ... other providers
],

Database Configuration#

Create lib/config/database.dart:

dart

Map<String, dynamic> get databaseConfig => {
  'database': {
    'default': 'sqlite',
    'connections': {
      'sqlite': {
        'driver': 'sqlite',
        'database': 'magic_app.db',
        'prefix': '',
      },
    },
  },
};

Running Raw Queries#

Select Queries#

dart

// Simple select
final users = DB.select('SELECT * FROM users');

// With parameters (prevents SQL injection)
final adults = DB.select(
  'SELECT * FROM users WHERE age > ?',
  [18],
);

Insert Statements#

dart

final id = DB.insert(
  'INSERT INTO users (name, email) VALUES (?, ?)',
  ['John Doe', '[email protected]'],
);

print('Inserted user with ID: $id');

Update & Delete#

dart

// Update
final affected = DB.update(
  'UPDATE users SET name = ? WHERE id = ?',
  ['Jane Doe', userId],
);

// Delete
final deleted = DB.delete(
  'DELETE FROM users WHERE id = ?',
  [userId],
);

General Statements#

dart

// DDL statements
DB.statement('DROP TABLE IF EXISTS temp_data');
DB.statement('CREATE INDEX idx_email ON users(email)');

Query Builder#

Magic provides a fluent query builder for common operations:

dart

// Select all
final users = await DB.table('users').get();

// With conditions
final activeUsers = await DB.table('users')
    .where('is_active', true)
    .get();

// Select specific columns
final names = await DB.table('users')
    .select(['id', 'name'])
    .get();

// Insert
await DB.table('users').insert({
  'name': 'John',
  'email': '[email protected]',
});

// Update
await DB.table('users')
    .where('id', userId)
    .update({'name': 'Updated Name'});

// Delete
await DB.table('users')
    .where('id', userId)
    .delete();

Where Clauses#

dart

// Basic where
DB.table('users').where('status', 'active');

// With operator
DB.table('users').where('age', '>=', 18);

// Multiple conditions
DB.table('users')
    .where('status', 'active')
    .where('age', '>=', 18);

Ordering & Limiting#

dart

final users = await DB.table('users')
    .orderBy('created_at', 'desc')
    .limit(10)
    .offset(20)
    .get();

Transactions#

Use transactions to ensure multiple operations succeed or fail together:

dart

await DB.transaction(() async {
  await DB.table('users').insert({'name': 'John'});
  await DB.table('profiles').insert({
    'user_id': 1,
    'bio': 'Hello world',
  });
});

If any operation throws an exception, the entire transaction is rolled back.

Do not close the transaction from inside the callback. DB.transaction owns it, and a DB.commit() or DB.rollback() in there means everything written afterwards ran outside any transaction. The two cases are answered differently and neither is silent:

On failure the rollback is skipped, so your error reaches you rather than being replaced by cannot rollback - no transaction is active.
On success it throws, naming what happened. There is no error to protect on that branch, so staying quiet would only cost you the signal: the block was not atomic and returning normally would say it was.

If you need that control, use the manual API below throughout rather than mixing the two.

Manual Transaction Control#

For cases where you need finer control over transaction boundaries, call DB.beginTransaction(), DB.commit(), and DB.rollback() directly. This is useful when you need to interleave non-database work between statements or handle multiple error branches differently. It is an alternative to DB.transaction, not something to use inside one.

dart

DB.beginTransaction();

try {
  await DB.table('accounts').update({'balance': newBalance});
  await DB.table('transactions').insert({...});
  DB.commit();
} catch (e) {
  DB.rollback();
  rethrow;
}

Web Platform Setup#

For web platform support, you must download sqlite3.wasm from the sqlite3 package releases and place it in your project's web/ folder.

text

web/
├── index.html
├── sqlite3.wasm  ← Required for web
└── ...