#Lecture 001 — What Is Sqflite & Project Setup

Hive saves a Dart object as a whole. Sqflite saves rows in a table, read back with real SQL — SELECT, WHERE, joins. It is the same SQLite engine that ships inside every Android and iOS phone, with a Dart API in front of it.

Contents

Prerequisites · Connection · Objectives · Lesson · Code Examples · Common Mistakes · Best Practices · Mini Challenge · Practice Questions · Answers · Takeaways

#Prerequisites

• Dart classes, maps and named constructors; Future, async and await
• A running Flutter app: Scaffold, AppBar, StatefulWidget (Essential Widgets 001)

#Connection With Previous Lectures

Earlier courses either kept data in a variable (gone when the app closes) or, in the Hive course, in a box keyed by a single field. This course builds a users table — one row per user, with an id, a name, an email, and a few other columns — and reads it back with SQL instead of a key lookup.

#Learning Objectives

By the end of this lecture you will be able to:

• Explain what sqflite is and when SQL beats a key-value box
• Add sqflite and path to a project
• Describe openDatabase, onCreate and a CREATE TABLE statement
• Know the folder layout the rest of this course uses

#1. Why a SQL Database

Sqflite is a good fit once you need to filter ("every user created this week"), sort, or join more than one table. For a single settings blob or a simple cache, Hive is simpler.

#2. What Is Sqflite

• A table, not a box — a grid of rows and named columns, same as a spreadsheet.
• Real SQL — you write CREATE TABLE, INSERT, UPDATE, DELETE, SELECT as text.
• One file on disk — the whole database lives in a single .db file inside the app's own folder, found with getDatabasesPath().
• Cross-platform — Android and iOS both ship SQLite already; the plugin just talks to it.

#3. The Three Moving Parts

1. Database — opened once with openDatabase, versioned, created with onCreate (Lecture 001, this one).
2. Model — a Dart class with toMap() / fromMap() so a row can become an object and back (Lecture 002).
3. Manager class — one file holding every insert / update / delete / query call (Lectures 003–004).

#4. Create the Project and Add Packages

flutter create sqflite_demo
cd sqflite_demo
flutter pub add sqflite path intl

• sqflite — the database plugin itself.
• path — joins the database file name to the platform's databases folder correctly on every OS (p.join, not string concatenation with /).
• intl — used later for formatting the created_at / updated_at timestamps.

#5. The Folder Layout

lib/
  main.dart
  data_sqflite/
    model_user.dart         ← the model (Lecture 002)
    database_user.dart      ← the manager (Lectures 003–004)
  screen_dashboard.dart     ← the list (not covered in this course)

Everything that touches the database lives in data_sqflite/; screens only call the manager.

#6. Opening the Database

// lib/data_sqflite/database_user.dart
import 'package:path/path.dart' as p;
import 'package:sqflite/sqflite.dart';

Future<Database> openUserDatabase() async {
  final databasesPath = await getDatabasesPath();
  final path = p.join(databasesPath, 'database_user.db');

  return openDatabase(
    path,
    version: 1,
    onCreate: (db, version) async {
      await db.execute('''
        CREATE TABLE table_user (
          id INTEGER PRIMARY KEY,
          name TEXT NOT NULL,
          email TEXT NOT NULL,
          phone_number TEXT NOT NULL,
          avatar TEXT NOT NULL,
          bearer TEXT NOT NULL,
          created_at TEXT NOT NULL,
          updated_at TEXT NOT NULL
        )
      ''');
    },
  );
}

• getDatabasesPath() — the folder sqflite is allowed to write to; never hard-code a path.
• p.join(...) — builds the file path with the right separator for the current platform.
• version: 1 — bump this and add an onUpgrade callback when you later change the schema.
• onCreate — runs once, the first time the file is created. It never runs again, even if you edit the CREATE TABLE text later (Lecture 003 covers why that matters).
• INTEGER PRIMARY KEY — SQLite treats this column as an alias for its internal row id and fills it in automatically on insert when you don't provide one.

#Code Examples

#Example 1 — Proof That Sqflite Is Installed

// lib/main.dart
import 'package:flutter/material.dart';

import 'data_sqflite/database_user.dart';

Future<void> main() async {
  WidgetsFlutterBinding.ensureInitialized();
  await openUserDatabase();
  runApp(const MyApp());
}

class MyApp extends StatelessWidget {
  const MyApp({super.key});

  @override
  Widget build(BuildContext context) {
    return const MaterialApp(
      debugShowCheckedModeBanner: false,
      home: Scaffold(body: Center(child: Text('Sqflite is ready'))),
    );
  }
}

WidgetsFlutterBinding.ensureInitialized() is required because main awaits plugin code before runApp.

#Common Beginner Mistakes

1. Building the path with string concatenation ('$databasesPath/database_user.db') — works on one platform and breaks on another. Always use path's p.join.

2. Expecting onCreate to run again after editing the table — it only runs when the file does not exist yet. Delete the app's data (or bump version with an onUpgrade) to see schema changes during development.

3. Forgetting WidgetsFlutterBinding.ensureInitialized() — needed whenever main awaits plugin code before runApp.

#Best Practices

• Keep table and column names as String constants in one place — Lecture 002 shows why.
• Give every column a type and NOT NULL unless you expect missing values.
• Keep all database code in one folder; screens should not know SQL.

#Mini Challenge

Create a new project, add the three packages, run openUserDatabase() on startup, and confirm (with a print statement) that the .db file's path looks right for your platform.

#Practice Questions

1. What does sqflite save that Hive does not know how to query directly? 2. What is getDatabasesPath() for? 3. When does onCreate run? 4. Why use path's p.join instead of string concatenation?

#Practice Answers

1. Rows across named columns, searchable and filterable with SQL — not just a value under a single key.
2. It returns the folder the app is allowed to store its database file in.
3. Only the first time the database file is created.
4. It builds a correct path separator on every platform sqflite runs on.

#Key Takeaways

• Sqflite stores rows in SQL tables inside one file, opened with openDatabase.
• onCreate runs the CREATE TABLE statement once, the first time the file is created.
• All database code lives in lib/data_sqflite/.