Getting Started with CoraDB¶

Start with a New Database ¶

Welcome to CoraDB, a unified database engine that blends relational SQL with native graph capabilities. Built on the CUBRID SQL engine, CoraDB lets you store, update, and query graph structures directly alongside relational data.

In this tutorial, you’ll build a transportation network where: - Cities, Companies, and Routes are vertex labels - Connects and Operates are edge labels - Relational tables Passenger and Ticket extend the schema.

You’ll create data, explore graph queries, and execute hybrid SQL + MATCH queries.

The following steps show how to quickly set up and connect to a CoraDB database.

Step 1: Create the Database

Use the coradb createdb utility to create a new database. The –db-volume-size option sets the initial data volume size. The example below creates a database named testdb with a 20 MB volume using the en_US locale.

% coradb createdb --db-volume-size=20M testdb en_US

Step 2: Start the Server

Start the CoraDB service and then start the testdb database server.

% coradb service start
% coradb server start testdb

Step 3: Connect to the Database

Use the corasql interpreter to connect to testdb as the dba user.

% corasql -u dba testdb

1. Defining the Schema¶

Let’s start by defining the graph and relational schema.

-- Vertex labels
CREATE VERTEX TABLE City (
    id INTEGER PRIMARY KEY,
    Name STRING,
    Country STRING,
    Population INTEGER
);

CREATE VERTEX TABLE Company (
    id INTEGER PRIMARY KEY,
    Name STRING,
    FleetSize INTEGER,
    Headquarters STRING
);

CREATE VERTEX TABLE Route (
    id INTEGER PRIMARY KEY,
    Code STRING,
    Type STRING,        -- "bus", "train", "flight"
    Distance INTEGER,
    Duration INTEGER
);

-- Edge labels
CREATE EDGE TABLE Connects (
    Since INTEGER
);

CREATE EDGE TABLE Operates (
    Since INTEGER
);

-- Relational tables (non-graph)
CREATE TABLE Passenger (
    id INTEGER PRIMARY KEY,
    Name STRING,
    HomeCity STRING
);

CREATE TABLE Ticket (
    id INTEGER PRIMARY KEY,
    PassengerID INTEGER,
    CompanyID INTEGER,
    RouteID INTEGER,
    Price INTEGER
);

2. Inserting Data¶

Insert vertices

INSERT INTO City VALUES
    (1, 'Bucharest', 'Romania', 1800000),
    (2, 'Sofia', 'Bulgaria', 1300000),
    (3, 'Belgrade', 'Serbia', 1200000),
    (4, 'Budapest', 'Hungary', 1750000),
    (5, 'Vienna', 'Austria', 1900000);

INSERT INTO Company VALUES
    (10, 'EuroTrans', 150, 'Vienna'),
    (11, 'Danube Lines', 80, 'Budapest'),
    (12, 'Carpathia Express', 120, 'Bucharest');

INSERT INTO Route VALUES
    (100, 'EUX-11', 'train', 240, 180),
    (101, 'EUX-12', 'bus', 370, 280),
    (102, 'EUX-13', 'bus', 380, 300),
    (103, 'EUX-14', 'train', 400, 310);

Insert edges

Each INSERT EDGE must connect exactly one source and one destination vertex. Subqueries that return multiple rows are not allowed.

Connect routes to the cities they serve:

-- Route EUX-12: Belgrade ↔ Budapest
INSERT EDGE
    FROM (SELECT r FROM Route r WHERE r.Code='EUX-12')
    TO   (SELECT c FROM City c WHERE c.Name='Belgrade')
    INTO Connects VALUES (2020);

INSERT EDGE
    FROM (SELECT r FROM Route r WHERE r.Code='EUX-12')
    TO   (SELECT c FROM City c WHERE c.Name='Budapest')
    INTO Connects VALUES (2020);

-- Route EUX-13: Bucharest ↔ Sofia
INSERT EDGE
    FROM (SELECT r FROM Route r WHERE r.Code='EUX-13')
    TO   (SELECT c FROM City c WHERE c.Name='Bucharest')
    INTO Connects VALUES (2021);

INSERT EDGE
    FROM (SELECT r FROM Route r WHERE r.Code='EUX-13')
    TO   (SELECT c FROM City c WHERE c.Name='Sofia')
    INTO Connects VALUES (2021);

