DuckDB SQLite extension

The SQLite extension allows DuckDB to directly read and write data from a SQLite database file. The data can be queried directly from the underlying SQLite tables. Data can be loaded from SQLite tables into DuckDB tables, or vice versa.

Reading Data from SQLite

To make a SQLite file accessible to DuckDB, use the ATTACH command, for example with the bundled sakila.db file:

ATTACH 'data/db/sakila.db'AS sakila;
USE sakila;

The tables in the file can be read as if they were normal DuckDB tables, but the underlying data is read directly from the SQLite tables in the file at query time.

SHOW TABLES;

You can query the tables using SQL, e.g. using the example queries from sakila-examples.sql

SELECTcat.name category_name,
Sum(Ifnull(pay.amount, 0)) revenue
FROM category cat
LEFT JOIN film_category flm_cat
ONcat.category_id=flm_cat.category_idLEFT JOIN film fil
ONflm_cat.film_id=fil.film_idLEFT JOIN inventory inv
ONfil.film_id=inv.film_idLEFT JOIN rental ren
ONinv.inventory_id=ren.inventory_idLEFT JOIN payment pay
ONren.rental_id=pay.rental_id
GROUP BY cat.name
ORDER BY revenue DESCLIMIT5;

Opening SQLite Databases Directly

SQLite databases can also be opened directly and can be used transparently instead of a DuckDB database file. In any client, when connecting, a path to a SQLite database file can be provided and the SQLite database will be opened instead.

For example, with the shell:

$ > duckdb data/db/sakila.dbv0.9.1 401c8061c6
D SHOW tables;
┌────────────────────────┐
│ name │
│ varchar │
├────────────────────────┤
│ actor │
│ address │
│ category │
│ city │
│ country │
│ customer │
│ customer_list │
│ film │
│ film_actor │
│ film_category │
│ film_list │
│ film_text │
│ inventory │
│ language │
│ payment │
│ rental │
│ sales_by_film_category │
│ sales_by_store │
│ staff │
│ staff_list │
│ store │
├────────────────────────┤
│ 21 rows │
└────────────────────────┘

Writing Data to SQLite

In addition to reading data from SQLite, the extension also allows you to create new SQLite database files, create tables, ingest data into SQLite and make other modifications to SQLite database files using standard SQL queries.

This allows you to use DuckDB to, for example, export data that is stored in a SQLite database to Parquet, or read data from a Parquet file into SQLite.

Below is a brief example of how to create a new SQLite database and load data into it.

ATTACH 'new_sqlite_database.db'AS sqlite_db (TYPE SQLITE);
CREATETABLEsqlite_db.tbl(id INTEGER, name VARCHAR);
INSERT INTOsqlite_db.tblVALUES (42, 'DuckDB');

The resulting SQLite database can then be read into from SQLite.

$r > sqlite3 new_sqlite_database.db
SQLite version 3.39.52022-10-1420:58:05
sqlite>SELECT*FROM tbl;
id name
-- ------42 DuckDB

Many operations on SQLite tables are supported. All these operations directly modify the SQLite database, and the result of subsequent operations can then be read using SQLite.

Below is a list of supported operations.

CREATE TABLE

CREATETABLEsqlite_db.tbl(id INTEGER, name VARCHAR);

INSERT INTO

INSERT INTOsqlite_db.tblVALUES (42, 'DuckDB');

SELECT

SELECT*FROMsqlite_db.tbl;
┌───────┬─────────┐
│ id │ name │
│ int64 │ varchar │
├───────┼─────────┤
│ 42 │ DuckDB │
└───────┴─────────┘

COPY

COPY sqlite_db.tbl TO 'data.parquet';
COPY sqlite_db.tblFROM'data.parquet';

UPDATE

UPDATEsqlite_db.tblSET name='Woohoo'WHERE id=42;

DELETE

DELETEFROMsqlite_db.tblWHERE id=42;

ALTER TABLE

ALTERTABLEsqlite_db.tbl ADD COLUMN k INTEGER;

DROP TABLE

DROPTABLEsqlite_db.tbl;

CREATE VIEW

CREATEVIEWsqlite_db.v1 ASSELECT42;

Transactions

CREATETABLEsqlite_db.tmp(i INTEGER);
BEGIN;
INSERT INTOsqlite_db.tmpVALUES (42);
SELECT*FROMsqlite_db.tmp;
┌───────┐
│ i │
│ int64 │
├───────┤
│ 42 │
└───────┘
ROLLBACK;
SELECT*FROMsqlite_db.tmp;
┌────────┐
│ i │
│ int64 │
├────────┤
│ 0 rows │
└────────┘

Building & Loading the Extension

Tip:

Before building, fetch submodules with make pull

To build, type

# to build with multiple cores, use `make GEN=ninja release`
make

To run, run the bundled duckdb shell:

 ./build/release/duckdb -unsigned

Then, load the SQLite extension like so:

LOAD 'build/release/extension/sqlite_scanner/sqlite_scanner.duckdb_extension';