Day 18: Building a Digital Library with SQLite
Sticky notes (`shared_preferences`) are great, but what happens when you need to store a whole collection of structured data? It’s time to upgrade from a junk drawer to a full-blown, organized library. Welcome to the world of databases with SQLite.
From Sticky Notes to a Library
Yesterday, we learned about `shared_preferences` for simple key-value pairs. It’s perfect for a single setting. But what if you wanted to save a list of 100 to-do items, each with a title, description, due date, and completion status? Using sticky notes for that would be a chaotic mess. You need a system.
SQLite is that system. It’s a powerful, self-contained, and serverless database engine that’s built into virtually every mobile device. It allows you to store large amounts of structured data in tables with rows and columns, just like a spreadsheet. With SQLite, you can query, filter, sort, and manage your data with incredible efficiency.
The Four Superpowers: CRUD
Working with any database comes down to four fundamental operations, known by the acronym **CRUD**: Create, Read, Update, and Delete. Master these four, and you can build almost any data-driven feature. We’ll use the `sqflite` package in Flutter to perform these operations.
Live Simulation: The Doggy Database
Database Records:
The database is empty. Add a dog to get started!
Add a New Dog:
The Code Behind the Curtain
Let’s look at the `sqflite` code that powers each CRUD operation. We’ll start with a simple `Dog` data model to represent our data structure.
Data Model (`Dog` class):
class Dog {
final int id;
final String name;
final int age;
Dog({required this.id, required this.name, required this.age});
// Convert a Dog into a Map. The keys must correspond to the names of the
// columns in the database.
Map<String, dynamic> toMap() {
return { 'id': id, 'name': name, 'age': age };
}
}Future<void> insertDog(Dog dog) async {
final db = await database; // Get a reference to the database.
await db.insert(
'dogs', // The table name
dog.toMap(), // The dog's data
conflictAlgorithm: ConflictAlgorithm.replace, // Replace if the dog already exists.
);
}Future<List<Dog>> dogs() async {
final db = await database;
// Query the table for all The Dogs.
final List<Map<String, dynamic>> maps = await db.query('dogs');
// Convert the List<Map<String, dynamic>> into a List<Dog>.
return List.generate(maps.length, (i) {
return Dog(
id: maps[i]['id'],
name: maps[i]['name'],
age: maps[i]['age'],
);
});
}Future<void> updateDog(Dog dog) async {
final db = await database;
await db.update(
'dogs',
dog.toMap(),
// Ensure that the Dog has a matching id.
where: 'id = ?',
// Pass the Dog's id as a whereArg to prevent SQL injection.
whereArgs: [dog.id],
);
}Future<void> deleteDog(int id) async {
final db = await database;
await db.delete(
'dogs',
where: 'id = ?',
whereArgs: [id],
);
}