-- Route EUX-14: Sofia ↔ Belgrade
INSERT EDGE
    FROM (SELECT r FROM Route r WHERE r.Code='EUX-14')
    TO   (SELECT c FROM City c WHERE c.Name='Sofia')
    INTO Connects VALUES (2022);

INSERT EDGE
    FROM (SELECT r FROM Route r WHERE r.Code='EUX-14')
    TO   (SELECT c FROM City c WHERE c.Name='Belgrade')
    INTO Connects VALUES (2022);

-- Route EUX-11: Budapest ↔ Vienna
INSERT EDGE
    FROM (SELECT r FROM Route r WHERE r.Code='EUX-11')
    TO   (SELECT c FROM City c WHERE c.Name='Budapest')
    INTO Connects VALUES (2023);

INSERT EDGE
    FROM (SELECT r FROM Route r WHERE r.Code='EUX-11')
    TO   (SELECT c FROM City c WHERE c.Name='Vienna')
    INTO Connects VALUES (2023);

Assign operating companies to specific routes:

INSERT EDGE
    FROM (SELECT comp FROM Company comp WHERE comp.Name='EuroTrans')
    TO   (SELECT r FROM Route r WHERE r.Code='EUX-11')
    INTO Operates VALUES (2015);

INSERT EDGE
    FROM (SELECT comp FROM Company comp WHERE comp.Name='Danube Lines')
    TO   (SELECT r FROM Route r WHERE r.Code='EUX-12')
    INTO Operates VALUES (2018);

INSERT EDGE
    FROM (SELECT comp FROM Company comp WHERE comp.Name='Carpathia Express')
    TO   (SELECT r FROM Route r WHERE r.Code='EUX-13')
    INTO Operates VALUES (2020);

Insert relational data

INSERT INTO Passenger VALUES
    (1, 'Alice', 'Bucharest'),
    (2, 'Boris', 'Sofia'),
    (3, 'Clara', 'Vienna');

INSERT INTO Ticket VALUES
    (101, 1, 12, 102, 120),
    (102, 2, 11, 101, 90),
    (103, 3, 10, 100, 140);

3. Querying the Graph¶

Example 1 – Show all routes and their cities

SELECT r.Code, r.Type, c.Name AS City
FROM MATCH (r:Route)-[e:Connects]->(c:City)
ORDER BY r.Code, City;

Example 2 – Companies and routes they operate

SELECT comp.Name AS Company, r.Code AS RouteCode, r.Type
FROM MATCH (comp:Company)-[:Operates]->(r:Route)
ORDER BY comp.Name;

Example 3 – Variable-length traversal

Find city-to-city connections reachable within 2 hops.

SELECT start.Name AS StartCity, dest.Name AS Destination,
       horizontal_count(e) AS Steps
FROM MATCH (start:City)<-[e:Connects{1,2}]-(r:Route), (r:Route)-[e2:Connects{1,2}]->(dest:City)
WHERE start.Name='Bucharest';

4. Hybrid Graph–Relational Queries¶

Example 1 – Join Ticket + Passenger with graph traversal

SELECT t.id AS TicketID,
       p.Name AS Passenger,
       r.Code AS RouteCode,
       comp.Name AS Operator,
       t.Price
FROM Ticket t
     JOIN Passenger p ON p.id = t.PassengerID
     LEFT OUTER JOIN MATCH (comp:Company)-[:Operates]->(r:Route)
         ON comp.id = t.CompanyID AND r.id = t.RouteID
ORDER BY Passenger;

Example 2 – Link relational and graph data

List all companies headquartered in cities that appear in any route.

SELECT comp.Name, comp.Headquarters
FROM Company comp
     LEFT OUTER JOIN MATCH (r:Route)-[:Connects]->(c:City)
         ON c.Name = comp.Headquarters
ORDER BY comp.Name;

Example 3 – Nested MATCH inside WHERE clause

Find all routes that reach Vienna directly or through one intermediate city.

SELECT rt.Code, rt.Type, rt.Distance, c2.Name AS Destination
FROM Route rt,
     MATCH (r:Route)-[:Connects]->(c2:City)
WHERE r.id = rt.id AND c2.Name = 'Vienna';

Example 4 – Multi-root MATCH with relational filter

