PuppyGraph
Search
K

PostgreSQL

A quick guide to getting started on PuppyGraph with PostgreSQL

Summary

In this guide, you will:
  • Start a PostgreSQL database and create tables with sample data in the database.
  • Start a PuppyGraph instance and query the PostgreSQL tables as a graph.

Prerequisites

Docker (with docker compose)

Please ensure that docker and docker compose CLIs are available. Docker Compose v2 is required. The version can be fetched by running:
docker compose version
See https://docs.docker.com/compose/install/ for Docker Compose installation instructions and https://www.docker.com/get-started/ for more details on Docker.

Steps

Start Services

▶️
Create a file docker-compose.yaml with the following content:
docker-compose.yaml
version: "3"
services:
puppygraph:
image: puppygraph/puppygraph:stable
pull_policy: always
container_name: puppygraph
environment:
- PUPPYGRAPH_USERNAME=puppygraph
- PUPPYGRAPH_PASSWORD=puppygraph123
networks:
postgres_net:
ports:
- "8081:8081"
- "8182:8182"
- "7687:7687"
postgres:
image: postgres:14.1-alpine
container_name: postgres
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres123
networks:
postgres_net:
ports:
- "5432:5432"
volumes:
- ./postgres-data:/var/lib/postgresql/data
networks:
postgres_net:
name: puppy-postgres
▶️
Then run the following command to start Postgres and PuppyGraph:
docker compose up -d
[+] Running 1/1
✔ puppygraph Pulled
[+] Running 3/3
✔ Network puppy-postgres Created
✔ Container postgres Started
✔ Container puppygraph Started

Prepare Data

We first prepare some data on PostgreSQL.
▶️
Run the following command to start a PostgreSQL shell to access the database:
docker exec -it postgres psql -h postgres -U postgres
It will show a password prompt:
Password for user postgres:
▶️
Input the password postgres123 to access the PostgreSQL client shell.
psql (14.1)
Type "help" for help.
postgres=#
▶️
Then execute the following SQL statements in the shell to create tables and insert data.
create schema modern;
create table modern.person (id text, name text, age integer);
insert into modern.person values
('v1', 'marko', 29),
('v2', 'vadas', 27),
('v4', 'josh', 32),
('v6', 'peter', 35);
create table modern.software (id text, name text, lang text);
insert into modern.software values
('v3', 'lop', 'java'),
('v5', 'ripple', 'java');
create table modern.created (id text, from_id text, to_id text, weight double precision);
insert into modern.created values
('e9', 'v1', 'v3', 0.4),
('e10', 'v4', 'v5', 1.0),
('e11', 'v4', 'v3', 0.4),
('e12', 'v6', 'v3', 0.2);
create table modern.knows (id text, from_id text, to_id text, weight double precision);
insert into modern.knows values
('e7', 'v1', 'v2', 0.5),
('e8', 'v1', 'v4', 1.0);
The above SQL creates the following tables:
person
software
knows
created
id
name
age
v1
marko
29
v2
vadas
27
v4
josh
32
v6
peter
35
id
name
lang
v3
lop
java
v5
ripple
java
id
from_id
to_id
weight
e7
v1
v2
0.5
e8
v1
v4
1.0
id
from_id
to_id
weight
e9
v1
v3
0.4
e10
v4
v5
1.0
e11
v4
v3
0.4
e12
v6
v3
0.2

Define a Graph

We then define a graph on top of the data tables we just created.
Modern Graph
▶️
Create a PuppyGraph schema file schema.json with the following content:
schema.json
{
"catalogs": [
{
"name": "postgres_data",
"type": "postgresql",
"jdbc": {
"username": "postgres",
"password": "postgres123",
"jdbcUri": "jdbc:postgresql://postgres:5432/postgres",
"driverClass": "org.postgresql.Driver"
}
}
],
"vertices": [
{
"label": "person",
"mappedTableSource": {
"catalog": "postgres_data",
"schema": "modern",
"table": "person",
"metaFields": {
"id": "id"
}
},
"attributes": [
{
"type": "Int",
"name": "age"
},
{
"type": "String",
"name": "name"
}
]
},
{
"label": "software",
"mappedTableSource": {
"catalog": "postgres_data",
"schema": "modern",
"table": "software",
"metaFields": {
"id": "id"
}
},
"attributes": [
{
"type": "String",
"name": "lang"
},
{
"type": "String",
"name": "name"
}
]
}
],
"edges": [
{
"label": "knows",
"mappedTableSource": {
"catalog": "postgres_data",
"schema": "modern",
"table": "knows",
"metaFields": {
"id": "id",
"from": "from_id",
"to": "to_id"
}
},
"from": "person",
"to": "person",
"attributes": [
{
"type": "Double",
"name": "weight"
}
]
},
{
"label": "created",
"mappedTableSource": {
"catalog": "postgres_data",
"schema": "modern",
"table": "created",
"metaFields": {
"id": "id",
"from": "from_id",
"to": "to_id"
}
},
"from": "person",
"to": "software",
"attributes": [
{
"type": "Double",
"name": "weight"
}
]
}
]
}
▶️
Run the following command to upload the schema file:
curl -XPOST -H "content-type: application/json" --data-binary @./schema.json --user "puppygraph:puppygraph123" localhost:8081/schema
The response shows that graph schema has been uploaded successfully:
{"Status":"OK","Message":"Schema uploaded and gremlin server restarted"}

