Graph Databases

-- The relational way: entities in one table, connections in another.
CREATE TABLE users (
    user_id  INTEGER PRIMARY KEY,
    name     VARCHAR(50) NOT NULL,
    location VARCHAR(50)
);

-- The join table exists only because SQL has no other way to express
-- a many-to-many link. It is bookkeeping, not domain modelling.
CREATE TABLE friendships (
    user_id   INTEGER REFERENCES users(user_id),
    friend_id INTEGER REFERENCES users(user_id),
    since     DATE NOT NULL,
    PRIMARY KEY (user_id, friend_id)
);

INSERT INTO users VALUES (1,'Alice','NYC'), (2,'Bob','SF'), (3,'Charlie','NYC');
INSERT INTO friendships VALUES
    (1, 2, '2023-01-15'),
    (1, 3, '2023-02-20'),
    (2, 3, '2023-03-10');

-- "Friends of friends": one self-join per degree of separation.
SELECT DISTINCT f2.friend_id
FROM friendships f1
JOIN friendships f2 ON f1.friend_id = f2.user_id
WHERE f1.user_id = 1 AND f2.friend_id != 1;

// The graph way: the connection is a first-class object with its own
// type and properties, stored alongside the nodes it joins. Nodes and
// the relationships between them are created in one statement.
CREATE (a:Person {id: 1, name: 'Alice',   location: 'NYC'}),
       (b:Person {id: 2, name: 'Bob',     location: 'SF'}),
       (c:Person {id: 3, name: 'Charlie', location: 'NYC'}),
       (a)-[:FRIENDS_WITH {since: '2023-01-15'}]->(b),
       (a)-[:FRIENDS_WITH {since: '2023-02-20'}]->(c),
       (b)-[:FRIENDS_WITH {since: '2023-03-10'}]->(c);

// The same question the SQL asked. *2 means "exactly two hops".
MATCH (me:Person {name: 'Alice'})-[:FRIENDS_WITH*2]->(fof)
WHERE fof <> me
RETURN DISTINCT fof.name;

// Widening the search is one token: *2 becomes *1..3.
MATCH (me:Person {name: 'Alice'})-[:FRIENDS_WITH*1..3]->(other)
WHERE other <> me
RETURN DISTINCT other.name ORDER BY other.name;

-- The same widening in SQL. Not another JOIN: a different query.
WITH RECURSIVE reachable(id, depth) AS (
    SELECT friend_id, 1 FROM friendships WHERE user_id = 1
    UNION
    SELECT f.friend_id, r.depth + 1
    FROM reachable r JOIN friendships f ON f.user_id = r.id
    WHERE r.depth < 3
)
SELECT DISTINCT u.name FROM reachable r JOIN users u ON u.user_id = r.id
WHERE r.id != 1 ORDER BY u.name;

from neo4j import GraphDatabase

class Neo4jConnection:
    def __init__(self, uri, user, password):
        self.driver = GraphDatabase.driver(uri, auth=(user, password))

    def close(self):
        self.driver.close()

    def query(self, cypher_query, parameters=None):
        """Execute a Cypher query and return results"""
        with self.driver.session() as session:
            result = session.run(cypher_query, parameters or {})
            return [record.data() for record in result]

# Create connection
conn = Neo4jConnection(
    uri="bolt://localhost:7687",
    user="neo4j",
    password="testpassword123"
)

print("✓ Connected to Neo4j")

# This block and every one after it continue the script from "Connecting
# to Neo4j" above: conn is the Neo4jConnection created there. Keep them in
# one file, or paste them into the same interpreter session.

# Start from an empty graph. Two reasons this matters:
#   1. The comparison section earlier in this lesson created its own
#      Alice, Bob and Charlie. Without this you end up with two of each.
#   2. CREATE always inserts. It is not idempotent, so re-running this
#      block would duplicate everything again.
conn.query("MATCH (n) DETACH DELETE n")

# Create Person nodes
create_person = """
CREATE (p:Person {
    name: $name,
    age: $age,
    email: $email,
    location: $location
})
RETURN p
"""

