Is there a query to obtain db schema?

[kamalikaray]

Hi,

Is there a cypher query to get the db schema? Something similar to this?
call db.schema()

Thanks

[prrao87]

Hi @kamalikaray, there are some schema functions that can help with this. Because Kùzu imposes more structure than some of the other graph DBs, this might not be achievable through a single CALL statement that simply outputs a JSON. The docs page on the CALL query clause describes this, but here's how I'd typically do this:

1. Try CALL show_tables() RETURN * to return a list of all tables in the database.

kuzu> CALL show_tables() RETURN *;
┌─────────────┬────────┬───────────────┬─────────┐
│ name        │ type   │ database name │ comment │
│ STRING      │ STRING │ STRING        │ STRING  │
├─────────────┼────────┼───────────────┼─────────┤
│ Customer    │ NODE   │ local(kuzu)   │         │
│ Product     │ NODE   │ local(kuzu)   │         │
│ Purchased   │ REL    │ local(kuzu)   │         │
│ Copurchased │ REL    │ local(kuzu)   │         │
└─────────────┴────────┴───────────────┴─────────┘

2. Once you know the table names, you can specify which table name you want to learn more about via CALL table_info("Customer") RETURN *;

kuzu> CALL table_info("Customer") RETURN *;
┌─────────────┬────────┬────────┬─────────────┐
│ property id │ name   │ type   │ primary key │
│ INT64       │ STRING │ STRING │ BOOL        │
├─────────────┼────────┼────────┼─────────────┤
│ 0           │ name   │ STRING │ True        │
│ 1           │ city   │ STRING │ False       │
└─────────────┴────────┴────────┴─────────────┘

Same for relationship tables, where it outputs the properties:

kuzu> CALL table_info("Purchased") RETURN *;
┌─────────────┬──────────┬────────┐
│ property id │ name     │ type   │
│ INT64       │ STRING   │ STRING │
├─────────────┼──────────┼────────┤
│ 1           │ quantity │ INT32  │
└─────────────┴──────────┴────────┘

3. To find the source/destination nodes for each relationship table, use the CALL show_connection("{Purchased") RETURN *:

kuzu> CALL show_connection("Purchased") RETURN *;
┌───────────────────┬────────────────────────┬──────────────────────────┬───────────────────────────────┐
│ source table name │ destination table name │ source table primary key │ destination table primary key │
│ STRING            │ STRING                 │ STRING                   │ STRING                        │
├───────────────────┼────────────────────────┼──────────────────────────┼───────────────────────────────┤
│ Customer          │ Product                │ name                     │ name                          │
└───────────────────┴────────────────────────┴──────────────────────────┴───────────────────────────────┘

As you can see, each query clause provides a different subset of information, and you can run these queries from within Python, collect the query result as a nested object and iterate through the table IDs and/or names to retrieve the information programmatically.

Again, because of the structured property graph model within Kùzu, it's difficult to nest tables within other tables and display them in a way that doesn't overwhelm the user. So we went with multiple query clauses this way. Hope that makes sense!