Attaching to external RDBMSs

Kùzu supports directly scanning from a variety of relational databases using the LOAD FROM statement. Because RDBMSs are a common source of structured data in enterprises, the purpose behind this set of Kùzu extensions is currently twofold: (1) to allow users to scan data from external RDBMSs without having to copy it to a graph database; and (2) if data will be copied over from an RDBMS, this feature also simplifies the pipeline for copying data from an external RDBMS into Kùzu tables.

The currently available relational database extensions, as well as their minimum required versions, are shown below:

Extension Name	Description	Minimum Version
duckdb	Scan from an attached DuckDB database	0.10.0
postgres	Scan from an attached PostgreSQL database	14.0
sqlite	Scan from an attached SQLite database	3.3.0

Usage

Click through each tab in this section to see the example usage for your extension of choice.

DuckDB

The DuckDB extension can be installed and loaded by running the following commands using the Kùzu CLI or your preferred language client API:

INSTALL duckdb;
LOAD EXTENSION duckdb;

1. Create a DuckDB database

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

import duckdb

conn = duckdb.connect('university.db')

# Insert data to person table
conn.execute("CREATE TABLE IF NOT EXISTS person (name VARCHAR, age INTEGER);")
conn.execute("INSERT INTO person values ('Alice', 30);")
conn.execute("INSERT INTO person values ('Bob', 27);")
conn.execute("INSERT INTO person values ('Carol', 19);")
conn.execute("INSERT INTO person values ('Dan', 25);")

2. Attach database

ATTACH [DB_PATH] AS [alias] (dbtype duckdb, skip_unsupported_table = OPTION, schema = SCHEMA_NAME)

• DB_PATH: Path to the DuckDB database instance (can be a relative/absolute/https/s3 path)
• alias: Database alias to use in Kùzu - 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 Kùzu should throw an exception or skip when encountering a table with unsupported datatype. Supported options are: TRUE/FALSE. Default is FALSE.
• schema: The name of the schema in duckdb to attach. Kùzu attaches to the main schema of duckdb by default.

The below example shows how the university.db DuckDB database can be attached to Kùzu using the alias uw:

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

Note: To attach a remote DuckDB database hosted on S3, users may have to configure the following options:

Option name	Description
s3_access_key_id	S3 access key id
s3_secret_access_key	S3 secret access key
s3_endpoint	S3 endpoint
s3_url_style	Uses S3 url style (should either be vhost or path)
s3_region	S3 region

Or through environment variables:

Setting	System environment variable
S3 key ID	S3_ACCESS_KEY_ID
S3 secret key	S3_SECRET_ACCESS_KEY
S3 endpoint	S3_ENDPOINT
S3 region	S3_REGION
S3 url style	S3_URL_STYLE

SET S3_URL_STYLE='VHOST';
ATTACH 's3://duckdb-blobs/databases/stations.duckdb' as station (dbtype duckdb);
LOAD FROM station.stations RETURN count(*);

Result:

┌──────────────┐
│ COUNT_STAR() │
│ INT64        │
├──────────────┤
│ 578          │
└──────────────┘

3. Data type mapping from DuckDB to Kùzu

The table below shows the mapping from duckdb’s type to Kùzu’s type:

Data type in DuckDB	Corresponding data type in Kùzu
BIGINT	INT64
BIT	UNSUPPORTED
BLOB	BLOB
BOOLEAN	BOOL
DATE	DATE
DECIMAL(prec, scale)	DECIMAL(prec, scale)
DOUBLE	DOUBLE
FLOAT	FLOAT
HUGEINT	INT128
INTEGER	INT32
INTERVAL	INTERVAL
SMALLINT	INT16
TIME	UNSUPPORTED
TIMESTAMP WITH TIME ZONE	UNSUPPORTED
TIMESTAMP	TIMESTAMP
TINYINT	INT8
UBIGINT	UINT64
UHUGEINT	UNSUPPORTED
UINTEGER	UINT32
USMALLINT	UINT16
UTINYINT	UINT8
UUID	UUID
VARCHAR	STRING
ENUM	UNSUPPORTED
ARRAY	ARRAY
LIST	LIST
MAP	MAP
STRUCT	STRUCT
UNION	UNION

4. Scan from DuckDB tables

Finally, we can utilize the LOAD FROM statement to scan the person table. Note that you need to prefix the external person table with the database alias (in our example uw). See the USE statement which allows you to skip this prefixing for a specific default database.

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 DuckDB tables

One important use case of the external RDBMS extensions is to facilitate seamless data transfer from the external RDBMS to Kùzu. 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 Kùzu. This is done with the COPY FROM statement. Here is an example:

We first create a Person table in Kùzu. 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 Kùzu 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 Kùzu

Finally, we can verify the data in the Person table in Kùzu.

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, Kùzu 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.

8. Detach database

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

DETACH uw

Postgres

The PostgreSQL extension can be installed and loaded by running the following commands using the Kùzu CLI or your preferred language client API:

INSTALL postgres;
LOAD EXTENSION postgres;

Set up a PostgreSQL server via Docker

During local development, it’s convenient to set up a PostgreSQL server using Docker. Run the following command to start a PostgreSQL server locally:

docker run --name kuzu-postgres -e POSTGRES_PASSWORD=testpassword -p 5432:5432 --rm postgres:latest

