SQLite Scanner Extension
Version 0.9.2

The sqlite extension allows DuckDB to directly read data from a SQLite database file. The data can be queried directly from the underlying SQLite tables, or read into DuckDB tables.

Usage

To make a SQLite file accessible to DuckDB, use the ATTACH statement, which supports read & write, or the older sqlite_attach function

For example with the bundled sakila.db file:

ATTACH 'sakila.db' (TYPE sqlite);
-- or
CALL sqlite_attach('sakila.db');

The tables in the file are registered as views in DuckDB, you can list them as follows:

PRAGMA show_tables;
┌────────────────────────┐
│          name          │
├────────────────────────┤
│ 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                  │
└────────────────────────┘

Then you can query those views normally using SQL, e.g., using the example queries from sakila-examples.sql

SELECT
  cat.name category_name,
  sum(ifnull(pay.amount, 0)) revenue
FROM category cat
LEFT JOIN film_category flm_cat
       ON cat.category_id = flm_cat.category_id
LEFT JOIN film fil
       ON flm_cat.film_id = fil.film_id
LEFT JOIN inventory inv
       ON fil.film_id = inv.film_id
LEFT JOIN rental ren
       ON inv.inventory_id = ren.inventory_id
LEFT JOIN payment pay
       ON ren.rental_id = pay.rental_id
GROUP BY cat.name
ORDER BY revenue DESC
LIMIT 5;

Querying Individual Tables

Instead of attaching, you can also query individual tables using the sqlite_scan function.

SELECT * FROM sqlite_scan('sakila.db', 'film');

Data Types

SQLite is a weakly typed database system. As such, when storing data in a SQLite table, types are not enforced. The following is valid SQL in SQLite:

CREATE TABLE numbers(i INTEGER);
INSERT INTO numbers VALUES ('hello');

DuckDB is a strongly typed database system, as such, it requires all columns to have defined types and the system rigorously checks data for correctness.

When querying SQLite, DuckDB must deduce a specific column type mapping. DuckDB follows SQLite’s type affinity rules with a few extensions.

  1. If the declared type contains the string “INT” then it is translated into the type BIGINT
  2. If the declared type of the column contains any of the strings “CHAR”, “CLOB”, or “TEXT” then it is translated into VARCHAR.
  3. If the declared type for a column contains the string “BLOB” or if no type is specified then it is translated into BLOB.
  4. If the declared type for a column contains any of the strings “REAL”, “FLOA”, “DOUB”, “DEC” or “NUM” then it is translated into DOUBLE.
  5. If the declared type is “DATE”, then it is translated into DATE.
  6. If the declared type contains the string “TIME”, then it is translated into TIMESTAMP.
  7. If none of the above apply, then it is translated into VARCHAR.

As DuckDB enforces the corresponding columns to contain only correctly typed values, we cannot load the string “hello” into a column of type BIGINT. As such, an error is thrown when reading from the “numbers” table above:

Error: Mismatch Type Error: Invalid type in column "i": column was declared as integer, found "hello" of type "text" instead.

This error can be avoided by setting the sqlite_all_varchar option:

SET GLOBAL sqlite_all_varchar=true;

When set, this option overrides the type conversion rules described above, and instead always converts the SQLite columns into a VARCHAR column. Note that this setting must be set before sqlite_attach is called.

Running More Than Once

If you want to run the sqlite_scan procedure more than once in the same DuckDB session, you’ll need to pass in the overwrite flag, as shown below:

CALL sqlite_attach('sakila.db', overwrite=true);

Loading the Extension

The SQLite Scanner extension is by default installed and loaded on first use. If you prefer to do so explicitly, run the following commands:

INSTALL sqlite;
LOAD sqlite;

GitHub Repository

GitHub

Search Shortcut cmd + k | ctrl + k