DuckDB.dart

Welcome to DuckDB.dart, the native Dart interface to DuckDB, a high-performance analytical database system. With DuckDB.dart, you can harness the power of DuckDB in your Dart applications across multiple platforms, including Apple (macOS, iOS), Android, Linux, Windows, and Web, delivering seamless integration and top-tier performance for your analytical workloads.

---

Table of Contents

• Introduction
• Why DuckDB.dart?
• Features
• Installation
• Getting Started
  • Basic Usage
  • Querying Data
• Advanced Examples
  • Querying a Parquet File
  • Using Window Functions
  • Working with CSV Files
• Platform Support
  • Mobile Extensions (Android/iOS)
• API Documentation
• Contributing
• License
• FAQ

---

Introduction

DuckDB.dart is the Dart interface to DuckDB, an in-process SQL OLAP database management system designed for high-performance analytical queries. Whether you're building mobile apps with Flutter, desktop software, or server-side solutions, DuckDB.dart enables fast, efficient, and versatile data analysis without requiring external database servers.

For an in-depth introduction, watch the DuckCon #5 talk from Seattle 2024: "Quack attack: Bringing DuckDB to the Dart side."

---

Why DuckDB.dart?

DuckDB.dart is an excellent choice for Dart developers needing a powerful embedded database. Here's why:

• Performance: Powered by DuckDB's vectorized query engine for lightning-fast analytical queries.
• Portability: Runs effortlessly across multiple platforms with no additional setup.
• Ease of Use: Provides a simple, Dart-native API that's intuitive for developers.
• Self-Contained: Includes DuckDB binaries, eliminating external dependencies.
• Advanced SQL: Supports a rich SQL dialect, including window functions and complex queries.

Choose DuckDB.dart for a lightweight, high-performance database solution tailored to Dart.

Features

• Native Dart API: Integrates seamlessly with Dart for a natural developer experience.
• Cross-Platform Support: Works on Apple (macOS, iOS), Android, Linux, and Windows.
• Batteries Included: Ships with DuckDB binaries—no external installations needed.
• High-Performance Queries: Leverages DuckDB's vectorized engine for optimal speed.
• Nonblocking I/O: Uses dedicated background isolates per connection for efficient, zero-copy query results.
• Direct File Access: Query CSV, JSON, Parquet, and other formats without importing data.
• Comprehensive SQL Dialect: Supports advanced SQL features like window functions and collations.

---

Installation

DuckDB.dart is available on pub.dev. Add it to your project as follows:

For Flutter Projects

Run this command:

flutter pub add dart_duckdb

This updates your pubspec.yaml:

dependencies:
  dart_duckdb: ^1.2.0

For Dart Projects

Run this command:

dart pub add dart_duckdb

This updates your pubspec.yaml:

dependencies:
  dart_duckdb: ^1.2.0

Download the latest duckdb release from duckdb.org.

In your dart code, tell the framework where the duckdb binary.

  open.overrideFor(
      OperatingSystem.macOS, 'path/to/libduckdb.dylib');

Import it

Now you can use the package:

import 'package:dart_duckdb/dart_duckdb.dart';

---

Getting Started

Basic Usage

Here's a simple example to start using DuckDB.dart:

Future<void> main() async {
  // flutter builds bundle duckdb binaries, but this can be overriden
  // open.overrideFor(OperatingSystem.macOS, 'path/to/libduckdb.dylib');

  final db = await duckdb.open(":memory:");
  final conn = await duckdb.connect(db);

  await conn.execute("CREATE TABLE users (id INTEGER, name VARCHAR)");
  await conn.execute("INSERT INTO users VALUES (1, 'Alice')");

  final result = await conn.query("SELECT * FROM users");
  for (final row in result.fetchAll()) {
    print(row);
  }

  await conn.dispose();
  await db.dispose();
}

This demonstrates opening a database, creating a table, inserting data, querying it, and closing resources.

Querying Data

Execute SQL queries and process results easily:

  // ...
  final result = await conn.query("SELECT id, name FROM users WHERE id > 0");
  for (final row in result.fetchAll()) {
    print('ID: ${row[0]}, Name: ${row[1]}');
  }

--

Advanced Examples

Querying a Parquet File

Query Parquet files directly without loading them into the database:

  final result = await conn.query(
    "SELECT * FROM 'data/large_dataset.parquet' LIMIT 10",
  );
  for (final row in result.fetchAll()) {
    print(row);
  }

Using Window Functions

Perform advanced analytics with window functions:

  await conn.execute(
    "CREATE TABLE sales (id INTEGER, amount DECIMAL, date DATE)",
  );
  await conn.execute(
    "INSERT INTO sales VALUES (1, 100.0, '2023-01-01'), (2, 150.0, '2023-01-02'), (3, 200.0, '2023-01-03')",
  );

  final result = await conn.query("""
  SELECT id, amount, date,
         SUM(amount) OVER (ORDER BY date) AS running_total
  FROM sales
""");
  for (final row in result.fetchAll()) {
    print(
      'ID: ${row[0]}, Amount: ${row[1]}, Date: ${row[2]}, Running Total: ${row[3]}',
    );
  }

