sql_lite_builder example | Flutter package

example/lib/main.dart
/// sql_lite_builder — Comprehensive Example Application
///
/// This example demonstrates every major feature of the package
/// through a simulated blog-style app with users and posts.
///
/// Run with: `flutter run`
///
/// Alternatively, this file can be read as a recipe book —
/// every code block is a working example with explanations.
library;

import 'package:sqflite/sqflite.dart';
import 'package:sql_lite_builder/sql_lite_builder.dart';
import 'database_setup.dart';
import 'models/user.dart';
import 'models/post.dart';

Future<void> main() async {
  // ── 1. Open Database ────────────────────────────────────────────────────

  // In a real app: openAppDatabase()
  // Here we use in-memory for portability:
  final db = await openInMemoryDatabase();
  print('\n' + '═' * 60);
  print(' sql_lite_builder — Example App');
  print('═' * 60);

  await _runAllExamples(db);

  await db.close();
  print('\n✅  All examples completed successfully.\n');
}

Future<void> _runAllExamples(Database db) async {
  await _example01_insert(db);
  await _example02_select(db);
  await _example03_where(db);
  await _example04_aggregates(db);
  await _example05_update(db);
  await _example06_delete(db);
  await _example07_joins(db);
  await _example08_pagination(db);
  await _example09_transactions(db);
  await _example10_schema(db);
  await _example11_typedResults(db);
  await _example12_advanced(db);
}

// ══════════════════════════════════════════════════════════════════════════════
// 01 — INSERT
// ══════════════════════════════════════════════════════════════════════════════

Future<void> _example01_insert(Database db) async {
  _header('01 — INSERT');

  // ── Single row insert ──
  final ahmedId = await db.table('users').insert({
    'name': 'Ahmed Ali',
    'email': 'ahmed@example.com',
    'age': 28,
    'is_active': true,
    'created_at': DateTime.now(),
  });
  print('  ✅  Inserted user id=$ahmedId');

  final saraId = await db.table('users').insert({
    'name': 'Sara Hassan',
    'email': 'sara@example.com',
    'age': 24,
    'is_active': true,
    'created_at': DateTime.now(),
  });

  final omarId = await db.table('users').insert({
    'name': 'Omar Khalid',
    'email': 'omar@example.com',
    'age': 35,
    'is_active': false,
    'created_at': DateTime.now(),
  });

  // ── Batch insert (insertMany) ──
  await db.table('posts').insertMany([
    {
      'user_id': ahmedId,
      'title': 'Getting Started with Flutter',
      'content': 'Flutter makes it easy to build beautiful apps...',
      'views': 1500,
      'published': true,
      'created_at': DateTime.now(),
    },
    {
      'user_id': ahmedId,
      'title': 'Dart null safety explained',
      'content': 'Null safety eliminates a whole class of bugs...',
      'views': 900,
      'published': true,
      'created_at': DateTime.now(),
    },
    {
      'user_id': ahmedId,
      'title': 'My draft ideas',
      'views': 0,
      'published': false,
      'created_at': DateTime.now(),
    },
    {
      'user_id': saraId,
      'title': 'Provider vs Riverpod',
      'content': 'State management comparison...',
      'views': 4200,
      'published': true,
      'created_at': DateTime.now(),
    },
    {
      'user_id': saraId,
      'title': 'Custom animations in Flutter',
      'views': 750,
      'published': true,
      'created_at': DateTime.now(),
    },
    {
      'user_id': omarId,
      'title': 'Testing in Flutter',
      'views': 300,
      'published': true,
      'created_at': DateTime.now(),
    },
  ]);
  print('  ✅  Inserted 6 posts via insertMany()');

  // ── insertOrIgnore ──
  final ignored = await db.table('users').insertOrIgnore({
    'name': 'Duplicate',
    'email': 'ahmed@example.com', // duplicate email — will be ignored
    'age': 99,
  });
  print('  ✅  insertOrIgnore returned: $ignored '
      '(0 means the row was ignored)');
}

// ══════════════════════════════════════════════════════════════════════════════
// 02 — SELECT
// ══════════════════════════════════════════════════════════════════════════════

Future<void> _example02_select(Database db) async {
  _header('02 — SELECT');

  // Select all
  final all = await db.table('users').get();
  print('  Total users: ${all.length}');

  // Select specific columns
  final names = await db.table('users').select(['id', 'name', 'age']).get();
  print('  Names: ${names.map((r) => r['name']).join(', ')}');

  // Select distinct values
  final roles = await db.table('users').selectDistinct(['is_active']).get();
  print('  Distinct is_active values: ${roles.map((r) => r['is_active'])}');

  // selectRaw for expressions
  final stats = await db.table('users')
      .selectRaw('COUNT(*) AS cnt, AVG(age) AS avg_age, MAX(age) AS max_age')
      .get();
  print('  Stats: $stats');
}

