Skip to content
Blog

Relational database extensions

Kùzu supports direct 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 to allow users to scan data from external relational databases without having to copy it to a graph database. In the future, we plan on extending this functionality to run Cypher queries directly on top of tables that sit in an external RDBMS.

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

Extension NameDescriptionMinimum Version
duckdbScan from an attached DuckDB database0.10.0
postgresScan from an attached PostgreSQL database14.0
sqliteScan from an attached SQLite database3.3.0

Usage

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

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 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')
  • DB_PATH: Path to the DuckDB 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.

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');

3. Scan from DuckDB 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 |
---------------

4. Reference database via alias

You can utilize 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 *

5. Copy data from DuckDB tables

One important use case of the DuckDB extension is to facilitate seamless data transfer from DuckDB 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 query results feature.

Create a Person table in Kùzu

We first create a Person table in Kùzu which has the same schema as the one defined in DuckDB.

CREATE NODE TABLE Person (name STRING, age INT32, PRIMARY KEY(name));
Copy the data to Kùzu

We can reference the created alias uw to copy data from the DuckDB table to the Kùzu table.

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

6. 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 |
------------------

7. 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 databases (DuckDB or some other attached database), 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() RETURN *

Note: this call function will clear cache of all attached databases.

8. Detach database

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

DETACH uw