Working with CSV Files

Query CSV files directly:

  final result = await conn.query(
    "SELECT * FROM 'data/sales_data.csv' WHERE quantity > 10",
  );
  for (final row in result.fetchAll()) {
    print(row);
  }

Explore more examples in the examples directory.

---

Platform Support

DuckDB.dart supports the following platforms:

• 🍎 Apple (macOS, iOS)
• 🤖 Android
• 🐧 Linux
• 🪟 Windows
• 🕸️ Web

Web setup

For Flutter web builds, add the following to web/index.html inside the <head> to load DuckDB WASM and Apache Arrow:

  <script type="importmap">
    {
      "imports": {
        "apache-arrow": "https://cdn.jsdelivr.net/npm/apache-arrow@17.0.0/+esm"
      }
    }
  </script>
  <script type="module">
    import * as duckdb from "https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.29.1-dev222.0/+esm";
    import * as arrow from "apache-arrow";
    window.duckdbWasmReady = new Promise((resolve) => {
      window.duckdbduckdbWasm = duckdb;
      window.ArrowTable = arrow.Table;
      resolve();
    });
  </script>

See platform-specific details in the Building Instructions

Mobile Extensions (Android/iOS)

This fork provides pre-built DuckDB binaries for Android and iOS with statically linked extensions. Below is the compatibility analysis for DuckDB core extensions on mobile platforms:

Extension	Dependencies	Android	iOS	Notes
Included in builds
icu	ICU library (bundled)	✅	✅	Unicode/collation support
json	None (in-tree)	✅	✅	JSON parsing and querying
parquet	None (in-tree)	✅	✅	Parquet file format
httpfs	OpenSSL, cURL, nghttp2	✅	✅	HTTP/HTTPS/S3 access (requires openssl_for_ios_and_android)
fts	None	✅	✅	Full-text search
inet	None	✅	✅	IPv4/IPv6 address handling
vss	None	✅	✅	Vector similarity search
autocomplete	None (in-tree)	✅	✅	SQL autocomplete support
ducklake	None	✅	✅	DuckLake lakehouse format support
sqlite_scanner	None	✅	✅	Read/write SQLite files
postgres_scanner	OpenSSL (bundled)	❌	✅	Connect to PostgreSQL databases (Android: libpq NDK issues)
Community extensions
h3	None (bundled)	✅	✅	H3 hexagonal hierarchical geospatial indexing
Easy to add
tpch	None (in-tree)	✅ Easy	TPC-H benchmark data generator
tpcds	None (in-tree)	✅ Easy	TPC-DS benchmark data generator
Medium complexity
excel	expat, minizip-ng	⚠️ Medium	Requires cross-compiling dependencies
avro	avro-c	⚠️ Medium	Single C library needs cross-compile
delta	OpenSSL	⚠️ Medium	Already have OpenSSL from httpfs
mysql	libmariadb	⚠️ Medium	MariaDB connector needs cross-compile
Complex - Not recommended
aws	aws-sdk-cpp, curl, openssl, zlib	❌ Complex	AWS SDK is huge C++ library
azure	azure-sdk-cpp (multiple libs)	❌ Complex	Azure SDK is huge C++ library
iceberg	avro-c, curl, openssl, roaring, aws-sdk-cpp	❌ Complex	Heavy deps including AWS SDK
spatial	GEOS, PROJ, GDAL, sqlite3, curl, openssl	❌ Very Complex	GDAL is notoriously difficult to cross-compile
jemalloc	System-specific	⚠️ Platform issues	May have issues on mobile

Pre-built binaries are available from GitHub Releases.

Adding Community Extensions

Community extensions can be easily added to the build by editing the COMMUNITY_EXTENSIONS variable in the workflow files:

# Format: "name|repo|branch" separated by spaces
COMMUNITY_EXTENSIONS: "h3|isaacbrodsky/h3-duckdb|main"

# To add more extensions:
COMMUNITY_EXTENSIONS: "h3|isaacbrodsky/h3-duckdb|main newext|owner/repo|branch"

The build system will automatically clone each extension with its submodules and generate the CMake configuration. See the h3-duckdb extension as an example.

Note: Some community extensions like a5 require additional toolchains (e.g., Rust) for cross-compilation and are not currently supported.

---

API Documentation

For detailed API information, visit the API Documentation.

---

If you have any questions, feedback or ideas, feel free to create an issue. If you enjoy this project, I'd appreciate your 🌟 on GitHub.

---

FAQ

Q: Can it handle large datasets?
A: Yes, DuckDB excels at processing large datasets efficiently, including direct file queries.

Q: Is it production-ready?
A: Yes, built on the stable DuckDB engine, it's suitable for production use.

Q: How do I report a bug?
A: Open an issue on the GitHub issue tracker.

---

Sponsors

DuckDB.dart is proudly Sponsored by TigerEye 🐅

---

Contributing

We'd love your contributions! Here's how to get started:

1. Fork the repository.
2. Create a new branch for your feature or bug fix.
3. Make your changes and commit them with descriptive messages.
4. Push your changes to your fork.
5. Submit a pull request with a detailed description of your changes.