Querying AlloyDB Data as a Graph

Summary

In this tutorial, you will:

Create an AlloyDB cluster in Google Cloud and load it with example data.
Start a PuppyGraph container and connect it to AlloyDB.
Run Cypher and Gremlin queries against the AlloyDB data as a graph.

Requires a real Google Cloud project

Unlike the local-stack tutorials, this one points at a real AlloyDB cluster you create in your own GCP project. PuppyGraph treats AlloyDB as a PostgreSQL endpoint over its built-in JDBC driver, so the schema shape is identical to the PostgreSQL tutorial; only the JDBC URI and network reach differ.

Prerequisites

docker is available on the host where you'll run PuppyGraph.
A Google Cloud project with the AlloyDB API enabled, and permission to create AlloyDB clusters.
Network reachability from the PuppyGraph container to the AlloyDB primary instance (public IP with authorized networks, or a private VPC peer).

Setup

Create an AlloyDB cluster

In the GCP web console at https://console.cloud.google.com/alloydb/, create or pick an AlloyDB cluster and a primary instance. The default settings work for this demo. Note down:

The primary instance's host (public IP or private VPC address) and port (default 5432).
The database name (default postgres).
A user / password (or the default postgres user).

Network reachability

If you can't already reach the AlloyDB instance from the host that will run PuppyGraph, configure access now: either enable public IP on the instance and add your host to the authorized networks list, or set up a private VPC peer.

Data Preparation

Connect to AlloyDB with psql (the same client used in the Postgres tutorial):

psql -h <primary-instance-ip> -p 5432 -U postgres -d postgres

Paste the following SQL into the prompt to create the schema and insert data:

modern.sql

create schema modern;

create table modern.software (
    id   text,
    name text,
    lang text
);
insert into modern.software values ('v3', 'lop', 'java'), ('v5', 'ripple', 'java');

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.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);

personsoftwarecreatedknows

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
e9	v1	v3	0.4
e10	v4	v5	1.0
e11	v4	v3	0.4
e12	v6	v3	0.2

id	from_id	to_id	weight
e7	v1	v2	0.5
e8	v1	v4	1.0

Start PuppyGraph

Start the PuppyGraph container:

docker run -d --name puppygraph \
  -p 8081:8081 -p 8182:8182 -p 7687:7687 \
  -e PUPPYGRAPH_USERNAME=puppygraph \
  -e PUPPYGRAPH_PASSWORD=puppygraph123 \
  --pull=always puppygraph/puppygraph:latest

Default password

Change PUPPYGRAPH_PASSWORD before running on a publicly accessible machine.

Modeling a Graph

We model the data as the TinkerPop modern graph: two node types (person, software) and two edge types (knows, created).

First, log into the PuppyGraph Web UI at http://localhost:8081 with the credentials configured above:

Field	Value
Username	`puppygraph`
Password	`puppygraph123`

There are two ways to define the schema in PuppyGraph: build it interactively in the Schema Builder, or upload a JSON file directly. Pick whichever you prefer; both produce the same graph.

Build the graph in the Schema Builder

The Schema Builder is the visual editor in the PuppyGraph Web UI for adding catalogs, nodes, and edges step by step. For a deeper visual walkthrough of every dialog and field, see Modeling a Graph through the Schema Builder. The summary below covers what's needed to build the modern graph against AlloyDB.

PuppyGraph queries AlloyDB through its PostgreSQL-compatible wire protocol.

Connecting to AlloyDB

Click Create Catalog, then expand Cloud Data Warehouses and pick AlloyDB.

Fill in the connection form (replace the placeholders with your AlloyDB cluster details):

Field	Value
Catalog name	`alloydb_data`
Username	`postgres`
Password	(your AlloyDB user password)
JDBC Connection String	`jdbc:postgresql://<alloydb-host>:5432/postgres`

Click Create Catalog.

Adding nodes and edges

Add the software and person nodes, then the created and knows edges, the same way as in the Postgres tutorial. For each edge, set the From / To Node, map from_id and to_id as the FROM / TO Select Column, and assign id as the edge identifier.

Upload a schema file

If you've already built the graph in the Schema Builder above, you can skip this section. The resulting schema is the same.

This method writes the full schema to a JSON file and uploads it directly. It's useful when you already have a schema for an environment and want to recreate it elsewhere (e.g. for CI, scripted setup, or copy-pasting between PuppyGraph instances).

Create a file schema.json with the following content. Replace the AlloyDB host, username, and password placeholders with your cluster's values:

schema.json