Show all city pairs connected by a route operated by a company with a fleet size greater than 100.

SELECT c1.Name AS FromCity, c2.Name AS ToCity, comp.Name AS Operator
FROM MATCH (comp:Company)-[:Operates]->(r:Route),
      (r:Route)-[:Connects]->(c1:City),
      (r:Route)-[:Connects]->(c2:City)
WHERE comp.FleetSize > 100 AND c1.id < c2.id
ORDER BY Operator;

Example 5 – Cross-join between SQL and MATCH

List passengers living in cities that have a route operated by EuroTrans.

SELECT p.Name AS Passenger, p.HomeCity
FROM Passenger p,
     MATCH (comp:Company)-[:Operates]->(r:Route), (r:Route)-[:Connects]->(c:City)
WHERE comp.Name='EuroTrans' AND c.Name = p.HomeCity;

5. Inspecting the Graph¶

CoraDB provides JSON utilities for introspection:

-- Export subgraph in JSON
SELECT JSON_PRETTY (JSON_GRAPH(comp, r))
FROM MATCH (comp:Company)-[:Operates]->(r:Route)
WHERE comp.Name='Danube Lines';

-- Display vertex label and properties
SELECT LABEL(v), JSON_PRETTY (JSON_PROPERTIES(v))
FROM MATCH (v:Route)
WHERE v.Type='train';

-- Inspect topology records
SELECT JSON_PRETTY (JSON_TOPOLOGY_RECORD(c))
FROM MATCH (c:City)
WHERE c.Name='Budapest';

6. Summary¶

You have now:

Created vertex and edge tables
Inserted graph and relational data
Queried graph relationships using MATCH
Combined graph and relational joins
Inspected graph structure with JSON tools

CoraDB unifies tabular and connected data in one query engine. Try next:

reference-guide for detailed syntax
Explore OPTIONAL MATCH, ANY_SHORTEST, and aggregate_arr()
Build more complex hybrid analytic queries

Use our Demo Database ¶

CoraDB also ships with a ready-to-use demo database called demodb that you can explore right away — no setup required.

Step 1: Start the Server

Start the CoraDB service and then start the demodb database server.

% coradb service start
% coradb server start demodb

Step 2: Connect to the Database

Use the corasql interpreter to connect to demodb as the dba user.

% corasql -u dba demodb

Step 3: Explore the Database

Once connected, you can explore the available tables using the SHOW TABLES statement.

SHOW TABLES;

For a full description of the demodb schema — including all tables, vertex tables, edge tables, and data volumes — consult the About Demodb section below.

To view the definition of a specific table, use the DESCRIBE statement followed by the table name. For example, the following command shows the definition of the customer table.

DESCRIBE customer;

Step 4: Run Graph and Hybrid Queries

The demodb database supports both pure graph queries and hybrid queries that combine graph traversal with relational SQL. The following examples illustrate what you can do.

Example 1 – Graph: products with their category and available suppliers

SELECT p.productName, c.categoryName, s.companyName AS supplierName, a.since
FROM MATCH (p:product)-[o:Part_Of]->(c:category), (p:product)-[a:Available_At]->(s:supplier)
ORDER BY 1, 2, 3, 4
LIMIT 10;

Example 2 – Graph: brands located in the same city as customers who like their manufactured products

SELECT
  cu.companyName AS Customer,
  b.brandName AS Brand,
  p.productName AS Product,
  la.since AS BrandLocatedSince
FROM MATCH
  (cu:customer)-[lk:Likes]->(p:product),
  (b:brand)-[m:Manufactures]->(p:product),
  (b:brand)-[la:Located_At]->(ci:city),
  (cu:customer)-[r:Resides_In]->(ci:city)
ORDER BY 1, 2, 3, 4;

Example 3 – Graph + relational: supplier revenue by city

SELECT
  ci.cityName AS City,
  s.companyName AS Supplier,
  SUM(p.unitPrice * oi.quantity * (1 - oi.discount)) AS TotalRevenue
FROM MATCH (p:product)-[:Available_At]->(s:supplier),
           (s:supplier)-[:Located_In]->(ci:city)
JOIN OrderItems oi ON oi.productID = p.productID
GROUP BY ci.cityName, s.companyName
ORDER BY 3 DESC, 1, 2;

