Does your APP need Apache AGE?
Tags: apacheage, database, graphs
Published: 2023-07-20 11:16:58

## Apache AGE
It is a PostgreSQL extension that provides graph database functionality. That means we could have the relational database alongside the graph database

## PostgreSQL
PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. 

## PostgreSQL and Apache AGE
As mentioned before having the extension AGE on top of PostgreSQL allowing you to enable a new feature of the graph databases on top of the structured relational databases, i.e. adding the OpenCypher query language to the SQL language into a single language.

## Need those or not?
- Do you need a **flexible schema** with **relations**?
- Need to **quickly** and **easily** **store data and analyze** the **relationships** among data, so you can better understand the myriad of possible outcomes?
- **Simple querying**
```sql
SELECT e.LastName, et.Description
FROM Employee AS e
JOIN EmployeeTerritory AS et ON (et.EmployeeID = e.EmployeeID)
JOIN Territory AS t ON (et.TerritoryID = t.TerritoryID);
Copy
```
**Replaced with**
```sql
MATCH (t:Territory)<-[:IN_TERRITORY]-(e:Employee)
RETURN t.description, collect(e.lastName);
```

- **Visualization**: you can view your graph using age-viewer
![agviewer](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nfvwnt87s78zf8zx0zhp.png)


- **Transactions** support wrapped as it is inside a sql from statement, that feature is not widely supported by the rest available solutions of graph db

## **Example**
### Requirements
- **PostgreSQL** installed
- **Apache AGE** installed
### 
- START A PSQL SESSION AND LOAD AGE AND SET SEARCH PATH
```sh
export $DB_NAME=test
psql $DB_NAME
test=# LOAD 'age';
LOAD
test=# SET search_path = ag_catalog, "$user", public;
SET
```
- **Create graph**
```sql
 SELECT * FROM create_graph('dev-graph');
```

- **Create nodes and edge**
```sql
test=#  SELECT * FROM cypher('dev-graph', $$
CREATE (n:DEVELOPER {name:"Mark", role:"Head of Engineering", department:"IT"})-[m:MANAGES]->(d:DEPARTMENT {name:"IT"}) return n, m, d 
$$) as (n agtype, m agtype, d agtype);
```

- **Explanation**:
 - (): Everything is wrapped with () is a node 
 - []: Everything is wrapped with () is an edge 
 - [str:] : str is an alias to hold the edge or if within () the node to mention it in the rest of the query 
 - (str: LABEL): LABEL is the category or the label of the node
 - {}: called properties of json type that holds any info you want to add

- **Query**
```sql
test=#  SELECT * FROM cypher('dev-graph', $$
MATCH (n:DEVELOPER)-[m:MANAGES]->(d:DEPARTMENT) return n, d
$$) as (n agtype, d agtype);
                                                                    n                                                                     |                                           d                           
                
------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------
----------------
 {"id": 844424930131969, "label": "DEVELOPER", "properties": {"name": "Mark", "role": "Head of Engineering", "department": "IT"}}::vertex | {"id": 1407374883553281, "label": "DEPARTMENT", "properties": {"name":
 "IT"}}::vertex
(1 row)
```

## Resources to learn more about Apache AGE:
- https://age.apache.org/
- https://sorrell.github.io/2020/12/10/Postgres-and-Apache-AGE.html
- https://bitnine.net/blog-postgresql/apache-age-journey-top-level/
- https://github.com/apache/age