SQLite with sqflite package in Flutter - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ ui_behavior

intermediate

What is the output of this Flutter widget using sqflite?

Consider this Flutter widget that fetches and displays a list of names from a SQLite database using sqflite. What will the UI show after the data is loaded?

Flutter

class NameList extends StatefulWidget {
  @override
  _NameListState createState() => _NameListState();
}

class _NameListState extends State<NameList> {
  List<String> names = [];

  @override
  void initState() {
    super.initState();
    loadNames();
  }

  Future<void> loadNames() async {
    final db = await openDatabase(':memory:');
    await db.execute('CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT)');
    await db.insert('people', {'name': 'Alice'});
    await db.insert('people', {'name': 'Bob'});
    final result = await db.query('people');
    setState(() {
      names = result.map((row) => row['name'] as String).toList();
    });
  }

  @override
  Widget build(BuildContext context) {
    return ListView(
      children: names.map((name) => Text(name)).toList(),
    );
  }
}

A A runtime error because the database is not opened correctly.

B An empty screen because the database is in memory and cleared immediately.

C A scrollable list showing two Text widgets: 'Alice' and 'Bob'.

D A single Text widget showing 'AliceBob' concatenated.

💡 Hint

Remember that ':memory:' creates a temporary database but it exists during the app runtime.

The code creates an in-memory database, inserts two names, queries them, and updates the UI. The ListView shows two Text widgets with 'Alice' and 'Bob'.

📝 Syntax

intermediate

What error does this sqflite query code produce?

Look at this sqflite query code snippet. What error will it raise?

Flutter

final db = await openDatabase('my.db');
final result = await db.query('users', where: 'id = ?', whereArgs: [5]);

A No error, query runs successfully.

B RuntimeError because the database file does not exist.

C SyntaxError due to wrong SQL syntax.

D TypeError because whereArgs expects a list but got an int.

💡 Hint

Check the type of whereArgs parameter.

The whereArgs parameter must be a list of arguments, but here it is given as a single int, causing a TypeError.

❓ lifecycle

advanced

When should you close the sqflite database in a Flutter app?

In a Flutter app using sqflite, when is the best time to close the database connection to avoid resource leaks?

A Only when the app is terminated or disposed of the database provider.

B Immediately after each query to free resources.

C Never close it; sqflite manages it automatically.

D After every screen navigation to a new page.

💡 Hint

Think about the app lifecycle and resource management.

Closing the database too early causes errors. The best practice is to close it when the app or the database provider is disposed to keep it open while needed.

🔧 Debug

advanced

Why does this sqflite insert not add data to the table?

This code runs without errors but the data is not saved. Why?

Flutter

final db = await openDatabase(':memory:');
await db.execute('CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, name TEXT)');
await db.insert('items', {'name': 'Item1'});
final count = Sqflite.firstIntValue(await db.rawQuery('SELECT COUNT(*) FROM items'));
print(count);

A The insert is missing a transaction, so it is rolled back.

B The database is opened fresh each time, so previous data is lost.

C The database is opened but not awaited properly, so insert fails silently.

D The table creation SQL is incorrect, so insert does nothing.

💡 Hint

Check how the database file is opened and if it persists data.

If the database is opened fresh each time without a persistent path, data is lost between runs. Using a fixed path ensures data persists.

🧠 Conceptual

expert

Which option correctly describes sqflite transactions?

In sqflite, what is the main benefit of using transactions for multiple database operations?

A Transactions ensure all operations succeed or all fail, keeping data consistent.

B Transactions speed up queries by caching results automatically.

C Transactions allow running queries in parallel threads safely.

D Transactions automatically backup the database after each operation.

💡 Hint

Think about what happens if one operation in a group fails.

Transactions group operations so either all succeed or none do, preventing partial updates and keeping data consistent.