sqflite
- Dart course
- Advanced
- Lesson 43/83
- 5 min
Learn SQLite with sqflite.
sqflite
sqflite is an embedded SQLite database package for Dart and Flutter. It stores data locally in a file; no server needed. It offers an async API: you open with openDatabase and run SQL with db.execute.
Example: final db = await openDatabase('notes.db'); opens a database. await db.execute('CREATE TABLE notes (...)'); creates a table. All calls are awaited; so main is usually async.
Database setup
When setting up a database you first open with openDatabase, then create a table with db.execute. The table structure is written in SQL: CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT, body TEXT).
When done you close the connection with await db.close(). Always closing is a good habit to avoid resource leaks. In this lesson we will write a helper function setupDb; we will reuse it in the next lessons.
Practice Database setup function
Add import 'package:sqflite/sqflite.dart';. Define Future<Database> setupDb() async { ... }: open with openDatabase('notes.db'), create the table with db.execute('CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT, body TEXT)') and return db. In main add print('sqflite ready: notes.db') and print('Table: notes (id, title, body)'), then call await setupDb().
Starter code
// import the sqflite package
// define setupDb (open notes.db, create notes table)
void main() async {
// print sqflite ready message and table info
}Sample solution
import 'package:sqflite/sqflite.dart';
Future<Database> setupDb() async {
final db = await openDatabase('notes.db');
await db.execute('CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT, body TEXT)');
return db;
}
void main() async {
print('sqflite ready: notes.db');
print('Table: notes (id, title, body)');
await setupDb();
}Expected output
sqflite ready: notes.db Table: notes (id, title, body)
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What does sqflite provide?
- Web server
- Cloud storage
- Embedded SQLite Correct answer
- In-memory cache
Explanation: sqflite provides an embedded SQLite database.
2 Opening a database
Future<Database> setupDb() async {
final db = await ____('notes.db');
await db.execute('CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT, body TEXT)');
return db;
}Future<Database> setupDb() async {
final db = await openDatabase('notes.db');
await db.execute('CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT, body TEXT)');
return db;
}Explanation: openDatabase('notes.db') opens the database.
3 Match the sqflite parts
| Term | Meaning |
|---|---|
sqflite |
Embedded SQLite |
openDatabase |
Opens the database |
db.execute |
Runs SQL |
db.close |
Closes the connection |