Khadem Logo
Khadem BETA

Database & ORM

Powerful database abstraction with migrations, query builder, and ORM capabilities for Khadem Dart.

DatabaseManagerKhademModelQueryBuilderMigrationsSeeders

Database Configuration

Database Manager Setup

dart
// config/database.dart
import 'package:khadem/khadem_dart.dart';

class DatabaseConfig {
  static Map<String, dynamic> get config => {
    'driver': Khadem.env.getOrDefault('DB_CONNECTION', 'mysql'),
    'host': Khadem.env.getOrDefault('DB_HOST', 'localhost'),
    'port': Khadem.env.getInt('DB_PORT'),
    'database': Khadem.env.get('DB_DATABASE'),
    'username': Khadem.env.get('DB_USERNAME'),
    'password': Khadem.env.get('DB_PASSWORD'),
    'run_migrations': true,
    'run_seeders': false,
  };
}

💡 Note: Configuration is loaded from config/database.dart

⚡ Tip: Use environment variables for sensitive connection details

SQLite

File-based, no server

sqlite

MySQL

Production-ready RDBMS

mysql

PostgreSQL

Advanced features, JSON

pgsql

Migrations

Creating Migrations

dart
// database/migrations/0_create_users_table.dart
import 'package:khadem/khadem_dart.dart';

class CreateUsersTable implements MigrationFile {
  @override
  String get name => 'create_users_table';

  @override
  Future<void> up(SchemaBuilder schema) async {
    schema.create('users', (Blueprint table) {
      table.id();
      table.string('name');
      table.string('email').unique();
      table.timestamp('email_verified_at').nullable();
      table.string('password');
      table.rememberToken();
      table.timestamps();
    });
  }

  @override
  Future<void> down(SchemaBuilder schema) async {
    schema.dropIfExists('users');
  }
}

📁 Location: database/migrations/

🔄 Naming: timestamp_description.dart

dart
// Available column types in Blueprint
schema.create('products', (Blueprint table) {
  // Primary Keys
  table.id(); // Auto-incrementing primary key
  table.id('custom_id'); // Custom primary key name

  // Strings
  table.string('name', length: 100); // VARCHAR with length
  table.text('description'); // TEXT

  // Numbers
  table.integer('quantity'); // INT
  table.bigInteger('views'); // BIGINT
  table.float('weight'); // FLOAT

  // Dates & Times
  table.date('birth_date'); // DATE
  table.timestamp('created_at'); // TIMESTAMP

  // Boolean & Enum
  table.boolean('is_active'); // BOOLEAN
  table.enumColumn('status', ['pending', 'approved', 'rejected']);

  // JSON & Arrays
  table.json('metadata'); // JSON
  table.array('tags'); // ARRAY

  // Special
  table.timestamps(); // created_at and updated_at
  table.softDeletes(); // deleted_at for soft deletes

  // Foreign Keys
  table.foreignId('user_id'); // Foreign key column
  table.morphs('commentable'); // Polymorphic relation columns
});
dart
// Using the Migrator class
import 'package:khadem/khadem_dart.dart';

final migrator = Migrator(Khadem.db);

// Run all pending migrations
await migrator.upAll();

// Rollback last migration
await migrator.downAll();

// Rollback all migrations
await migrator.reset();

// Refresh database (rollback + migrate)
await migrator.refresh();

// Show migration status
await migrator.status();

// Run specific migration
await migrator.up('create_users_table');

// Rollback specific migration
await migrator.down('create_users_table');

Khadem Model (ORM)

Model Definition

dart
// app/models/user.dart
import 'package:khadem/khadem_dart.dart';

class User extends KhademModel<User> with Timestamps, HasRelationships {
  User({
    this.name,
    this.email,
    this.password,
    int? id,
  }) {
    this.id = id;
  }

  String? name;
  String? email;
  String? password;

  @override
  List<String> get fillable => [
    'name',
    'email',
    'password',
    'created_at',
    'updated_at'
  ];

  @override
  List<String> get hidden => ['password'];

  @override
  List<String> get appends => [];

  @override
  Map<String, RelationDefinition> get relations => {
    // 'posts': hasMany<Post>(
    //   foreignKey: 'user_id',
    //   relatedTable: 'posts',
    //   factory: () => Post(),
    // )
  };