# Create multiple people
people = [
    {"name": "Alice", "age": 28, "email": "alice@example.com", "location": "NYC"},
    {"name": "Bob", "age": 32, "email": "bob@example.com", "location": "SF"},
    {"name": "Charlie", "age": 25, "email": "charlie@example.com", "location": "NYC"},
    {"name": "Diana", "age": 30, "email": "diana@example.com", "location": "LA"},
    {"name": "Eve", "age": 27, "email": "eve@example.com", "location": "SF"}
]

print("Creating person nodes...")
for person in people:
    result = conn.query(create_person, person)
    print(f"  Created: {person['name']}")

print("✓ All person nodes created!")

# Create FRIENDS_WITH relationships
create_friendship = """
MATCH (a:Person {name: $person1})
MATCH (b:Person {name: $person2})
CREATE (a)-[r:FRIENDS_WITH {since: $since}]->(b)
RETURN a.name, b.name, r.since
"""

# Define friendships
friendships = [
    {"person1": "Alice", "person2": "Bob", "since": "2023-01-15"},
    {"person1": "Alice", "person2": "Charlie", "since": "2023-02-20"},
    {"person1": "Bob", "person2": "Charlie", "since": "2023-03-10"},
    {"person1": "Bob", "person2": "Diana", "since": "2023-04-05"},
    {"person1": "Charlie", "person2": "Eve", "since": "2023-05-12"},
    {"person1": "Diana", "person2": "Eve", "since": "2023-06-08"}
]

print("Creating friendship relationships...")
for friendship in friendships:
    result = conn.query(create_friendship, friendship)
    if result:
        print(f"  {friendship['person1']} -> {friendship['person2']}")

print("✓ All friendships created!")

# Query 1: Find all people
query = """
MATCH (p:Person)
RETURN p.name, p.age, p.location
ORDER BY p.age DESC
"""

result = conn.query(query)

print("All people:")
for record in result:
    print(f"  {record['p.name']}, {record['p.age']} years old, {record['p.location']}")

# Query 2: Find all friendships
query = """
MATCH (a:Person)-[r:FRIENDS_WITH]->(b:Person)
RETURN a.name AS person1, b.name AS person2, r.since AS since
ORDER BY since
"""

result = conn.query(query)

print("\nAll friendships:")
for record in result:
    print(f"  {record['person1']} -> {record['person2']} (since {record['since']})")

# Find friends of friends (2 degrees)
query = """
MATCH (me:Person {name: $my_name})-[:FRIENDS_WITH*2]->(fof:Person)
WHERE fof <> me
RETURN DISTINCT fof.name AS friend_of_friend
ORDER BY friend_of_friend
"""

result = conn.query(query, {"my_name": "Alice"})

print("Alice's friends of friends:")
for record in result:
    print(f"  {record['friend_of_friend']}")

# Find mutual friends
query = """
MATCH (me:Person {name: $person1})-[:FRIENDS_WITH]->(mutual:Person)
      <-[:FRIENDS_WITH]-(them:Person {name: $person2})
RETURN mutual.name AS mutual_friend
"""

result = conn.query(query, {"person1": "Alice", "person2": "Bob"})

print("\nMutual friends of Alice and Bob:")
for record in result:
    print(f"  {record['mutual_friend']}")

# Find shortest path between two people
query = """
MATCH path = shortestPath(
    (start:Person {name: $start_name})-[:FRIENDS_WITH*]-(end:Person {name: $end_name})
)
RETURN [node in nodes(path) | node.name] AS path_names,
       length(path) AS degrees_of_separation
"""

result = conn.query(query, {"start_name": "Alice", "end_name": "Eve"})

if result:
    path = result[0]
    print(f"Shortest path from Alice to Eve:")
    print(f"  Path: {' -> '.join(path['path_names'])}")
    print(f"  Degrees of separation: {path['degrees_of_separation']}")

