Introducing AgeSQL: Seamlessly Bridging the Gap Between SQL and Cypher in PostgreSQL

Introduction

Are you looking for a powerful tool that combines the expressive capabilities of Cypher queries with the familiarity of SQL syntax? Meet AgeSQL, a command-line interface (CLI) client for PostgreSQL that extends its capabilities to support both traditional SQL and Cypher queries without the hassle of wrapping Cypher queries. AgeSQL leverages the AGE extension for PostgreSQL, which enables graph queries within the database, making it a versatile tool for working with graph data.

In this blog post, I'll share the details of my current project, AgeSQL, its key features, and how you can install and use it to harness the power of both SQL and Cypher in your PostgreSQL database.

Understanding the Need for AgeSQL

Working with graph databases can be both exciting and challenging. Traditional SQL is powerful for certain types of data analysis and manipulation, but when it comes to traversing and querying connected graph data, Cypher, a language specifically designed for graph databases, shines. AgeSQL was born out of the need to have a unified tool that brings together the strengths of SQL and Cypher, making it easier for users to interact with graph databases using a familiar interface.

What sets this apart from the current version of Apache AGE is that AgeSQL does not require Cypher queries to be wrapped in an SQL function clause, and can be directly written in Cypher syntax. The backend takes the raw user inputs and convert them into a wrapped clause that is compatible in AGE, which will then be sent to the server.

Addressing the Challenges

Building a CLI tool like AgeSQL comes with its share of challenges. Let's briefly touch on how we've addressed some of these challenges to make AgeSQL a robust and user-friendly tool:

1. Determining the graph to use: AgeSQL has a mechanism to identify the graph to be queried, allowing it to handle different graphs based on user input.

2. Determining the number of output parameters: AgeSQL intelligently determines the number of parameters to expect in the final result, which is crucial for processing and presenting query results accurately.

3. Determining the output type: AgeSQL identifies the appropriate output type for each parameter in the result, taking into account different data types supported by PostgreSQL, such as pg_float8 or agtype.

By addressing these challenges, AgeSQL provides a seamless and efficient experience for executing SQL and Cypher queries within the PostgreSQL database.

Key Features of AgeSQL

AgeSQL offers a variety of features that make it a powerful tool for working with graph data:

• Seamless integration of Cypher queries into PostgreSQL: AgeSQL supports standard SQL queries in addition to Cypher queries. It internally converts Cypher queries into PostgreSQL functions, eliminating the need for manual function calls.

• Execution of Cypher queries: With AgeSQL, you can execute Cypher queries directly within the PostgreSQL database. This allows you to perform graph traversal, pattern matching, and graph analysis operations.

• Integration with traditional SQL: AgeSQL seamlessly wraps Cypher commands in SQL syntax, allowing you to execute them within PostgreSQL.

• Support for graph-specific operations: AgeSQL supports various graph-specific operations, including traversing nodes and edges, retrieving properties, and performing graph analytics.

• Familiar psql-like interface: AgeSQL provides an interface similar to PostgreSQL's psql, making it easy for users to interact with the database.

Installing and Using AgeSQL

To get started with AgeSQL, follow these steps:

1. Ensure you have PostgreSQL 15 installed with the AGE extension enabled.

2. Clone the PostgreSQL repository from GitHub and compile the source code.

3. Install AGE 12.1.0, a prerequisite for AgeSQL.

4. Clone the AgeSQL repository from GitHub and compile the source code.

5. Start the AgeSQL command-line interface.

The detailed step-by-step instructions could be found in the README section of the GitHub page. Once you're inside the AgeSQL CLI, you can run both SQL and Cypher queries effortlessly. For SQL queries, simply type them as you would in psql. For Cypher queries, remember to set the graph path first using the CREATE GRAPH command or the SET GRAPH_PATH command. AgeSQL seamlessly converts Cypher queries to PostgreSQL functions, so you can execute them without calling functions explicitly.

Examples

Creating graphs:

CREATE GRAPH graph_name1;

Creating vertices and relationships:

CREATE (a:label)-[e:RELTYPE]->(b:label);

Returning values:

RETURN a AS rslt ORDER BY b ASC SKIP 1 LIMIT 5;

Matching data:

MATCH (n:label), (m:label) WHERE property(n) <> property(m)
OPTIONAL MATCH (n)-[r]->(p), (m)-[s]->(q);

Conclusion

AgeSQL is a powerful CLI tool that bridges the gap between SQL and Cypher in PostgreSQL, allowing you to work seamlessly with graph databases using familiar SQL syntax while benefiting from the expressive power of Cypher without having the need to wrap it in an SQL clause. With its seamless integration and support for both SQL and Cypher queries, AgeSQL empowers developers and data analysts to tackle complex graph data with ease.

So, if you're working with graph databases and want a versatile tool that brings together the best of SQL and Cypher, give AgeSQL a try! Get started with AgeSQL today and unlock the potential of your PostgreSQL database for graph-based operations. Happy graph querying!