  @override
  dynamic getField(String key) {
    return switch (key) {
      'id' => id,
      'name' => name,
      'email' => email,
      'password' => password,
      'created_at' => createdAt,
      'updated_at' => updatedAt,
      _ => null
    };
  }

  @override
  void setField(String key, dynamic value) {
    return switch (key) {
      'id' => id = value,
      'name' => name = value,
      'email' => email = value,
      'password' => password = value,
      'created_at' => createdAt = value,
      'updated_at' => updatedAt = value,
      _ => null
    };
  }

  @override
  User newFactory(Map<String, dynamic> data) {
    return User()..fromJson(data);
  }
}

📁 Location: app/models/

🔗 Extends: KhademModel

dart
// Create a new user
final user = User(name: 'John Doe', email: 'john@example.com');
await user.save();

// Find user by ID
final user = await User().findById(1);

// Find users by condition
final users = await User().findWhere('email', '=', 'john@example.com');

// Update user
user.name = 'John Smith';
await user.save();

// Delete user
await user.delete();

// Query with relationships
final userWithPosts = await User().load(['posts']);

// Check if relation is loaded
if (user.isRelationLoaded('posts')) {
  final posts = user.getRelation('posts');
}

// Append computed attributes
user.append(['full_name']);
user.appendAttribute('display_name');

// Get only specific attributes
final userData = user.only(['name', 'email']);

// Get all except specific attributes
final userData = user.except(['password']);
dart
// One-to-One relationship
class User extends KhademModel<User> {
  @override
  Map<String, RelationDefinition> get relations => {
    'profile': RelationDefinition<Profile>(
      type: RelationType.hasOne,
      relatedTable: 'profiles',
      localKey: 'id',
      foreignKey: 'user_id',
      factory: () => Profile(),
    ),
  };
}

class Profile extends KhademModel<Profile> {
  @override
  Map<String, RelationDefinition> get relations => {
    'user': RelationDefinition<User>(
      type: RelationType.belongsTo,
      relatedTable: 'users',
      localKey: 'user_id',
      foreignKey: 'id',
      factory: () => User(),
    ),
  };
}

// One-to-Many relationship
class User extends KhademModel<User> {
  @override
  Map<String, RelationDefinition> get relations => {
    'posts': RelationDefinition<Post>(
      type: RelationType.hasMany,
      relatedTable: 'posts',
      localKey: 'id',
      foreignKey: 'user_id',
      factory: () => Post(),
    ),
  };
}

class Post extends KhademModel<Post> {
  @override
  Map<String, RelationDefinition> get relations => {
    'user': RelationDefinition<User>(
      type: RelationType.belongsTo,
      relatedTable: 'users',
      localKey: 'user_id',
      foreignKey: 'id',
      factory: () => User(),
    ),
  };
}

// Many-to-Many relationship (simplified)
class User extends KhademModel<User> {
  @override
  Map<String, RelationDefinition> get relations => {
    'roles': RelationDefinition<Role>(
      type: RelationType.belongsToMany,
      relatedTable: 'roles',
      localKey: 'id',
      foreignKey: 'user_id',
      factory: () => Role(),
    ),
  };
}

Query Builder

Fluent Query Interface

dart
// Using DatabaseManager
import 'package:khadem/khadem_dart.dart';

final db = Khadem.db;

// Get all records
final users = await db.table('users').get();

// Get first record
final user = await db.table('users').first();

// Get specific columns
final names = await db.table('users').select(['name', 'email']).get();

// Where clauses
final activeUsers = await db.table('users')
    .where('is_active', '=', true)
    .get();

// Count records
final count = await db.table('users').count();

// Check existence
final exists = await db.table('users')
    .where('email', '=', 'john@example.com')
    .exists();

// Insert record
final userId = await db.table('users').insert({
  'name': 'John Doe',
  'email': 'john@example.com',
  'created_at': DateTime.now(),
  'updated_at': DateTime.now(),
});

// Update records
await db.table('users')
    .where('id', '=', 1)
    .update({
      'name': 'John Smith',
      'updated_at': DateTime.now(),
    });

// Delete records
await db.table('users')
    .where('id', '=', 1)
    .delete();

🔧 Interface: QueryBuilderInterface

📊 Supports: Complex queries, joins, aggregations

