Attaching to SQLite databases

The SQLite extension can be installed and loaded by running the following commands using the Kuzu CLI or your preferred language client API:

INSTALL sqlite;
LOAD sqlite;

1. Create a SQLite database

To illustrate the usage of this extension, we create a sample SQLite database of university students in Python.

import sqlite3
conn = sqlite3.connect('university.db')
cursor = conn.cursor()

cursor.execute("CREATE TABLE IF NOT EXISTS person (name VARCHAR, age INTEGER);")
cursor.execute("INSERT INTO person (name, age) VALUES ('Alice', 30);")
cursor.execute("INSERT INTO person (name, age) VALUES ('Bob', 27);")
cursor.execute("INSERT INTO person (name, age) VALUES ('Carol', 19);")
cursor.execute("INSERT INTO person (name, age) VALUES ('Dan', 25);")
conn.commit()
conn.close()

2. Attach database

ATTACH [DB_PATH] AS [alias] (dbtype sqlite, skip_unsupported_table = OPTION)

• DB_PATH: Path to the SQLite database instance (can be relative or absolute path)
• alias: Database alias to use in Kuzu - If not provided, the database name from DuckDB will be used. When attaching multiple databases, it’s recommended to use aliasing.
• skip_unsupported_table: Whether Kuzu should throw an exception or skip when encountering a table with unsupported datatype. Supported options: TRUE or FALSE.

The below example shows how the university.db SQLite database can be attached to Kuzu using the alias uw:

ATTACH 'university.db' AS uw (dbtype sqlite);

3. Data type mapping from SQLite to Kuzu

The table below shows the mapping from SQLite’s type to Kuzu’s type:

SQLite Storage Class / Datatype	Corresponding Data Type in Kuzu
NULL	BLOB
INTEGER	INT64
REAL	DOUBLE
TEXT	STRING
BLOB	BLOB
BOOLEAN	INT64
DATE	DATE
TIME	TIMESTAMP

Note: Sqlite uses a dynamic type system, meaning that a column in sqlite can store values with different types. The option: sqlite_all_varchar_option is provided to scan such columns in Kuzu. Usage:

`CALL sqlite_all_varchar_option=<OPTION>`

If sqlite_all_varchar_option is set to true, all sqlite columns will be treated and scanned as VAR_CHAR columns. If sqlite_all_varchar_option is set to false, trying to scan a column with values incompatible with the specified data type will result in a runtime exception.

4. Scan from SQLite tables

Finally, we can utilize the LOAD FROM statement to scan the person table.

LOAD FROM uw.person
RETURN *

Result:

---------------
| name  | age |
---------------
| Alice | 30  |
---------------
| Bob   | 27  |
---------------
| Carol | 19  |
---------------
| Dan   | 25  |
---------------

5. USE: Reference database without alias

You can use the USE statement for attached databases to use a default database name for future operations. This can be used when reading from an attached database to avoid specifying the full database name as a prefix to the table name.

Consider the same attached database as above:

ATTACH 'university.db' AS uw (dbtype duckdb);

Instead of defining the database name for each subsequent clause like this:

LOAD FROM uw.person
RETURN *

You can do:

USE uw;
LOAD FROM person
RETURN *

6. Copy data from SQLite tables

One important use case of the external RDBMS extensions is to facilitate seamless data transfer from the external RDBMS to Kuzu. In this example, we continue using the university.db database created in the last step, but this time, we copy the data and persist it to Kuzu. This is done with the COPY FROM statement. Here is an example:

We first create a Person table in Kuzu. In this example we will make Person have the same schema as the one defined in the external RDBMS.

CREATE NODE TABLE Person (name STRING, age INT32, PRIMARY KEY(name));

When the schemas are the same, we can copy the data from the external DBMS table to the Kuzu table simply as follows.

COPY Person FROM uw.person;

If the schemas are not the same, e.g., Person contains only name property while the external uw.person contains name and age, we can still use COPY FROM but with a subquery as follows:

COPY Person FROM (LOAD FROM uw.person RETURN name);

7. Query the data in Kuzu

Finally, we can verify the data in the Person table in Kuzu.

MATCH (p:Person) RETURN p.*;

Result:

------------------
| p.name | p.age |
------------------
| Alice  | 30    |
------------------
| Bob    | 27    |
------------------
| Carol  | 19    |
------------------
| Dan    | 25    |
------------------

8. Clear attached database schema cache

To avoid redundantly retrieving schema information from attached databases, Kuzu maintains a schema cache including table names and their respective columns and types. Should modifications occur in the schema via an alternate connection to attached RDBMSs, such as creation or deletion of tables, the cached schema data may become obsolete. You can use the clear_attached_db_cache() function to refresh cached schema information in such cases.

CALL clear_attached_db_cache()

Note: If you have attached to databases from different RDBMSs, say Postgres, DuckDB, and Sqlite, this call will clear the cache for all of them.

9. Detach database

To detach a database, use DETACH [ALIAS] as follows:

DETACH uw