Retrieving Plain Values from SQLite with Flutter and Sqflite

======================================================

In this article, we’ll explore the process of retrieving plain values from an SQLite database using the Sqflite package in Flutter. We’ll start by understanding how to create a SQLite database and perform CRUD (Create, Read, Update, Delete) operations.

Creating a SQLite Database with Sqflite

The Sqflite package provides a convenient interface for interacting with SQLite databases on Android and iOS platforms. Here’s an example of how to create a new SQLite database using the sqflute_database class:

import 'package:sqflite/sqflite.dart';
import 'package:sqfluttdatabase/sqfluttdatabase.dart';

Future<void> main() async {
  // Initialize Sqflite database
  final sqfliteDatabase = await openDatabase('my_database.db', version: 1, onCreate: (db, version) async {
    await db.execute(
      'CREATE TABLE Article(id INTEGER PRIMARY KEY, title TEXT, description TEXT, url TEXT, urltoimage TEXT, publishedat TEXT)',
    );
  });

  // Perform CRUD operations
  final articles = await sqfliteDatabase.rawQuery('SELECT * FROM Article');

  // Close the database connection
  sqfliteDatabase.close();
}

Writing Data to the SQLite Database

Once we have established a connection to our SQLite database, we can write data using the execute or rawInsert methods.

// Inserting new article
final txn = await sqfliteDatabase.transaction;
await txn.rawInsert(
  'INSERT INTO Article(title, description, url, urltoimage, publishedat) VALUES("$title", "$description", "$url", "$urlToImage", "$publishedAt")',
);

Retrieving Data from the SQLite Database

After writing data to our database, we can retrieve it using the rawQuery method. We’ll demonstrate how to fetch all articles and display their titles.

// Fetching article titles
final title = await sqfliteDatabase.rawQuery('SELECT title FROM Article');

Returning Plain Values from the SQLite Database

When working with Sqflite, we often need to access values in a specific format. For example, if we want to display only the title of an article, we can use the following code:

// Retrieving plain value (title)
final title = await sqfliteDatabase.rawQuery('SELECT title FROM Article');
Text('${title[index]["title"]}')

Key Takeaways and Best Practices

Here are some key takeaways and best practices for working with Sqflite and SQLite databases in Flutter:

Always ensure to close the database connection when it’s no longer needed to avoid memory leaks.
Use transactions to execute multiple SQL queries as a single, atomic unit.
When retrieving data from the database, specify the columns you need using rawQuery or query.
Be aware of the data type and format requirements for each column.
Handle errors and exceptions when working with databases.

Conclusion

In this article, we’ve explored how to create a SQLite database, perform CRUD operations, and retrieve plain values from an SQLite database using the Sqflite package in Flutter. We’ve also discussed key takeaways and best practices for working with Sqflite databases. With this knowledge, you’ll be better equipped to handle common tasks when interacting with SQLite databases in your Flutter applications.

Additional Tips

Use await to ensure non-blocking I/O operations.
When using transactions, always commit or rollback the transaction after executing all necessary queries.
Avoid hardcoding database credentials or paths; instead, use environment variables or secure storage mechanisms.

Last modified on 2023-07-23