{
  "catalog": [
    {
      "name": "alloydb_data",
      "type": "postgresql",
      "jdbc": {
        "username": "postgres",
        "password": "<your_password>",
        "jdbcUri": "jdbc:postgresql://<alloydb-host>:5432/postgres"
      }
    }
  ],
  "node": [
    {
      "label": "software",
      "dataSourceGroup": {
        "externalDataSource": {
          "enabled": true,
          "catalog": "alloydb_data",
          "schema": "modern",
          "table": "software",
          "mappedField": [
            { "sourceFieldName": "id",   "targetFieldName": "id"   },
            { "sourceFieldName": "name", "targetFieldName": "name" },
            { "sourceFieldName": "lang", "targetFieldName": "lang" }
          ]
        }
      },
      "id":        [{ "name": "id",   "type": "STRING" }],
      "attribute": [
        { "name": "name", "type": "STRING" },
        { "name": "lang", "type": "STRING" }
      ]
    },
    {
      "label": "person",
      "dataSourceGroup": {
        "externalDataSource": {
          "enabled": true,
          "catalog": "alloydb_data",
          "schema": "modern",
          "table": "person",
          "mappedField": [
            { "sourceFieldName": "id",   "targetFieldName": "id"   },
            { "sourceFieldName": "name", "targetFieldName": "name" },
            { "sourceFieldName": "age",  "targetFieldName": "age"  }
          ]
        }
      },
      "id":        [{ "name": "id",   "type": "STRING" }],
      "attribute": [
        { "name": "name", "type": "STRING" },
        { "name": "age",  "type": "INT"    }
      ]
    }
  ],
  "edge": [
    {
      "label":         "created",
      "fromNodeLabel": "person",
      "toNodeLabel":   "software",
      "dataSourceGroup": {
        "externalDataSource": {
          "enabled": true,
          "catalog": "alloydb_data",
          "schema": "modern",
          "table": "created",
          "mappedField": [
            { "sourceFieldName": "id",      "targetFieldName": "id"      },
            { "sourceFieldName": "from_id", "targetFieldName": "from_id" },
            { "sourceFieldName": "to_id",   "targetFieldName": "to_id"   },
            { "sourceFieldName": "weight",  "targetFieldName": "weight"  }
          ]
        }
      },
      "id":        [{ "name": "id",      "type": "STRING" }],
      "fromKey":   [{ "name": "from_id", "type": "STRING" }],
      "toKey":     [{ "name": "to_id",   "type": "STRING" }],
      "attribute": [
        { "name": "from_id", "type": "STRING" },
        { "name": "to_id",   "type": "STRING" },
        { "name": "weight",  "type": "DOUBLE" }
      ]
    },
    {
      "label":         "knows",
      "fromNodeLabel": "person",
      "toNodeLabel":   "person",
      "dataSourceGroup": {
        "externalDataSource": {
          "enabled": true,
          "catalog": "alloydb_data",
          "schema": "modern",
          "table": "knows",
          "mappedField": [
            { "sourceFieldName": "id",      "targetFieldName": "id"      },
            { "sourceFieldName": "from_id", "targetFieldName": "from_id" },
            { "sourceFieldName": "to_id",   "targetFieldName": "to_id"   },
            { "sourceFieldName": "weight",  "targetFieldName": "weight"  }
          ]
        }
      },
      "id":        [{ "name": "id",      "type": "STRING" }],
      "fromKey":   [{ "name": "from_id", "type": "STRING" }],
      "toKey":     [{ "name": "to_id",   "type": "STRING" }],
      "attribute": [
        { "name": "from_id", "type": "STRING" },
        { "name": "to_id",   "type": "STRING" },
        { "name": "weight",  "type": "DOUBLE" }
      ]
    }
  ]
}

In the Web UI, click Graph in the sidebar, then Upload Schema, and select schema.json.

Upload via CLI

You can also POST the schema directly:

curl -X POST -H "content-type: application/json" \
  --data-binary @./schema.json \
  --user "puppygraph:puppygraph123" \
  http://localhost:8081/schema

Querying the Graph

In the PuppyGraph Web UI, click Query in the sidebar. You can run graph queries in either Cypher or Gremlin.

The following query answers "What software was created by people that marko knows?"

CypherGremlin

MATCH path = (p:person)-[:knows]->()-[:created]->()
WHERE p.name = 'marko'
RETURN path;

g.V().hasLabel('person').has('name', 'marko')
  .out('knows').out('created').path()

There are two paths in the result: marko knows josh, who created lop and ripple.

Cleanup

Stop the PuppyGraph container:

docker stop puppygraph && docker rm puppygraph

Delete the AlloyDB cluster from the GCP console when you're done so it stops billing.