Create property graph

The first step in using SQL/PGQ is creating a property graph as a layer on top of your data. In DuckPGQ, property graphs are transient; they only exist as long as the connection to the database is open.

<aside> 💡

As of community v0.1.0 released with DuckDB v1.1.3 property graphs are persistent and are synchronised between connections.

</aside>

The tables will be divided into vertex tables and edge tables, having a primary key-foreign key relationship. An edge table should have a column defining the source node and a column describing the destination node.

To create a property graph the syntax is as follows:

CREATE [ OR REPLACE ] PROPERTY GRAPH (<property_graph_name> 
VERTEX TABLES (
	<vertex table>
[, <vertex table> ]
)
[ EDGE TABLES (	
	<edge table> 
[, <edge table ] ) ];

At least one <vertex table> must be specified to create a valid property graph. The EDGE TABLES are optional. For example to make a property graph over a subset of the Social Network Benchmark dataset from LDBC:

CREATE PROPERTY GRAPH snb 
VERTEX TABLES (
  Person, 
  Message,
  Forum
) 
EDGE TABLES (
  Person_knows_person     SOURCE KEY (Person1Id) REFERENCES Person (id)
                          DESTINATION KEY (Person2Id) REFERENCES Person (id)
                          LABEL Knows,
  Forum_hasMember_Person  SOURCE KEY (ForumId) REFERENCES Forum (id)
                          DESTINATION KEY (PersonId) REFERENCES Person (id)
                          LABEL hasMember,
	Person_likes_Message    SOURCE KEY (PersonId) REFERENCES Person (id)
                          DESTINATION KEY (id) REFERENCES Message (id)
                          LABEL likes_Message
);

Vertex table

<table name> [ AS <table name alias> ] [ <[properties](<https://duckpgq.notion.site/Property-graph-05c1dffe3f2547f0abfa3ea5a2b4eae1>)> ] [ LABEL <[label](<https://duckpgq.notion.site/Property-graph-05c1dffe3f2547f0abfa3ea5a2b4eae1>)> ] 

Only the table name is required for the vertex table; the table name alias, properties, and label are optional.

Edge table

To define the edge table, it is necessary to specify the table name, along with the source and destination keys.

In the following example, the source of the edge references the Person table, where the primary key is id and the foreign key is personId. The destination references the Message table, where both the primary key and the foreign key are id.

Person_likes_Message  SOURCE KEY (PersonId) REFERENCES Person (id)
                      DESTINATION KEY (id) REFERENCES Message (id)
                      LABEL likes_Message

The LABEL and the PROPERTIES are optional.

Pre-defined PK-FK relations

If the PK-FK relationships have already been defined during table creation, it is not necessary to repeat them when creating a property graph, unless this leads to ambiguity. The system will automatically infer the relationships based on the existing PK-FK constraints.