// ══════════════════════════════════════════════════════════════════════════════
// 03 — WHERE (all variants)
// ══════════════════════════════════════════════════════════════════════════════

Future<void> _example03_where(Database db) async {
  _header('03 — WHERE');

  // Basic equality
  final active = await db.table('users')
      .where('is_active', WhereOp.eq, true)
      .count();
  print('  Active users: $active');

  // Comparison operators
  final over25 = await db.table('users')
      .where('age', WhereOp.gte, 25)
      .pluck('name');
  print('  Users aged ≥ 25: $over25');

  // LIKE pattern
  final withA = await db.table('users')
      .where('name', WhereOp.like, 'A%')
      .pluck('name');
  print('  Names starting with A: $withA');

  // OR WHERE
  final adminOrInactive = await db.table('posts')
      .where('published', WhereOp.eq, true)
      .orWhere('views', WhereOp.gt, 1000)
      .count();
  print('  Published OR >1000 views: $adminOrInactive');

  // whereIn
  final specificUsers = await db.table('users')
      .whereIn('name', ['Ahmed Ali', 'Sara Hassan'])
      .pluck('email');
  print('  Specific emails: $specificUsers');

  // whereNotIn
  final notSara = await db.table('users')
      .whereNotIn('name', ['Sara Hassan'])
      .pluck('name');
  print('  Users excluding Sara: $notSara');

  // whereBetween
  final midAge = await db.table('users')
      .whereBetween('age', 25, 35)
      .pluck('name');
  print('  Users aged 25–35: $midAge');

  // whereNull / whereNotNull
  final noContent = await db.table('posts').whereNull('content').count();
  print('  Posts with no content: $noContent');

  // whereNested
  final complex = await db.table('users')
      .where('is_active', WhereOp.eq, true)
      .whereNested((q) => q
          .where('age', WhereOp.lt, 25)
          .orWhere('age', WhereOp.gt, 30))
      .pluck('name');
  print('  Active users aged <25 or >30: $complex');

  // whereRaw
  final rawResult = await db.table('posts')
      .whereRaw('views > ? AND published = ?', [500, 1])
      .count();
  print('  Posts with >500 views AND published (raw): $rawResult');
}

// ══════════════════════════════════════════════════════════════════════════════
// 04 — AGGREGATES
// ══════════════════════════════════════════════════════════════════════════════

Future<void> _example04_aggregates(Database db) async {
  _header('04 — AGGREGATES');

  print('  count():           ${await db.table('users').count()}');
  print('  count(active):     '
      '${await db.table('users').where('is_active', WhereOp.eq, true).count()}');
  print('  max(age):          ${await db.table('users').max('age')}');
  print('  min(age):          ${await db.table('users').min('age')}');
  print('  avg(age):          ${await db.table('users').avg('age')}');
  print('  sum(views):        ${await db.table('posts').sum('views')}');
  print('  exists (ahmed):    '
      '${await db.table('users').where('email', WhereOp.eq, 'ahmed@example.com').exists()}');
  print('  doesntExist(ghost):'
      '${await db.table('users').where('email', WhereOp.eq, 'ghost@nowhere.com').doesntExist()}');
}

// ══════════════════════════════════════════════════════════════════════════════
// 05 — UPDATE
// ══════════════════════════════════════════════════════════════════════════════

Future<void> _example05_update(Database db) async {
  _header('05 — UPDATE');

  // Standard update with WHERE
  final affected = await db.table('users')
      .where('email', WhereOp.eq, 'ahmed@example.com')
      .update({'age': 29, 'name': 'Ahmed Mohammed'});
  print('  update() affected: $affected rows');

  // Increment
  await db.table('posts')
      .where('title', WhereOp.like, '%Flutter%')
      .increment('views', by: 100);
  print('  increment(views, by: 100) on Flutter posts — done');

  // Decrement
  await db.table('posts')
      .where('published', WhereOp.eq, false)
      .decrement('views');
  print('  decrement(views) on draft posts — done');

  // updateAll (no WHERE required — explicit intent)
  // Mark all posts as reviewed (hypothetical column)
  // await db.table('posts').updateAll({'reviewed': false});

  // Verify
  final ahmed = await db.table('users')
      .where('email', WhereOp.eq, 'ahmed@example.com')
      .first();
  print('  Ahmed after update: name=${ahmed!['name']}, age=${ahmed['age']}');
}

// ══════════════════════════════════════════════════════════════════════════════
// 06 — DELETE
// ══════════════════════════════════════════════════════════════════════════════