dart
// Complex where conditions
final users = await db.table('users')
    .where('age', '>', 18)
    .where('city', '=', 'New York')
    .orWhere('city', '=', 'Los Angeles')
    .get();

// Ordering and pagination
final users = await db.table('users')
    .orderBy('created_at', direction: 'DESC')
    .limit(10)
    .offset(20)
    .get();

// Joins
final users = await db.table('users')
    .join('posts', 'users.id', '=', 'posts.user_id')
    .select(['users.name', 'posts.title'])
    .get();

// Grouping and aggregation
final stats = await db.table('posts')
    .select(['user_id'])
    .groupBy('user_id')
    .having('COUNT(*)', '>', 5)
    .get();

// Raw queries
final users = await db.table('users')
    .whereRaw('created_at > ?', [DateTime.now().subtract(Duration(days: 30))])
    .get();
dart
// Raw SQL queries using connection
final connection = db.connection;

// Raw select
final users = await connection.query('SELECT * FROM users WHERE id = ?', [1]);

// Raw insert
final result = await connection.execute(
  'INSERT INTO users (name, email) VALUES (?, ?)',
  ['John Doe', 'john@example.com']
);

// Raw update
await connection.execute(
  'UPDATE users SET name = ? WHERE id = ?',
  ['John Smith', 1]
);

// Raw delete
await connection.execute('DELETE FROM users WHERE id = ?', [1]);

Seeders

Database Seeding

dart
// database/seeders/database_seeder.dart
import 'package:khadem/khadem_dart.dart';

class DatabaseSeeder implements Seeder {
  @override
  String get name => 'database_seeder';

  @override
  Future<void> run() async {
    // Call other seeders
    await UserSeeder().run();
    await PostSeeder().run();
    await CategorySeeder().run();
  }
}

// database/seeders/user_seeder.dart
class UserSeeder implements Seeder {
  @override
  String get name => 'user_seeder';

  @override
  Future<void> run() async {
    // Create test users
    await Khadem.db.table('users').insert([
      {
        'name': 'John Doe',
        'email': 'john@example.com',
        'password': 'hashed_password',
        'created_at': DateTime.now(),
        'updated_at': DateTime.now(),
      },
      {
        'name': 'Jane Smith',
        'email': 'jane@example.com',
        'password': 'hashed_password',
        'created_at': DateTime.now(),
        'updated_at': DateTime.now(),
      },
    ]);
  }
}

📁 Location: database/seeders/

🎯 Purpose: Populate database with test data

dart
// Using factories for seeding
class UserFactory {
  static Map<String, dynamic> make() {
    return {
      'name': 'Test User',
      'email': 'test@example.com',
      'password': 'hashed_password',
      'created_at': DateTime.now(),
      'updated_at': DateTime.now(),
    };
  }

  static Map<String, dynamic> makeWithOverrides(Map<String, dynamic> overrides) {
    return {...make(), ...overrides};
  }
}

// In seeder
class UserSeeder extends Seeder {
  @override
  void run() {
    // Create 10 users using factory
    for (var i = 0; i < 10; i++) {
      db.table('users').insert(UserFactory.makeWithOverrides({
        'name': 'User $i',
        'email': 'user$i@example.com',
      }));
    }
  }
}
dart
// Using SeederManager
import 'package:khadem/khadem_dart.dart';

final seederManager = SeederManager();

// Register seeders
seederManager.register(DatabaseSeeder());
seederManager.register(UserSeeder());

// Run all seeders
await seederManager.runAll();

// Run specific seeder
await seederManager.run('user_seeder');

Database Transactions

Transaction Management

dart
// Using DatabaseManager transactions
await Khadem.db.connection.transaction((trx) async {
  final userId = await trx.table('users').insert({
    'name': 'John Doe',
    'email': 'john@example.com',
  });

  await trx.table('profiles').insert({
    'user_id': userId,
    'bio': 'Software developer',
  });
});

// Manual transaction using connection
final connection = Khadem.db.connection;
await connection.transaction((trx) async {
  final userId = await trx.table('users').insert({
    'name': 'John Doe',
    'email': 'john@example.com',
  });

  await trx.table('profiles').insert({
    'user_id': userId,
    'bio': 'Software developer',
  });
});

🔒 Ensures: Data consistency and integrity

⚡ Performance: Batch operations efficiently

On this page