Example 4 – Graph + relational: products liked by customers with high total spending

SELECT
  p.productName,
  COUNT(DISTINCT cu.customerID) AS HighValueFans
FROM MATCH (cu:customer)-[:Likes]->(p:product)
JOIN (
  SELECT po.customerID
  FROM Orders po
  JOIN OrderItems oi ON oi.orderID = po.orderID
  JOIN product pr ON pr.productID = oi.productID
  GROUP BY po.customerID
  HAVING SUM(
    COALESCE(pr.unitPrice,0) * COALESCE(oi.quantity,0) * (1 - COALESCE(oi.discount,0))
  ) > 500
) hv ON hv.customerID = cu.customerID
GROUP BY p.productName
ORDER BY 2 DESC, 1;

About Demodb ¶

The demodb database is a demonstration database for TesseractDB/CoraDB. It models an e-commerce domain using both traditional relational tables and graph tables (vertex and edge tables), showcasing the hybrid relational/graph capabilities of the database engine.

Loading the Demo Database¶

The demo database can be loaded using the provided shell script:

export CORADB=/path/to/coradb
./demo/make_coradb_demo.sh

The script creates a database named demodb and loads the schema tables, indexes, and data objects in sequence.

Schema Overview¶

The schema consists of two regular (relational) tables and six vertex tables (graph nodes), connected by nine edge tables (graph relationships).

Regular Tables¶

Regular tables store transactional data using standard relational semantics.

`orders`¶

Stores customer orders.

Column	Type	Constraints	Description
`orderID`	BIGINT	NOT NULL	Unique identifier for the order
`customerID`	BIGINT	NOT NULL	References the ordering customer
`orderDate`	DATETIME		Date the order was placed
`shippedDate`	DATETIME		Date the order was shipped
`shippingCost`	DECIMAL(10,2)		Cost of shipping
`shipAddress`	STRING		Delivery address

Indexes:

idx_orders_orderID — on orderID
idx_orders_customerID — on customerID
idx_orders_orderDate — on orderDate
idx_orders_shippedDate — on shippedDate

Sample data: 1,000 rows

`orderItems`¶

Stores line items within an order, linking orders to products.

Column	Type	Constraints	Description
`orderID`	BIGINT	NOT NULL	References the parent order
`productID`	BIGINT	NOT NULL	References the ordered product
`quantity`	DECIMAL(10,2)		Quantity of the product ordered
`discount`	DECIMAL(10,2)		Discount applied to this line item

Indexes:

idx_orderItems_orderID — on orderID
idx_orderItems_productID — on productID
idx_orderItems_order_product — composite on (orderID, productID)

Sample data: 4,383 rows

Vertex Tables¶

Vertex tables represent nodes in the property graph. Each vertex table has a primary key column and can participate in edge relationships.

`city`¶

Represents geographic cities or locations.

Column	Type	Constraints	Description
`cityID`	BIGINT	PRIMARY KEY	Unique identifier for the city
`cityRegion`	STRING		Region or state within the country
`postalCode`	STRING		Postal/ZIP code
`cityName`	STRING		Name of the city
`country`	STRING		Country name

Sample data: 121 vertices

Example cities: Berlin (Germany), México D.F. (Mexico), London (UK), Madrid (Spain), Buenos Aires (Argentina)

`customer`¶

Represents customers who place orders.

Column	Type	Constraints	Description
`customerID`	BIGINT	PRIMARY KEY	Unique identifier for the customer
`companyName`	STRING		Name of the customer’s company
`contactName`	STRING		Name of the primary contact
`contactTitle`	STRING		Job title of the primary contact
`address`	STRING		Street address
`phone`	STRING		Phone number

Sample data: 1,000 vertices

`category`¶

Represents product categories used to organize the product catalog.

Column	Type	Constraints	Description
`categoryID`	BIGINT	PRIMARY KEY	Unique identifier for the category
`categoryName`	STRING		Name of the category
`categoryDescription`	STRING		Description of the category

Sample data: 32 vertices

Example categories: Beverages, Condiments, Confections, Dairy Products, Grains/Cereals, Meat/Poultry, Produce, Seafood, Men, Women, Tops, Bottoms, Jackets, Gear, Sale

`product`¶

Represents products available for purchase.

