Skip to content

getting-started.md

Latest commit

 

History

History
217 lines (165 loc) · 4.44 KB

getting-started.md

File metadata and controls

217 lines (165 loc) · 4.44 KB

Database: Getting Started

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:

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

Database Configuration

Create lib/config/database.dart:

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

Running Raw Queries

Select Queries

// 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

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

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

Update & Delete

// 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

// 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:

// 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': 'john@example.com',
});

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

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

Where Clauses

// 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

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:

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.

Manual Transaction Control

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.

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

Warning

The web platform uses an in-memory SQLite database. Data is not persisted between page reloads unless you implement custom persistence.