Note that the storage volume for this database is not persistent and will be deleted once the container is stopped. Moreover, the password in this case is provided via plain text, which is not recommended in a real use case, so the below example is for testing purposes only.

1. Create a sample PostgreSQL database

To illustrate the usage of the extension, we create a sample Postgres database of university students. We will use asyncpg, an asynchronous PostgreSQL client library for Python, to create the database and insert some sample data via a Python script. This step assumes you have run pip install asyncpg to install the library, and are using Python 3.10+.

import asyncio
import asyncpg

async def main():
    conn = await asyncpg.connect('postgresql://postgres:testpassword@localhost:5432/postgres')
    # Create and insert data to a new table
    try:
        await conn.execute("CREATE TABLE IF NOT EXISTS person (name VARCHAR, age INTEGER);")
        await conn.execute("INSERT INTO person (name, age) VALUES ('Alice', 30)")
        await conn.execute("INSERT INTO person (name, age) VALUES ('Bob', 27)")
        await conn.execute("INSERT INTO person (name, age) VALUES ('Carol', 19)")
        await conn.execute("INSERT INTO person (name, age) VALUES ('Dan', 25)")
    except asyncpg.exceptions.DuplicateTableError:
        print(f"Table already exists, skipping creation and insertion...")
    # Check results
    print(await conn.fetch("SELECT * FROM person"))

asyncio.run(main())

2. Attach database

ATTACH [PG_CONNECTION_STRING] AS [alias] (dbtype postgres, skip_unsupported_table = OPTION, schema = SCHEMA_NAME)

• skip_unsupported_table: Whether Kùzu should throw an exception or skip when encountering a table with unsupported datatype. Supported options are: TRUE or FALSE. Default is FALSE.
• schema: The name of the schema in Postgres to attach. Kùzu attaches to the public schema of postgres by default.

The below example shows how the university PostgreSQL database can be attached to Kùzu using the alias uw:

ATTACH 'dbname=university user=postgres host=localhost password=testpassword port=5432' AS uw (dbtype postgres);

The ATTACH statement requires the following parameters:

• PG_CONNECTION_STRING: PostgreSQL connection string with the necessary parameters
• alias: Database alias to use in Kùzu - If not provided, the database name from PostgreSQL will be used. When attaching multiple databases, it’s recommended to use aliasing.

The below table lists some common connection string parameters:

Parameter	Description	Default
dbname	Database name	[user defined]
host	Host IP address	localhost
user	Postgres username	postgres
password	Postgres password	[empty]
port	Port number	5432

3. Data type mapping from PostgreSQL to Kùzu

The table below shows the mapping from PostgreSQL’s type to Kùzu’s type:

PostgreSQL Data Type	Corresponding Data Type in Kùzu
bigint (int8)	INT64
bigserial (serial8)	INT64
bit [ (n) ]	STRING
bit varying [ (n) ] (varbit [ (n) ])	STRING
boolean (bool)	BOOL
box	DOUBLE[]
bytea	BLOB
character [ (n) ] (char [ (n) ])	STRING
character varying [ (n) ] (varchar [ (n)])	STRING
cidr	STRING
circle	DOUBLE[]
date	DATE
double precision (float8)	DOUBLE
inet	STRING
integer (int, int4)	INT32
interval [ fields ] [ (p) ]	INTERVAL
json	JSON
line	DOUBLE[]
lseg	DOUBLE[]
macaddr	STRING
macaddr8	STRING
money	STRING
numeric [ (p, s) ] (decimal [ (p, s) ])	DECIMAL
path	DOUBLE[]
pg_lsn	STRING
pg_snapshot	STRING
point	STRUCT(x DOUBLE, y DOUBLE)
polygon	DOUBLE[]
real (float4)	FLOAT
smallint (int2)	INT16
smallserial (serial2)	INT16
serial (serial4)	INT32
text	STRING
time [ (p) ] [ without time zone ]	UNSUPPORTED
time [ (p) ] with time zone (timetz)	UNSUPPORTED
timestamp [ (p) ] [ without time zone ]	TIMESTAMP
timestamp [ (p) ] with time zone (timestamptz)	UNSUPPORTED
tsquery	STRING
tsvector	STRING
txid_snapshot	STRING
uuid	UUID
xml	STRING

4. Scan from PostgreSQL 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' AS uw (dbtype postgres);

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 PostgreSQL tables

One important use case of the external RDBMS extensions is to facilitate seamless data transfer from the external RDBMS to Kùzu. 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 Kùzu. This is done with the COPY FROM statement. Here is an example:

We first create a Person table in Kùzu. 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 Kùzu 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 Kùzu

Finally, we can verify the data in the Person table in Kùzu.

MATCH (p:Person) RETURN p.*;

Result:

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

8. Clear attached database schema cache

To avoid redundantly retrieving schema information from attached databases, Kùzu 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

SQLite

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

INSTALL sqlite;
LOAD EXTENSION 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 Kùzu - 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 Kùzu 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 Kùzu using the alias uw:

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

3. Data type mapping from SQLite to Kùzu

The table below shows the mapping from SQLite’s type to Kùzu’s type:

SQLite Storage Class / Datatype	Corresponding Data Type in Kùzu
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 Kùzu. 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 Kùzu. 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 Kùzu. This is done with the COPY FROM statement. Here is an example:

We first create a Person table in Kùzu. 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 Kùzu 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 Kùzu

Finally, we can verify the data in the Person table in Kùzu.

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, Kùzu 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