Query the Graph

Now we can query the graph through PuppyGraph CLI
▶️
Access the puppygraph CLI by running the following command
docker exec -it puppygraph ./bin/puppygraph
▶️
In the PuppyGraph CLI, type in console to start a Gremlin console.
____ ____ _
| _ \ _ _ _ __ _ __ _ _ / ___| _ __ __ _ _ __ | |__
| |_) | | | | | | '_ \ | '_ \ | | | | | | _ | '__| / _` | | '_ \ | '_ \
| __/ | |_| | | |_) | | |_) | | |_| | | |_| | | | | (_| | | |_) | | | | |
|_| \__,_| | .__/ | .__/ \__, | \____| |_| \__,_| | .__/ |_| |_|
|_| |_| |___/ |_|
Welcome to PuppyGraph, type help to see the command list
[PuppyGraph]> console
You can then query the graph we just defined using Gremlin query language.
▶️
Try out the following queries in the console.
g.V()
g.E()
g.V().count()
g.E().count()
g.V().outE().otherV().path()
g.V().elementMap()
g.E().elementMap()
The results are like the following:
gremlin> g.V()
==>v[software:::v5]
==>v[software:::v3]
==>v[person:::v4]
==>v[person:::v6]
==>v[person:::v1]
==>v[person:::v2]
gremlin> g.E()
==>e[created:::e10][person:::v4-created->software:::v5]
==>e[created:::e11][person:::v4-created->software:::v3]
==>e[created:::e12][person:::v6-created->software:::v3]
==>e[created:::e9][person:::v1-created->software:::v3]
==>e[knows:::e7][person:::v1-knows->person:::v2]
==>e[knows:::e8][person:::v1-knows->person:::v4]
gremlin> g.V().count()
==>6
gremlin> g.E().count()
==>6
gremlin> g.V().outE().otherV().path()
==>path[v[person:::v4], e[created:::e10][person:::v4-created->software:::v5], v[software:::v5]]
==>path[v[person:::v6], e[created:::e12][person:::v6-created->software:::v3], v[software:::v3]]
==>path[v[person:::v1], e[created:::e9][person:::v1-created->software:::v3], v[software:::v3]]
==>path[v[person:::v4], e[created:::e11][person:::v4-created->software:::v3], v[software:::v3]]
==>path[v[person:::v1], e[knows:::e7][person:::v1-knows->person:::v2], v[person:::v2]]
==>path[v[person:::v1], e[knows:::e8][person:::v1-knows->person:::v4], v[person:::v4]]
gremlin> g.V().elementMap()
==>{id=software:::v3, label=software, name=lop, lang=java}
==>{id=software:::v5, label=software, name=ripple, lang=java}
==>{id=person:::v4, label=person, name=josh, age=32}
==>{id=person:::v6, label=person, name=peter, age=35}
==>{id=person:::v1, label=person, name=marko, age=29}
==>{id=person:::v2, label=person, name=vadas, age=27}
gremlin> g.E().elementMap()
==>{id=created:::e10, label=created, IN={id=software:::v5, label=software}, OUT={id=person:::v4, label=person}, weight=1.0}
==>{id=created:::e11, label=created, IN={id=software:::v3, label=software}, OUT={id=person:::v4, label=person}, weight=0.4}
==>{id=created:::e12, label=created, IN={id=software:::v3, label=software}, OUT={id=person:::v6, label=person}, weight=0.2}
==>{id=created:::e9, label=created, IN={id=software:::v3, label=software}, OUT={id=person:::v1, label=person}, weight=0.4}
==>{id=knows:::e7, label=knows, IN={id=person:::v2, label=person}, OUT={id=person:::v1, label=person}, weight=0.5}
==>{id=knows:::e8, label=knows, IN={id=person:::v4, label=person}, OUT={id=person:::v1, label=person}, weight=1.0}
gremlin>
▶️
To exit the Gremlin Console, type in and press enter
:exit

Cleanup

▶️
To exit PuppyGraph CLI, type in and press enter
exit
▶️
Run the following command to shut down and remove the services:
docker compose down

See Also

Last modified 2mo ago