One Graph Database to Rule Them All
Tags: python, tutorial, postgres
Published: 2023-05-30 21:26:34

## The Secret of the Spheres

_At his ancient tower, the data mage ponders over it's mighty database, wondering how one could make this majestic library of information more powerful. How could he perform greater spells over it... A graph database perhaps? If there was only one graph database to rule them all, one database to find all vertices and edges, one database to bring all knowledge, and in the light guide him._

_He delves inside the [Mages Guild library](https://github.com/), looking for some kind of sacred texts to help him in his task. A great book catches his eyes. It's cover filled with small spheres made of glass (which seems to contain constellations inside them) connected by strings of gold. The title reads "[Apa Rindë Istima Age](https://github.com/apache/age)"._

_Bringing the book back to his tower, the data mage starts reading it's pages. As he reads each sentence, the words evaporate into small blue spheres, hovering in the air. Gold connections twist and bind each of these blue orbs together._

![Database wizard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hraz0vg1j3a4cbmbhz3w.png)

_A blue smoke comes out from his mouth, turning into a royal python snake. Flying gently, she swirls over his head and proceeds to swallow the spheres one by one. After she has eaten them all, she begins to burn, her smoke entering the mage's ears. As the mage listens attentively to the whispered wisdom carried by the smoke, his eyes closed in deep concentration, he finally opens them and utters with a sense of enlightenment... "The secrets of the spheres reveal the path to ultimate knowledge."_

## Revealing the Knowledge

Here we are going to reveal what the mage heard whilst the magical snake dust entered his ears. One thing that he heard for sure was that, for him to unlock his database's maximum potential,it was necessary to use [Apache AGE](https://age.apache.org/), a graph extension for PostgreSQL.

To use it, there are some requirements you must type in your _Crystal Ball_ ... the Terminal I mean:
```
sudo apt-get update
sudo apt-get install python3-dev libpq-dev
pip install --no-binary :all: psycopg2
pip install antlr4-python3-runtime==4.11.1
pip install apache-age-python
```

After that, create a python script beginning with the following lines:
```python
import psycopg2
import age

GRAPH_NAME = 'testing_py'
conn = psycopg2.connect(host="localhost", port="5432", dbname="database", user="username", password="password")

age.setUpAge(conn, GRAPH_NAME)
```
It will import two modules: `psycopg2` and `age`. `psycopg2` is a PostgreSQL adapter for Python, allowing Python programs to interact with a PostgreSQL database. `age.setUpAge(conn, GRAPH_NAME)` will create the graph namespace, where the vertices and edges will be stored.

AGE uses openCypher in it's queries, so every time that you want to make a query for the graph, you must follow the pattern:
```sql
SELECT * FROM cypher('graph_name', $$ 
/* Cypher Query Here */ 
$$) AS (result1 agtype, result2 agtype);
```

Now, lets make a function to create a node in the graph. It will have two arguments `node_label` and `node_prop`. `node_label` will be the label of the node and `node_prop` the properties, which must be written in a JSON like format.
```python
def create_node(node_label, node_prop):
    
    with conn.cursor() as cursor:
        
        try:
            CREATE_NODE_CMD = f"""SELECT * FROM cypher('{GRAPH_NAME}', $$ CREATE (n:{node_label} {node_prop}) $$) as (v agtype); """
            cursor.execute(CREATE_NODE_CMD)
            conn.commit()
        
        except Exception as ex:
            print(type(ex), ex)
            conn.rollback()

# Example on how to use it:
create_node("Wizard", "{name: 'Gadwick, the Wizened'}")
```

To create nodes with an edge connecting them, we can create the function bellow:
```python
# Creates two new nodes with an edge connecting them.
def create_connected_nodes(node_one_label, node_one_prop, node_two_label, node_two_prop, edge_label, edge_prop):
    with conn.cursor() as cursor:
        try:
            CREATE_CONNECTED_NODES_CMD = f"""SELECT * FROM cypher('{GRAPH_NAME}', $$ 
                                            CREATE (n:{node_one_label} {node_one_prop})-[:{edge_label} {edge_prop}]->(m:{node_two_label} {node_two_prop}) 
                                            $$) as (a agtype);"""
            cursor.execute(CREATE_CONNECTED_NODES_CMD)
            conn.commit()
        
        except Exception as ex:
            print(type(ex), ex)
            conn.rollback

create_connected_nodes("Human", "{name: 'Beren'}", "Elf", "{name: Luthien}", "LOVES", "")
```

Function for finding two specified nodes and connecting them with an edge:
```python
# Finds two specified nodes and connects them.
def connect_two_existing_nodes(node_one_label, node_one_prop, prop_one_val, node_two_label, node_two_prop, prop_two_val, edge_label, edge_prop):
    with conn.cursor() as cursor:
        try:
            CREATE_CONNECTED_NODES_CMD = f"""SELECT * FROM cypher('{GRAPH_NAME}', $$
                                            MATCH (a:{node_one_label}), (b:{node_two_label})
                                            WHERE a.{node_one_prop} = {prop_one_val} AND b.{node_two_prop} = {prop_two_val}
                                            CREATE (a)-[:{edge_label} {edge_prop}]->(b)
                                            $$) as (a agtype);"""
            cursor.execute(CREATE_CONNECTED_NODES_CMD)
            conn.commit()
        
        except Exception as ex:
            print(type(ex), ex)
            conn.rollback

# Example
connect_two_existing_nodes("Elf", "name", "Luthien", "Human", "name", "Beren", "LOVES", "")
```

To retrieve all created nodes with the desired properties (the properties must be a list):
```python
def select_all_nodes(properties):
    with conn.cursor() as cursor:
        try:
            FIND_ALL_NODES = f"""SELECT * FROM cypher('{GRAPH_NAME}', $$ 
                                MATCH (n) 
                                RETURN n $$) as (node agtype);"""
            cursor.execute(FIND_ALL_NODES)
            for row in cursor:
                node = row[0]
                print(node.id, node.label)
                for property in properties:
                    print(node[property])
                print("-->", node)
        
        except Exception as ex:
            print(type(ex), ex)
            conn.rollback

# Example on how to use it.
select_all_nodes(["name", "title"])
```

Now, you can initialize your Postgres database and run these scripts. After you've done so, the database will be updated with the new nodes and edges.

## Continuing The Mage's Story

_Empowered by the newfound insights from the sacred texts, the data mage's mind brims with creative energy. With a determined focus, he begins crafting intricate scripts that will shape and mold graph databases to his will. Each line of code serves as a spell, an enchantment that brings the structure of knowledge to life._

_Carefully, the mage designs a script for creating graph databases, envisioning a realm where vertices and edges intertwine to form a web of interconnected information. He considers the attributes and properties that will define each vertex, giving them purpose and meaning within the vast network. The script takes shape, imbued with the mage's profound understanding of the intricate relationships between data points._

_Unbeknownst to the mage, his rival, Neo Jumbo Jimmy Jellyboo Jeebus, watches from the shadows, his eyes filled with envy and malice. Jeebus recognizes the potential of the mage's creations. Sensing an opportunity to gain an upper hand, Jeebus plots to steal the scripts and harness their power for his own nefarious purposes. The data mage now must be cautious..._