In this tutorial, we will guide you through the process of creating a simple database using PostgreSQL and Apache AGE. PostgreSQL is a powerful relational database management system (RDBMS) that is widely used in the industry due to its performance, scalability, and reliability. However, when combined with Apache AGE, it adds graph database capabilities, making it even more useful in a variety of applications.

With Apache AGE, you can use SQL and Cypher queries to access and analyze your data. SQL is a standard query language that is widely used for relational databases, while Cypher is a graph query language that allows you to perform powerful graph traversals and pattern matching. By learning both query languages, you'll have a powerful set of tools to work with data in different ways, depending on the needs of your application.

Throughout this tutorial, you will learn the basics of creating tables, inserting data, and querying data using both SQL and Cypher. By the end of the tutorial, you'll have a solid understanding of how to use PostgreSQL and Apache AGE together, and you'll be ready to start building your own applications that leverage the power of both technologies.

Prerequisites

Before starting this tutorial, you need to make sure that you have installed PostgreSQL version 11 or 12 and Apache AGE. If you haven't installed them yet, you can follow the steps in this tutorial Step-by-Step Guide to Install PostgreSQL & Apache AGE on Windows..

To visualize the graph generated by the queries in this tutorial, you can use AGE Viewer. This is a useful tool to help you understand the graph structure and relationships between nodes and edges. It can also help you identify any issues with your queries and data.

AGE Graph Fundamentals: Vertices and Edges

In AGE, a vertex represents a fundamental entity in a graph, which can exist independently. Each vertex may be assigned a label, and may have zero or more outgoing edges, as well as zero or more incoming edges.

On the other hand, an edge in AGE encodes a directed connection between exactly two nodes, namely the source node and the target node. An outgoing edge represents a directed relationship from the perspective of its source node, while an incoming edge represents a directed relationship from the perspective of its target node. Each edge is assigned exactly one edge type.

Understanding the basics of vertices and edges is crucial to working with graphs in AGE. By leveraging these fundamental concepts, users can effectively model complex relationships and dependencies between various entities in their data, enabling them to extract meaningful insights and knowledge.

Database Entities

In this tutorial, we will be creating a basic online ordering application that allows users to place orders for products. The application will utilize two main types of vertices, namely User and Product, and one edge type, which is ORDERS. This means that a user can order one or more products through the ORDERS edge type. By utilizing these vertices and edges, we can represent the relationships and interactions between the various entities in our application.

Creating The Application

1. Configuring AGE with PostgreSQL

To configure AGE with PostgreSQL, you need to load the AGE extension and set the search path to ag_catalog. Here are the correct SQL queries:

LOAD 'age'
SET search_path to ag_catalog,"your_user_name",public;

The first query loads the AGE extension, while the second query sets the search path to ag_catalog and public. Make sure to replace "your_user_name" with your actual PostgreSQL username.

2. Creating a graph

To store our data, we need to create a new graph. This can be achieved by executing the following SQL query:

SELECT * FROM ag_catalog.create_graph('graph_name');

This query will create a new graph with the specified name.

3. Creating Users

First, we will create a 'User' vertex (node) to represent our user in the graph. We can also add some properties to the node such as name, email, phone, and address.

SELECT *
FROM cypher('online_orders', $$
    CREATE(:User{name:"user1" ,email : "test@test.com" , phone:"123456" , address : "Egypt"});
$$) AS (result agtype)

This query will create a new vertex with the label 'User' and the specified properties, and return the result in the 'result' column.

agtype is a data type in AGE that represents an Abstract Graph object. It can store graphs, vertices, edges, and other graph-related data structures. It is similar to the JSON data type in PostgreSQL but is optimized for graph data. The agtype data type is used extensively in AGE for storing and querying graph data.

Adding more users...

SELECT *
FROM cypher('online_orders', $$
    CREATE(:User{name:"user2" ,email : "test2@test.com" , phone:"123488" , address : "USA"})
$$) AS (result agtype)

You can use the following query to verify the inserted users:

SELECT * FROM cypher('online_orders' , $$
MATCH(u:User) RETURN u
$$) AS (result agtype);

This query uses the MATCHclause to retrieve all the User nodes in the online_orders graph and returns them in the result set. The AS result clause renames the result set to result.

4. Creating Products

Next, we can create a 'Product' vertex (node) to hold all product-related data such as name and price (additional properties can also be added).

SELECT *
FROM cypher('online_orders', $$
    CREATE (:Product{name:"Product 1", price: 10.99}),
           (:Product{name:"Product 2", price: 5.99}),
           (:Product{name:"Product 3", price: 7.50})
$$) AS (result agtype);

This query creates three products in our graph.

You can use the following query to verify the inserted products:

SELECT * FROM cypher('online_orders' , $$
MATCH(u:Product) RETURN u
$$) AS (result agtype);

This query uses the MATCHclause to retrieve all the Product nodes in the online_orders graph.

5. Creating Edges
In this step, we will create the ORDERS relationship (edge) to represent the relation between users and a product. Whenever a user wants to make an order, we will create this relationship between the user and the desired product(s). Additionally, we can add some properties to edges, just like we did with the nodes. In this example, we will add the current time and the quantity in the edge.

To make an order for Product 1 with a quantity of five for user1 that we created previously, execute the following query:

SELECT * FROM cypher('online_orders' , $$
    MATCH (u:User{name:'user1'}) , (p:Product{name:"Product 1"})
    CREATE((u)-[o:ORDERS{time:timestamp() , quantity:5}]->(p))
    RETURN u,o,p
$$) AS (u agtype, o agtype, p agtype);

After inserting the data, the AGE viewer can be used to visualize the graph and its relationships.

Based on the data shown in the AGE viewer, it appears that user1 has an ORDERSrelationship with Product 1.

If you want to display all the data in the graph you can run the following query:

You can run the following query to display all the data in the graph:

SELECT *
FROM cypher('online_orders', $$
    MATCH (u:User), (p:Product)
    OPTIONAL MATCH (u)-[o:ORDERS]->(p)
    RETURN u, o, p
$$) AS (u agtype, o agtype, p agtype);

Note that this will return all users, their orders, and the products they ordered. However, for larger graphs with many nodes and edges, this query can be slow and resource-intensive. It is recommended to use filters and limit the number of results returned when querying large graphs.

Note: This is a simple example for illustration purposes only. In a real-world scenario, there would be more complex data models and relationships between nodes and edges.

Great! You have created a simple graph using AGE and PostgreSQL. This was just a simple example to demonstrate the basic steps involved in creating and querying a graph. There are many other features and capabilities of AGE that you can explore to build more complex and sophisticated graphs to meet your specific requirements. We hope this tutorial has been helpful in getting you started with AGE and PostgreSQL.

References
AGE - Documentation

Contribute to Apache AGE
Apache AGE website: https://age.apache.org/
Apache AGE Github: https://github.com/apache/age