Column	Type	Constraints	Description
`productID`	BIGINT	PRIMARY KEY	Unique identifier for the product
`productName`	STRING		Name of the product
`productDescription`	STRING		Description or packaging information
`unitPrice`	DECIMAL(5,2)		Price per unit
`unitsInStock`	DECIMAL(5,2)		Current inventory level

Sample data: 2,125 vertices

`brand`¶

Represents brands that manufacture and market products.

Column	Type	Constraints	Description
`brandID`	BIGINT	PRIMARY KEY	Unique identifier for the brand
`brandName`	STRING		Name of the brand

Sample data: 106 vertices

`supplier`¶

Represents suppliers that provide products and distribute brands.

Column	Type	Constraints	Description
`supplierID`	BIGINT	PRIMARY KEY	Unique identifier for the supplier
`companyName`	STRING		Name of the supplier’s company
`contactName`	STRING		Name of the primary contact
`contactTitle`	STRING		Job title of the primary contact
`address`	STRING		Street address
`phone`	STRING		Phone number

Sample data: 29 vertices

Edge Tables¶

Edge tables represent directed relationships between vertex tables in the property graph. Each edge table includes a since column recording when the relationship was established.

All edge tables share the following property:

Column	Type	Description
`since`	DATETIME	Date/time when the relationship began

`part_of`¶

Direction: product → category

A product belongs to one or more categories.

Sample data: 3,630 edges

`located_in`¶

Direction: supplier → city

A supplier is located in a city.

Sample data: 29 edges

`located_at`¶

Direction: brand → city

A brand has its headquarters or presence at a city.

Sample data: 106 edges

`resides_in`¶

Direction: customer → city

A customer resides in a city.

Sample data: 591 edges

`manufactures`¶

Direction: brand → product

A brand manufactures one or more products.

Sample data: 102 edges

`follows`¶

Direction: customer → brand

A customer follows a brand (e.g. brand loyalty or subscription).

Sample data: 1,181 edges

`available_at`¶

Direction: product → supplier

A product is available through a particular supplier.

Sample data: 77 edges

`likes`¶

Direction: customer → product

A customer has expressed a preference for a product.

Sample data: 646 edges

`distributes`¶

Direction: supplier → brand

A supplier distributes products from a particular brand.

Sample data: 29 edges

Graph Relationships Summary¶

The diagram below illustrates how the vertex and edge tables are connected:

    city ◀──resides_in── customer ──follows──▶ brand ──located_at──▶ city
     ▲                       │                   │
located_in             likes               manufactures
     │                       │                   │
supplier ──distributes──▶ brand              product ──part_of──▶ category
     ▲                                           │
available_at ◀─────────────────────────────────┘

A cleaner tabular view of all edges:

Edge Table	From	To	Meaning
`part_of`	product	category	Product belongs to category
`located_in`	supplier	city	Supplier is based in city
`located_at`	brand	city	Brand is located at city
`resides_in`	customer	city	Customer lives in city
`manufactures`	brand	product	Brand manufactures product
`follows`	customer	brand	Customer follows brand
`available_at`	product	supplier	Product is available from supplier
`likes`	customer	product	Customer likes product
`distributes`	supplier	brand	Supplier distributes brand

Data Summary¶

Table / Edge	Type	Row Count
`orders`	Table	1,000
`orderItems`	Table	4,383
`city`	Vertex	121
`customer`	Vertex	1,000
`category`	Vertex	32
`product`	Vertex	2,125
`brand`	Vertex	106
`supplier`	Vertex	29
`part_of`	Edge	3,630
`located_in`	Edge	29
`located_at`	Edge	106
`resides_in`	Edge	591
`manufactures`	Edge	102
`follows`	Edge	1,181
`available_at`	Edge	77
`likes`	Edge	646
`distributes`	Edge	29

Getting Started with CoraDB¶

Start with a New Database¶

1. Defining the Schema¶

2. Inserting Data¶

3. Querying the Graph¶

4. Hybrid Graph–Relational Queries¶

5. Inspecting the Graph¶

6. Summary¶

Use our Demo Database¶

About Demodb¶

Loading the Demo Database¶

Schema Overview¶

Regular Tables¶

orders¶

orderItems¶

Vertex Tables¶

city¶

customer¶

category¶

product¶

brand¶

supplier¶