Future<void> _example06_delete(Database db) async {
  _header('06 — DELETE');

  // Insert a temporary user to delete
  final tempId = await db.table('users').insert({
    'name': 'Temp User',
    'email': 'temp@example.com',
    'age': 1,
    'is_active': false,
  });

  final deleted = await db.table('users')
      .where('id', WhereOp.eq, tempId)
      .delete();
  print('  delete() removed: $deleted row');

  // Safety: delete without WHERE throws
  try {
    await db.table('users').delete();
  } on DangerousOperationException catch (e) {
    print('  Safety guard caught: ${e.toString().substring(0, 50)}...');
  }

  final remaining = await db.table('users').count();
  print('  Remaining users: $remaining');
}

// ══════════════════════════════════════════════════════════════════════════════
// 07 — JOINs
// ══════════════════════════════════════════════════════════════════════════════

Future<void> _example07_joins(Database db) async {
  _header('07 — JOINs');

  // INNER JOIN
  final postsWithAuthors = await db.table('posts')
      .select([
        'posts.id',
        'posts.title',
        'users.name AS author_name',
        'posts.views',
        'posts.published',
      ])
      .join('users', 'posts.user_id', '=', 'users.id')
      .where('posts.published', WhereOp.eq, true)
      .orderByDesc('posts.views')
      .limit(3)
      .getAs(Post.fromMap);

  print('  Top 3 published posts (with authors):');
  for (final p in postsWithAuthors) {
    print('    📝 "${p.title}" by ${p.authorName} — ${p.views} views');
  }

  // LEFT JOIN — include users with no posts
  final usersAndPostCounts = await db.table('users')
      .selectRaw('users.name, COUNT(posts.id) AS post_count')
      .leftJoin('posts', 'users.id', '=', 'posts.user_id')
      .groupBy('users.id')
      .orderByDesc('post_count')
      .get();

  print('  Post counts per user:');
  for (final r in usersAndPostCounts) {
    print('    👤 ${r['name']}: ${r['post_count']} posts');
  }
}

// ══════════════════════════════════════════════════════════════════════════════
// 08 — PAGINATION
// ══════════════════════════════════════════════════════════════════════════════

Future<void> _example08_pagination(Database db) async {
  _header('08 — PAGINATION');

  // Basic paginate()
  final page1 = await db.table('posts')
      .where('published', WhereOp.eq, true)
      .orderByDesc('views')
      .paginate(page: 1, perPage: 2)
      .get();
  print('  Page 1 (2 per page): ${page1.map((r) => r['title'])}');

  // Typed paginateAs()
  final typedPage = await db.table('posts')
      .where('published', WhereOp.eq, true)
      .orderByDesc('views')
      .paginateAs(page: 1, perPage: 2, mapper: Post.fromMap);

  print('  PaginatedResult: page=${typedPage.currentPage}/${typedPage.lastPage}, '
      'total=${typedPage.total}, hasNext=${typedPage.hasNextPage}');

  // chunk() — process large datasets without loading all into memory
  var totalViews = 0;
  await db.table('posts').chunk(2, (rows) async {
    totalViews += rows.fold(0, (sum, r) => sum + (r['views'] as int));
    return true;
  });
  print('  Total views (via chunk): $totalViews');
}

// ══════════════════════════════════════════════════════════════════════════════
// 09 — TRANSACTIONS
// ══════════════════════════════════════════════════════════════════════════════

Future<void> _example09_transactions(Database db) async {
  _header('09 — TRANSACTIONS');

  final beforeAhmedViews = await db.table('posts')
      .where('user_id', WhereOp.eq, 1)
      .sum('views');
  print('  Ahmed\'s total views before: $beforeAhmedViews');

  // Atomic transfer: deactivate Omar, activate all his posts to Ahmed
  await db.transactionSafe((txn) async {
    // Deactivate Omar
    await txn.table('users')
        .where('name', WhereOp.eq, 'Omar Khalid')
        .update({'is_active': false});

    // Boost all published posts' views by 50 atomically
    await txn.table('posts')
        .where('published', WhereOp.eq, true)
        .increment('views', by: 50);
  });

  print('  Transaction committed — Omar deactivated, post views boosted');

  // Demonstrate rollback
  final preCount = await db.table('users').count();
  try {
    await db.transactionSafe((txn) async {
      await txn.table('users').insert({
        'name': 'Ghost',
        'email': 'ghost@t.com',
        'age': 0,
      });
      // Simulate an error
      throw Exception('Something went wrong — rolling back');
    });
  } catch (e) {
    print('  Transaction rolled back: $e');
  }
  final postCount = await db.table('users').count();
  print('  User count before=$preCount, after=$postCount '
      '(should be equal — rollback worked)');
}

