DEV Community

Marco Aurélio Silva de Souza Júnior
Marco Aurélio Silva de Souza Júnior

Posted on

Cypher on PostgreSQL: A Reason to Use Apache AGE

Do you find it difficult to remember all the syntax and commands of SQL? Then take a look at the PostgreSQL extension Apache AGE, which allows you to use the popular graph query language Cypher on PostgreSQL to simplify your queries.

Cypher is a declarative language that focuses on the relationships between data rather than the data itself. This means that it's easier to write and understand queries that involve complex relationships.

But why is Cypher easier to learn than SQL?

For example, let's say you want to find all the customers who have bought a particular product, and then find all the products that these customers have bought in the past. With SQL, you'd have to write a complex join statement to achieve this. With Cypher on Apache AGE, you can simply write:

SELECT * FROM cypher('your_graph_name', $$
    MATCH (c:Customer)-[:BOUGHT]->(p:Product)<-[:BOUGHT]-(c2:Customer) 
    WHERE p.name = 'Product X' RETURN c2, p $$)
as (c2 agtype, p product);
Enter fullscreen mode Exit fullscreen mode

See that the pattern (c:Customer) indicates a node with the label "Customer" that is assigned to the variable "c", while the pattern -[:BOUGHT]-> represents an edge with the label "BOUGHT".

This query is much simpler and easier to read than the equivalent SQL query:

SELECT c2.*, p.* FROM Customer c 
JOIN Bought b ON c.customer_id = b.customer_id 
JOIN Product p ON b.product_id = p.product_id 
JOIN Bought b2 ON p.product_id = b2.product_id 
JOIN Customer c2 ON b2.customer_id = c2.customer_id 
WHERE p.name = 'Product X'
Enter fullscreen mode Exit fullscreen mode

Apache AGE is a PostgreSQL extension that allows you to use Cypher to query graph data stored in PostgreSQL. It provides a Cypher-to-SQL compiler that translates Cypher queries into SQL queries that can be executed by PostgreSQL. This means that you can use Cypher and SQL to query your PostgreSQL database without having to learn a new query language.

If you're looking for an easier way to write complex SQL queries, then Apache AGE and Cypher are worth checking out. With Cypher's declarative syntax and Apache AGE's Cypher-to-SQL compiler, you can simplify your queries and make them more readable. Give it a try and see how it can help you write better queries in less time.

https://age.apache.org/
https://github.com/apache/age

Top comments (0)