# Find all paths within N degrees
query = """
MATCH path = (start:Person {name: $name})-[:FRIENDS_WITH*1..3]-(connected:Person)
WHERE start <> connected
WITH connected.name AS person, MIN(length(path)) AS degrees
RETURN person, degrees
ORDER BY degrees, person
"""

result = conn.query(query, {"name": "Alice"})

print("\nPeople connected to Alice (within 3 degrees):")
current_degree = None
for record in result:
    if record['degrees'] != current_degree:
        current_degree = record['degrees']
        print(f"  {current_degree} degree{'s' if current_degree > 1 else ''}:")
    print(f"    {record['person']}")

# Create product nodes
create_products = """
UNWIND $products AS product
CREATE (p:Product {
    id: product.id,
    name: product.name,
    category: product.category,
    price: product.price
})
"""

products = [
    {"id": "P1", "name": "Laptop", "category": "Electronics", "price": 999},
    {"id": "P2", "name": "Mouse", "category": "Electronics", "price": 29},
    {"id": "P3", "name": "Keyboard", "category": "Electronics", "price": 79},
    {"id": "P4", "name": "Monitor", "category": "Electronics", "price": 299},
    {"id": "P5", "name": "Desk Chair", "category": "Furniture", "price": 199}
]

conn.query(create_products, {"products": products})
print("✓ Created products")

# Create PURCHASED relationships
create_purchases = """
MATCH (u:Person {name: $user})
MATCH (p:Product {id: $product_id})
CREATE (u)-[:PURCHASED {date: $date, rating: $rating}]->(p)
"""

purchases = [
    {"user": "Alice", "product_id": "P1", "date": "2024-01-15", "rating": 5},
    {"user": "Alice", "product_id": "P2", "date": "2024-01-15", "rating": 4},
    {"user": "Bob", "product_id": "P1", "date": "2024-02-10", "rating": 5},
    {"user": "Bob", "product_id": "P3", "date": "2024-02-10", "rating": 5},
    {"user": "Bob", "product_id": "P4", "date": "2024-02-12", "rating": 4},
    {"user": "Charlie", "product_id": "P2", "date": "2024-03-05", "rating": 4},
    {"user": "Charlie", "product_id": "P3", "date": "2024-03-05", "rating": 5},
    {"user": "Diana", "product_id": "P1", "date": "2024-04-01", "rating": 5},
    {"user": "Diana", "product_id": "P5", "date": "2024-04-02", "rating": 5}
]

for purchase in purchases:
    conn.query(create_purchases, purchase)

print("✓ Created purchase relationships")

# Recommendation: "Users who bought X also bought Y"
query = """
MATCH (me:Person {name: $user_name})-[:PURCHASED]->(p:Product)
MATCH (p)<-[:PURCHASED]-(other:Person)-[:PURCHASED]->(rec:Product)
WHERE NOT (me)-[:PURCHASED]->(rec)
  AND me <> other
RETURN rec.name AS product,
       rec.price AS price,
       COUNT(DISTINCT other) AS bought_by_similar_users,
       AVG(other.age) AS avg_buyer_age
ORDER BY bought_by_similar_users DESC, price ASC
LIMIT 5
"""

result = conn.query(query, {"user_name": "Alice"})

print("Recommended products for Alice:")
print("(Based on users who bought similar products)\n")
for i, record in enumerate(result, 1):
    print(f"{i}. {record['product']} - ${record['price']}")
    print(f"   Bought by {record['bought_by_similar_users']} similar users")
    print(f"   Avg buyer age: {record['avg_buyer_age']:.0f}")
    print()

# Recommendation: "Your friends bought these products"
query = """
MATCH (me:Person {name: $user_name})-[:FRIENDS_WITH*1..2]-(friend:Person)
MATCH (friend)-[r:PURCHASED]->(p:Product)
WHERE NOT (me)-[:PURCHASED]->(p)
  AND me <> friend
WITH p, friend, r
ORDER BY friend.name
RETURN p.name AS product,
       p.price AS price,
       COUNT(DISTINCT friend) AS friend_count,
       AVG(r.rating) AS avg_rating,
       COLLECT(DISTINCT friend.name)[0..3] AS example_friends
ORDER BY friend_count DESC, avg_rating DESC
LIMIT 5
"""

