DuxtOrm #

ActiveRecord-style ORM for the Duxt framework with GORM-like auto-migrations.

Supports PostgreSQL, MySQL, and SQLite.

Installation #

dependencies:
  duxt_orm: ^0.1.0

Quick Start #

1. Define Your Model #

import 'package:duxt_orm/duxt_orm.dart';

class User extends Model {
  int? _id;
  String? email;
  String? name;
  bool isActive;

  User({int? id, this.email, this.name, this.isActive = true}) : _id = id;

  @override dynamic get id => _id;
  @override set id(dynamic value) => _id = value as int?;

  @override
  Map<String, dynamic> toMap() => {
    'email': email,
    'name': name,
    'is_active': isActive ? 1 : 0,
  };

  factory User.fromRow(Map<String, dynamic> row) => User(
    id: row['id'] as int?,
    email: row['email'] as String?,
    name: row['name'] as String?,
    isActive: (row['is_active'] as int?) == 1,
  );

  static void register() {
    Model.registerModel<User>(
      User.fromRow,
      schema: {
        'id': Column.integer().primaryKey().autoIncrement(),
        'email': Column.string(255).unique().notNull(),
        'name': Column.string(100).notNull(),
        'is_active': Column.boolean().defaultValue(true),
        'created_at': Column.dateTime().nullable(),
        'updated_at': Column.dateTime().nullable(),
      },
    );
  }
}

2. Initialize and Migrate #

// Register models
User.register();
Post.register();

// Initialize with config
await DuxtOrm.init(DuxtConfig.database);

// Auto-create tables
await DuxtOrm.migrate();

3. Use It #

// Create
final user = User(email: 'test@example.com', name: 'Test');
await user.save();

// Query all
final users = await Model.all<User>();

// Find by ID
final user = await Model.find<User>(1);

// Query with conditions
final admins = await Model.where<User>('role', 'admin').get();

// Query builder
final results = await Model.query<User>()
    .where('is_active', 1)
    .orderByDesc('created_at')
    .limit(10)
    .get();

// Update
user.name = 'Updated Name';
await user.save();

// Delete
await user.destroy();

Configuration #

Configure in your duxt.config.dart:

class DuxtConfig {
  static const database = (
    driver: 'postgres',  // 'postgres', 'mysql', 'sqlite'
    host: String.fromEnvironment('DB_HOST', defaultValue: 'localhost'),
    port: int.fromEnvironment('DB_PORT', defaultValue: 5432),
    database: String.fromEnvironment('DB_NAME', defaultValue: 'myapp'),
    username: String.fromEnvironment('DB_USER', defaultValue: ''),
    password: String.fromEnvironment('DB_PASS', defaultValue: ''),
    path: 'data/app.db',  // SQLite only
    ssl: false,
  );
}

Table Name Convention #

Table names are auto-inferred from class names:

Override by passing tableName to registerModel():

Model.registerModel<User>(User.fromRow, tableName: 'app_users');

Schema Definition #

Column Types #

Column.integer()        // INTEGER
Column.bigInteger()     // BIGINT
Column.string(255)      // VARCHAR(255)
Column.text()           // TEXT
Column.boolean()        // BOOLEAN
Column.dateTime()       // TIMESTAMP
Column.date()           // DATE
Column.time()           // TIME
Column.decimal(10, 2)   // DECIMAL(10, 2)
Column.float()          // FLOAT
Column.json()           // JSON
Column.binary()         // BLOB

Column Modifiers #

Column.integer().primaryKey()           // PRIMARY KEY
Column.integer().autoIncrement()        // AUTO_INCREMENT
Column.string(255).notNull()            // NOT NULL
Column.string(255).nullable()           // NULL (default)
Column.string(255).unique()             // UNIQUE
Column.boolean().defaultValue(true)     // DEFAULT true
Column.integer().references('users')    // FOREIGN KEY

Example Schema #

Model.registerModel<Post>(
  Post.fromRow,
  schema: {
    'id': Column.integer().primaryKey().autoIncrement(),
    'title': Column.string(200).notNull(),
    'slug': Column.string(200).unique().notNull(),
    'content': Column.text().nullable(),
    'user_id': Column.integer().notNull().references('users'),
    'published': Column.boolean().defaultValue(false),
    'views': Column.integer().defaultValue(0),
    'created_at': Column.dateTime().nullable(),
    'updated_at': Column.dateTime().nullable(),
  },
);

Query Builder #

Basic Queries #

// All records
final users = await Model.all<User>();

// Find by ID
final user = await Model.find<User>(1);
final user = await Model.findOrFail<User>(1);  // throws if not found

// First record
final first = await Model.first<User>();

// Count
final count = await Model.count<User>();

Where Clauses #

// Simple where
Model.where<User>('is_active', 1)
Model.where<User>('age', 18, '>=')

// Multiple conditions
Model.query<User>()
    .where('is_active', 1)
    .where('role', 'admin')

// OR conditions
Model.query<User>()
    .where('role', 'admin')
    .orWhere('role', 'moderator')

// NULL checks
Model.query<User>().whereNull('deleted_at')
Model.query<User>().whereNotNull('email_verified_at')

// IN clause
Model.query<User>().whereIn('id', [1, 2, 3])
Model.query<User>().whereNotIn('status', ['banned', 'suspended'])

// BETWEEN
Model.query<User>().whereBetween('age', 18, 65)

// LIKE
Model.query<User>().whereLike('name', '%john%')

// Raw SQL
Model.query<User>().whereRaw('created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)')

Ordering & Pagination #

// Order by
Model.query<User>().orderBy('name')
Model.query<User>().orderByDesc('created_at')
Model.query<User>().latest()  // created_at DESC
Model.query<User>().oldest()  // created_at ASC

// Pagination
Model.query<User>()
    .orderByDesc('created_at')
    .skip(20)   // offset
    .take(10)   // limit
    .get()

Aggregates #

final count = await Model.query<User>().where('is_active', 1).count();
final sum = await Model.query<Order>().sum('total');
final avg = await Model.query<User>().avg('age');
final max = await Model.query<Product>().max('price');
final min = await Model.query<Product>().min('price');

Existence Checks #

if (await Model.where<User>('email', email).exists()) {
  // Email already taken
}

if (await Model.where<User>('role', 'admin').doesntExist()) {
  // No admins yet
}

Pluck #

// Get just one column
final emails = await Model.query<User>().pluck('email');
// ['john@example.com', 'jane@example.com', ...]

Bulk Operations #

// Update all matching
await Model.query<User>()
    .where('last_login', DateTime(2023), '<')
    .update({'is_active': 0});

// Delete all matching
await Model.query<User>()
    .where('is_active', 0)
    .delete();

// Increment/Decrement
await Model.query<Post>().where('id', 1).increment('views');
await Model.query<Product>().where('id', 1).decrement('stock', 5);

Transactions #

await DuxtOrm.transaction((tx) async {
  await tx.query('INSERT INTO users (email, name) VALUES (?, ?)',
    ['test@example.com', 'Test']);
  await tx.query('INSERT INTO profiles (user_id, bio) VALUES (?, ?)',
    [1, 'Hello!']);
});
// Auto-commits on success, rolls back on error

Raw Queries #

// Select
final result = await DuxtOrm.raw(
  'SELECT * FROM users WHERE email = ?',
  ['test@example.com'],
);

// Execute (INSERT/UPDATE/DELETE)
final affected = await DuxtOrm.execute(
  'UPDATE users SET is_active = ? WHERE last_login < ?',
  [0, '2023-01-01'],
);

License #

MIT