view release on metacpan or  search on metacpan

README.md  view on Meta::CPAN

# DBIO::PostgreSQL::Age

Apache AGE graph database extension support for DBIO::PostgreSQL.

## Supports

- Apache AGE openCypher graph queries ([DBIO::PostgreSQL::Age::Storage](https://metacpan.org/pod/DBIO::PostgreSQL::Age::Storage))
- graph creation and deletion lifecycle
- cypher() SQL function execution via [DBIO::PostgreSQL::Age::Storage/cypher](https://metacpan.org/pod/DBIO::PostgreSQL::Age::Storage)
- integration with [DBIO::PostgreSQL](https://metacpan.org/pod/DBIO::PostgreSQL) base driver

## Usage

    package MyApp::Schema;
    use base 'DBIO::Schema';
    __PACKAGE__->load_components('PostgreSQL::Age');

    my $schema = MyApp::Schema->connect(
      $dsn, $user, $pass,
      { on_connect_call => 'load_age' },
    );

    $schema->storage->create_graph('social');

    my $rows = $schema->storage->cypher(
      'social',
      'MATCH (a:Person)-[:KNOWS]->(b:Person) RETURN a.name, b.name',
      [qw( person friend )],
    );

DBIO core autodetects `dbi:Pg:` DSNs with the PostgreSQL driver, and
[DBIO::PostgreSQL::Age](https://metacpan.org/pod/DBIO::PostgreSQL::Age) is loaded via `load_components`.

## Apache AGE Features

**Graph Operations**
- `create_graph($name)` - create a named graph
- `drop_graph($name, $cascade)` - drop a graph (cascade drops vertices and edges too)
- `cypher($graph, $query, \@columns, \%params, \%opts)` - execute openCypher query
- `decode_agtype($value)` - decode a single agtype text value into native Perl data
- `connect_call_load_age` - connection callback: `LOAD 'age'` + `SET search_path = ag_catalog, ...`

By default `cypher()` returns each result cell as the raw agtype text
that PostgreSQL hands back over the wire — strings are quoted (`"alice"`),
maps and vertices are JSON, and so on. Pass `{ auto_decode => 1 }` as the
fifth argument to apply [`decode_agtype`](https://metacpan.org/pod/DBIO::PostgreSQL::Age::Storage#decode_agtype)
to every cell of every row, so you get back plain Perl strings, numbers,
hashrefs, arrayrefs, vertex hashrefs (`{ id, label, properties }`), and
edge hashrefs (`{ id, label, start_id, end_id, properties }`).

**openCypher Support**
- `MATCH`, `OPTIONAL MATCH` - graph pattern matching
- `WHERE` - filtering on node/relationship properties
- `RETURN`, `RETURN DISTINCT` - result projection
- `ORDER BY`, `SKIP`, `LIMIT` - pagination
- `WITH` - query chaining and aggregation (`count()`, `collect()`, ...)
- `CREATE`, `MERGE`, `SET`, `REMOVE` - graph mutation
- `DELETE`, `DETACH DELETE` - graph deletion
- Variable-length paths: `()-[:KNOWS*1..3]->()`
- Node labels and relationship types
- Parameterized queries via `$name` placeholders

**Labels & Types**
- Node labels: `(:Person)`, `(:Person {name: 'Alice'})`
- Relationship types: `[:KNOWS]`, `[:KNOWS {since: 2020}]`
- Multiple labels: `(:Person:Employee)`
- Multiple relationships: `(a)-[:KNOWS]->(b)-[:WORKS_WITH]->(c)`

## Testing

Three layers, each more involved than the last:

### 1. Offline unit tests (no database needed)

```bash
prove -l t/20-cypher.t t/21-agtype.t t/30-registry.t t/00-load.t
```

Covers `cypher()` SQL generation, `decode_agtype` for every agtype shape
(vertex / edge / path / scalar / null / bool / map / list), and the
regression that loading AGE storage must not hijack the plain `'Pg'`
driver registry.

### 2. Local live tests (docker compose)

Spins up PostgreSQL 18 with the AGE extension preinstalled (image
`apache/age:latest`, which is `postgres:18` + AGE 1.7):

```bash
docker compose up -d
# wait for "database system is ready to accept connections"
DBIO_TEST_PG_DSN="dbi:Pg:dbname=dbio_age_test;host=127.0.0.1;port=54329" \
DBIO_TEST_PG_USER=postgres \
DBIO_TEST_PG_PASS=dbio_age_test \
  prove -lr t/
docker compose down -v
```