result = conn.query(query, {"user_name": "Alice"})

print("Products your friends bought:")
for i, record in enumerate(result, 1):
    print(f"{i}. {record['product']} - ${record['price']}")
    print(f"   {record['friend_count']} friend(s) bought this")
    print(f"   Avg rating: {record['avg_rating']:.1f}/5")
    print(f"   Friends: {', '.join(record['example_friends'])}")
    print()

# Find most connected people (degree centrality)
query = """
MATCH (p:Person)
OPTIONAL MATCH (p)-[r:FRIENDS_WITH]-(friend)
WITH p, COUNT(DISTINCT friend) AS friend_count
RETURN p.name AS person,
       friend_count,
       CASE
         WHEN friend_count >= 3 THEN 'Highly Connected'
         WHEN friend_count >= 2 THEN 'Well Connected'
         ELSE 'Few Connections'
       END AS status
ORDER BY friend_count DESC, person
"""

result = conn.query(query)

print("Social network centrality:")
for record in result:
    print(f"  {record['person']}: {record['friend_count']} friends ({record['status']})")

# Find communities based on location
query = """
MATCH (p:Person)
WITH p ORDER BY p.name
WITH p.location AS location, COLLECT(p.name) AS people
RETURN location,
       people,
       SIZE(people) AS member_count
ORDER BY member_count DESC, location
"""

result = conn.query(query)

print("Communities by location:")
for record in result:
    print(f"  {record['location']}: {record['member_count']} members")
    print(f"    Members: {', '.join(record['people'])}")
    print()

# Find tightly-knit groups (triangles)
query = """
MATCH (a:Person)-[:FRIENDS_WITH]-(b:Person)-[:FRIENDS_WITH]-(c:Person)-[:FRIENDS_WITH]-(a)
WHERE a.name < b.name AND b.name < c.name
RETURN a.name AS person1, b.name AS person2, c.name AS person3
"""

result = conn.query(query)

print("Tightly-knit friend groups (triangles):")
for record in result:
    print(f"  {record['person1']} - {record['person2']} - {record['person3']}")

from gremlin_python.driver import client, serializer

# Connect to Neptune endpoint
neptune_endpoint = "wss://your-neptune-cluster.region.neptune.amazonaws.com:8182/gremlin"

gremlin_client = client.Client(
    neptune_endpoint,
    'g',
    message_serializer=serializer.GraphSONSerializersV3d0()
)

# Add a vertex (node)
query = """
g.addV('Person')
 .property('name', 'Alice')
 .property('age', 28)
 .property('location', 'NYC')
"""

result = gremlin_client.submit(query).all().result()
print("✓ Created person vertex")

# Add an edge (relationship)
query = """
g.V().has('Person', 'name', 'Alice').as('a')
 .V().has('Person', 'name', 'Bob').as('b')
 .addE('FRIENDS_WITH')
 .from('a').to('b')
 .property('since', '2023-01-15')
"""

result = gremlin_client.submit(query).all().result()
print("✓ Created friendship edge")

# Traversal query
query = """
g.V().has('Person', 'name', 'Alice')
 .out('FRIENDS_WITH')
 .values('name')
"""

result = gremlin_client.submit(query).all().result()

print("\nAlice's friends:")
for friend in result:
    print(f"  {friend}")

# The harness that produced the tables below. Needs both drivers:
#   pip install "psycopg[binary]" neo4j
import statistics
import time

import psycopg
from neo4j import GraphDatabase

PG_DSN = "host=localhost dbname=demo user=demo password=demo"
NEO4J_URI, NEO4J_AUTH = "bolt://localhost:7687", ("neo4j", "testpassword123")

# Query A walks out from one person; Query B looks for a path between two.
START = 84599
PATH_FROM, PATH_TO = 42446, 23154
REPS = 10