// ══════════════════════════════════════════════════════════════════════════════
// 10 — SCHEMA & DATABASE HELPERS
// ══════════════════════════════════════════════════════════════════════════════

Future<void> _example10_schema(Database db) async {
  _header('10 — SCHEMA & DATABASE HELPERS');

  print('  tableExists(users):  ${await db.tableExists('users')}');
  print('  tableExists(orders): ${await db.tableExists('orders')}');
  print('  columnExists(users, email): ${await db.columnExists('users', 'email')}');
  print('  columnExists(users, phone): ${await db.columnExists('users', 'phone')}');
  print('  rowCount(users): ${await db.rowCount('users')}');
  print('  rowCount(posts): ${await db.rowCount('posts')}');

  // Demonstrate toCreateSql output
  final usersSchema = const UsersTable().toCreateSql();
  print('\n  Generated CREATE TABLE for users:');
  for (final line in usersSchema.split('\n')) {
    print('    $line');
  }
}

// ══════════════════════════════════════════════════════════════════════════════
// 11 — TYPED RESULTS
// ══════════════════════════════════════════════════════════════════════════════

Future<void> _example11_typedResults(Database db) async {
  _header('11 — TYPED RESULTS (getAs / firstAs / groupedBy / getIndexedBy)');

  // getAs
  final users = await db.table('users')
      .where('is_active', WhereOp.eq, true)
      .orderBy('name')
      .getAs(User.fromMap);
  print('  Active users: ${users.map((u) => u.name).join(', ')}');

  // firstAs
  final ahmed = await db.table('users')
      .where('email', WhereOp.eq, 'ahmed@example.com')
      .firstAs(User.fromMap);
  print('  firstAs User: $ahmed');

  // getIndexedBy — build a lookup map
  final usersById = await db.table('users').getIndexedBy<int, User>(
    keyColumn: 'id',
    mapper: User.fromMap,
    keyMapper: (v) => v as int,
  );
  print('  Users indexed by id: keys=${usersById.keys.toList()}');

  // groupedBy
  final byActive = await db.table('users').groupedBy<int, User>(
    keyColumn: 'is_active',
    mapper: User.fromMap,
    keyMapper: (v) => v as int,
  );
  print('  Grouped by is_active:');
  byActive.forEach((k, v) {
    print('    is_active=$k → ${v.map((u) => u.name).join(', ')}');
  });
}

// ══════════════════════════════════════════════════════════════════════════════
// 12 — ADVANCED
// ══════════════════════════════════════════════════════════════════════════════

Future<void> _example12_advanced(Database db) async {
  _header('12 — ADVANCED (debug, random order, group by)');

  // toSqlWithBindings — inspect generated SQL without executing
  final sqlPreview = db.table('users')
      .select(['id', 'name'])
      .where('age', WhereOp.gt, 18)
      .where('is_active', WhereOp.eq, true)
      .orderBy('name')
      .limit(10)
      .toSqlWithBindings();
  print('  Generated SQL:\n    $sqlPreview');

  // inRandomOrder — useful for recommendations
  final random = await db.table('posts')
      .where('published', WhereOp.eq, true)
      .inRandomOrder()
      .limit(2)
      .pluck('title');
  print('  Random 2 posts: $random');

  // GROUP BY with HAVING
  final activeContributors = await db.table('posts')
      .selectRaw('user_id, COUNT(*) AS cnt, SUM(views) AS total_views')
      .where('published', WhereOp.eq, true)
      .groupBy('user_id')
      .having('COUNT(*)', '>', 1)
      .orderByDesc('total_views')
      .get();
  print('  Contributors with >1 published post: $activeContributors');

  // whereRaw with SQLite function
  final year2024Users = await db.table('users')
      .whereRaw("strftime('%Y', created_at) = ?", ['2024'])
      .count();
  print('  Users created in 2024: $year2024Users');

  // pluck a single column
  final allEmails = await db.table('users').pluck('email');
  print('  All emails: $allEmails');
}

// ─── Helper ───────────────────────────────────────────────────────────────────

void _header(String title) {
  print('\n┌─ $title ${'─' * (50 - title.length)}┐');
}
sql_lite_builder 1.0.0
sql_lite_builder: ^1.0.0 copied to clipboard

Metadata

← Metadata

Documentation

Publisher

Weekly Downloads

Metadata

Topics

License

Dependencies

More

sql_lite_builder 1.0.0 sql_lite_builder: ^1.0.0 copied to clipboard

Metadata

← Metadata

Documentation

Publisher

Weekly Downloads

Metadata

Topics

License

Dependencies

More

sql_lite_builder 1.0.0
sql_lite_builder: ^1.0.0 copied to clipboard
