DuckDB in Flutter: Super-Fast Analytics for Your Mobile App! 🚀

In mobile app development, we commonly use databases like SQLite to store and manage data. But when you need to run fast and complex analytical queries on large datasets, SQLite might not be the best option. This is where DuckDB comes into the picture!

What is DuckDB? 🤔

DuckDB is an in-process analytical database system (OLAP). Let’s break this down into simple terms:

• In-Process: This means it doesn’t require a separate server. It runs as a library right inside your Flutter application, making it extremely easy to set up and use.
• Analytical (OLAP): It is designed to execute complex queries (like SUM, AVG, GROUP BY) on large datasets very quickly. This makes it ideal for data analysis and reporting.
• Column-Oriented: Traditional databases (like SQLite) store data in ‘rows’. DuckDB stores data in ‘columns’. This makes it incredibly fast when you are performing calculations on only a few specific columns, as it doesn’t need to read unnecessary data.

In simple terms, if SQLite is a digital filing cabinet for everyday transactions, DuckDB is a super-computer with a powerful calculator for analyzing big data.

Why Use DuckDB in Flutter?

You might be wondering, “Why should I use DuckDB in my Flutter app?” Here are some key reasons:

1. Incredible Speed: It’s blazing fast for aggregations and analytical queries on large datasets.
2. No Server Required: It runs directly in your app, which simplifies deployment.
3. Direct File Query: You can directly query files like CSV or Parquet without importing them into the database first. This is a game-changing feature!
4. Offline Analytics: Users can analyze large amounts of data on their devices even without an internet connection.
5. Standard SQL Syntax: It supports standard SQL, so if you know SQL, you can easily use DuckDB.

Setting Up and Using DuckDB in a Flutter Project

Let’s now see how to use it in Flutter.

Step 1: Add the Dependency

First, add the duckdb_dart package to your pubspec.yaml file.

YAML

dependencies:flutter:sdk: flutterduckdb_dart: ^0.1.0 # Always check for the latest version

Then, run flutter pub get in your terminal to install the package in your project.

Step 2: Initialize the Database

You can open a DuckDB database in two ways:

1. In-Memory: This database exists only in RAM and is destroyed as soon as the app closes. It’s great for temporary calculations.
2. File-based: This saves the database to a file, so the data is stored permanently.

Dart

import ‘package:duckdb_dart/duckdb_dart.dart’;
void main() {
// Open an in-memory databasefinal dbInMemory = DuckDB.openInMemory();
print(‘In-memory database opened.’);

// Open a file-based database// Replace ‘path/to/my_database.db’ with your desired pathfinal dbFile = DuckDB.open(‘path/to/my_database.db’);
print(‘File-based database opened.’);

// Don’t forget to close the database when you’re done
dbInMemory.close();
dbFile.close();
}

Step 3: Querying Data

Let’s look at a simple example where we will create a table, insert data into it, and then query it.

Dart

import ‘package:duckdb_dart/duckdb_dart.dart’;
void runDuckDBExample() {
// Open the databasefinal db = DuckDB.openInMemory();

try {
// 1. Create a table db.execute(“CREATE TABLE products(id INTEGER, name VARCHAR, price REAL);”);
print(‘Table “products” created.’);

// 2. Insert data db.execute(“INSERT INTO products VALUES (1, ‘Laptop’, 85000.0), (2, ‘Mouse’, 1200.0), (3, ‘Keyboard’, 2500.0), (4, ‘Monitor’, 15000.0);”);
print(‘Data inserted.’);

// 3. Query the datafinal result = db.query(‘SELECT * FROM products WHERE price > 10000.0;’);
print(‘Query Result:’);
// Print the resultsfor (final row in result) {
print(row);
// You can also access by column name:// print(‘ID: ${row[‘id’]}, Name: ${row[‘name’]}, Price: ${row[‘price’]}’); } // 4. Analytical Query (find the average price)final avgPriceResult = db.query(‘SELECT AVG(price) as average_price FROM products;’);
print(‘Average Price: ${avgPriceResult.first[‘average_price’]}’);

} catch (e) {
print(‘An error occurred: $e’);
} finally {
// 5. Close the database
db.close();
print(‘Database closed.’);
}
}

void main() {
runDuckDBExample();
}

The Killer Feature: Direct Query on CSV Files! ⚡

One of DuckDB’s most powerful features is its ability to query CSV or Parquet files directly, without importing the data.

Suppose you have a file named sales.csv. You can query it directly like this:

Dart

// Replace ‘path/to/sales.csv’ with the correct path to your filefinal csvResult = db.query(“SELECT region, SUM(amount) as total_sales FROM ‘path/to/sales.csv’ GROUP BY region;”);
for (final row in csvResult) {
print(‘Region: ${row[‘region’]}, Total Sales: ${row[‘total_sales’]}’);
}

This feature is extremely useful for mobile apps where you need to quickly analyze large data files.

DuckDB vs. SQLite: When to Use What?

Conclusion: DuckDB and SQLite are not competitors; they solve different problems. You can even use both in the same Flutter application—SQLite for everyday transactional data and DuckDB for complex analysis.