def sql_n_hops(n):
    """Build the N-self-join query. One JOIN per hop, aliases f1..fN."""
    joins = "\n".join(
        f"JOIN bench_edges f{i} ON f{i-1}.friend_id = f{i}.user_id"
        for i in range(2, n + 1)
    )
    return (f"SELECT DISTINCT f{n}.friend_id\nFROM bench_edges f1\n{joins}\n"
            f"WHERE f1.user_id = %s AND f{n}.friend_id <> %s")


def timed(fn, reps=REPS):
    """Median of REPS runs after one warm-up. Fetching is inside the timer:
    a query that streams lazily would otherwise look free."""
    fn()
    times = []
    for _ in range(reps):
        t0 = time.perf_counter()
        rows = fn()
        times.append((time.perf_counter() - t0) * 1000)
    return statistics.median(times), rows


pg = psycopg.connect(PG_DSN)
driver = GraphDatabase.driver(NEO4J_URI, auth=NEO4J_AUTH)

print(f"{'hops':>5} {'PostgreSQL':>12} {'Neo4j':>12} {'rows(pg)':>10} {'rows(neo)':>10}")
for hops in (2, 3, 4, 5, 6):
    query = sql_n_hops(hops)
    cypher = (f"MATCH (me:Person {{id: $start}})-[:FRIENDS_WITH*{hops}]->(p:Person) "
              f"WHERE p.id <> $start RETURN DISTINCT p.id")

    def run_pg():
        with pg.cursor() as cur:
            cur.execute(query, (START, START))
            return len(cur.fetchall())

    def run_neo():
        with driver.session() as session:
            return len(list(session.run(cypher, start=START)))

    pg_ms, pg_rows = timed(run_pg)
    neo_ms, neo_rows = timed(run_neo)
    print(f"{hops:>5} {pg_ms:>10.1f}ms {neo_ms:>10.1f}ms {pg_rows:>10,} {neo_rows:>10,}")


# ---- QUERY B: unbounded shortest path ----------------------------------
BFS_SQL = """
WITH RECURSIVE bfs(node, depth) AS (
    SELECT %s, 0
  UNION
    SELECT f.friend_id, b.depth + 1
    FROM bfs b JOIN bench_edges f ON f.user_id = b.node
    WHERE b.depth < 6
)
SELECT MIN(depth) FROM bfs WHERE node = %s
"""
SHORTEST_CYPHER = """
MATCH p = shortestPath(
    (x:Person {id: $x})-[:FRIENDS_WITH*..6]->(y:Person {id: $y})
)
RETURN length(p) AS len
"""


def bfs_pg():
    with pg.cursor() as cur:
        cur.execute(BFS_SQL, (PATH_FROM, PATH_TO))
        return cur.fetchone()[0]


def shortest_neo():
    with driver.session() as session:
        record = session.run(SHORTEST_CYPHER, x=PATH_FROM, y=PATH_TO).single()
        return record["len"] if record else None


pg_ms, pg_depth = timed(bfs_pg)
neo_ms, neo_len = timed(shortest_neo)
print(f"\nQuery B, shortest path {PATH_FROM} -> {PATH_TO}")
print(f"  PostgreSQL {pg_ms:8.1f} ms   (depth found: {pg_depth})")
print(f"  Neo4j      {neo_ms:8.1f} ms   (path length: {neo_len})")
print(f"  -> Neo4j {pg_ms / neo_ms:.0f}x faster")

# Why the CTE is slow: count what it actually materialises.
with pg.cursor() as cur:
    cur.execute("""
        WITH RECURSIVE bfs(node, depth) AS (
            SELECT %s, 0
          UNION
            SELECT f.friend_id, b.depth + 1
            FROM bfs b JOIN bench_edges f ON f.user_id = b.node
            WHERE b.depth < 6
        )
        SELECT count(*) FROM bfs
    """, (PATH_FROM,))
    print(f"  (node, depth) rows the CTE materialises: {cur.fetchone()[0]:,}")

pg.close()
driver.close()