SQLite with sqflite package in Flutter - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the sqflite package used for in Flutter?

The sqflite package is used to work with SQLite databases in Flutter apps. It allows you to store, query, update, and delete data locally on the device.

beginner

How do you open or create a database using sqflite?

You use the openDatabase() function from sqflite. It opens an existing database or creates a new one if it doesn't exist, and you can provide a path and version.

intermediate

What is the purpose of the onCreate callback in openDatabase?

The onCreate callback runs when the database is created for the first time. It is used to create tables and set up the initial database structure.

beginner

How do you insert data into a SQLite table using sqflite?

You use the insert() method on the database object, passing the table name and a map of column names to values.

intermediate

What is the difference between rawQuery() and query() methods in sqflite?

rawQuery() lets you write SQL statements as strings for complex queries, while query() provides a simpler, structured way to query tables without writing raw SQL.

Which function is used to open or create a SQLite database in Flutter with sqflite?

A initDatabase()

B createDatabase()

C openDatabase()

D startDatabase()

openDatabase() is the correct function to open or create a database using sqflite.

What does the onCreate callback do when opening a database?

A Creates tables and initializes the database

B Updates the database version

C Closes the database

D Deletes the database

onCreate is called only once when the database is first created to set up tables and initial data.

How do you insert a new row into a table using sqflite?

A db.insert('table', dataMap)

B db.add('table', dataMap)

C db.create('table', dataMap)

D db.put('table', dataMap)

The insert() method is used to add new rows to a table.

Which method allows you to run raw SQL queries in sqflite?

A execute()

B rawQuery()

C query()

D runSQL()

rawQuery() lets you run raw SQL statements as strings.

What type of data structure is used to represent a row when inserting data with sqflite?

A List

B String

C Set

D Map

A Map with column names as keys and values as data is used to represent a row.

Explain the steps to create and open a SQLite database using the sqflite package in Flutter.

Think about how you tell sqflite where the database is and what to do when it is new.

Describe how to insert and query data from a SQLite database using sqflite.

Consider how you add data and then get it back in a Flutter app.

Practice

1. What is the primary purpose of the sqflite package in Flutter?

easy

A. To handle network requests and APIs

B. To manage SQLite databases for storing app data locally

C. To create user interfaces with widgets

D. To manage app navigation between screens

Solution

1. Step 1: Understand sqflite package role

   The sqflite package is designed to work with SQLite databases in Flutter apps.

2. Step 2: Identify main functionality

   It helps create, open, and manage local databases for storing app data persistently.

3. Final Answer:

   To manage SQLite databases for storing app data locally -> Option B

4. Quick Check:

   sqflite = SQLite database management [OK]

Hint: sqflite = SQLite database helper in Flutter [OK]

Common Mistakes:

• Confusing sqflite with UI or navigation packages
• Thinking sqflite handles network or API calls
• Assuming sqflite is for animations or gestures

2. Which of the following is the correct way to open a database using sqflite?

easy

A. db.open('my_db.db');

B. openDB('my_db.db');

C. await openDatabase('my_db.db');

D. Database.open('my_db.db');

Solution

1. Step 1: Recall sqflite openDatabase syntax

   The sqflite package provides an async function called openDatabase to open or create a database.

2. Step 2: Match correct syntax

   The correct usage is: await openDatabase('filename'); other options are not valid sqflite methods.

3. Final Answer:

   await openDatabase('my_db.db'); -> Option C

4. Quick Check:

   openDatabase is async and correct method [OK]

Hint: Use await openDatabase('filename') to open DB [OK]

Common Mistakes:

• Using non-existent methods like openDB or Database.open
• Forgetting to await the async openDatabase call
• Calling openDatabase without parentheses

3. Given this code snippet, what will be the output printed?

var db = await openDatabase('test.db');
await db.execute('CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT)');
await db.insert('users', {'id': 1, 'name': 'Alice'});
var result = await db.query('users');
print(result);

medium

A. SyntaxError

B. []

C. null

D. [{'id': 1, 'name': 'Alice'}]

Solution

1. Step 1: Analyze database operations

   The code opens a database, creates a table if missing, inserts one user, then queries all users.

2. Step 2: Understand query result

   The query returns a list of maps representing rows. Since one user was inserted, result contains one map with id and name.

3. Final Answer:

   [{'id': 1, 'name': 'Alice'}] -> Option D

4. Quick Check:

   Insert then query returns inserted row [OK]

Hint: Insert then query returns list with inserted rows [OK]

Common Mistakes:

• Expecting empty list if table was just created
• Confusing query result with null or error
• Not awaiting async calls causing runtime errors

4. What is wrong with this sqflite code snippet?

var db = await openDatabase('app.db');
await db.execute('CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT)');
await db.insert('users', {'id': 1, 'name': 'Bob'});
await db.insert('users', {'id': 1, 'name': 'Carol'});

medium

A. It tries to insert two rows with the same primary key, causing a conflict error

B. The table creation SQL syntax is incorrect

C. The insert method requires a third argument for conflictAlgorithm

D. openDatabase must be called with a version number

Solution

1. Step 1: Check table and inserts

   The table has id as PRIMARY KEY. Two inserts use the same id=1.

2. Step 2: Understand primary key constraint

   Inserting duplicate primary keys causes a conflict error unless handled explicitly.

3. Final Answer:

   It tries to insert two rows with the same primary key, causing a conflict error -> Option A

4. Quick Check:

   Duplicate primary keys cause insert conflict error [OK]

Hint: Primary key must be unique; duplicates cause errors [OK]

Common Mistakes:

• Assuming insert silently overwrites existing rows
• Thinking version is mandatory for openDatabase
• Believing insert always needs conflictAlgorithm argument

5. You want to update a user's name in the database using sqflite. Which code snippet correctly updates the user with id = 2 to have the name 'David'?

hard

A. await db.update('users', {'name': 'David'}, where: 'id = ?', whereArgs: [2]);

B. await db.insert('users', {'id': 2, 'name': 'David'});

C. await db.execute('UPDATE users SET name = 'David' WHERE id = 2');

D. await db.update('users', {'name': 'David'}, where: 'id = 2');

Solution

1. Step 1: Understand update method parameters

   The update method requires the table name, values map, and a where clause with arguments to avoid SQL injection.

2. Step 2: Check correct usage of where and whereArgs

   await db.update('users', {'name': 'David'}, where: 'id = ?', whereArgs: [2]); uses where: 'id = ?' and whereArgs: [2], which is the safe and correct way.

3. Final Answer:

   await db.update('users', {'name': 'David'}, where: 'id = ?', whereArgs: [2]); -> Option A

4. Quick Check:

   Use whereArgs with ? placeholders for safe updates [OK]

Hint: Use where with ? and whereArgs list for updates [OK]

Common Mistakes:

• Using raw SQL string without parameter binding
• Using insert instead of update to change data
• Omitting whereArgs causing errors or unsafe queries