Rails 7.1.x - Graph AGE Integration Exploration

Explore Rails Integration with Postgres Apache AGE GraphDB

Graph Databases are very interesting technologies for managing social networks, recommendation engines, etc.

In this article, I want to explore using Apache AGE within a Rails Application. This article assumes a working knowledge of OpenCypher and Apache AGE. If you want to get started you can find an Intro and further resources at: https://btihen.dev/posts/tech/graphdb_getting_started_age_1_5_0/

This code can be found at: https://github.com/btihen-dev/rails_graphdb_age

NO CODE YET AVAILABLE - this is a Document in PROGRESS!

Getting Started

I will assume you have the AGE extension already installed in your Postgres instance and you understand OpenCypher at least casually. If not please first visit: https://btihen.dev/posts/tech/graphdb_getting_started_age_1_5_0/

As we have done in a few apps, we will build a Flintstone’s App. Let’s create our rails app:

rails _7.1.3.2_ new graphdb_age -T -d postgresql \
      --css=bootstrap
cd graphdb_age

Config Postgress Connections

No matter how you installed Apache Age, you now need to do the following withing postgres:

CREATE EXTENSION IF NOT EXISTS age;

LOAD 'age';

SET search_path = ag_catalog, "$user", public;

To do this config we need to update our config & create migrations

Config

I assume you have installed Apache AGE and understand Cypher - if not please read GraphDB - Apache AGE 1.5.0

Quick install summary using Docker:

docker pull apache/age

Start Docker with (please change the password & username)

docker run \
    --name myPostgresDb  \
    -p 5455:5432 \
    -e POSTGRES_USER=postgresUser \
    -e POSTGRES_PASSWORD=postgresPW \
    -e POSTGRES_DB=postgresDB \
    -d \
    apache/age

Now configuring the DB config/database.yml to look like:

# config/database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  username: postgresUser
  password: postgresPW
  host: localhost
  port: 5455
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
...

now lets check our setup with:

bin/rails db:setup

Migration for the Schema

we can create the migration with:

bin/rails g migration AddAgeSetup

Now write the migration:

class AddAgeSetup < ActiveRecord::Migration[7.1]
  def change
    # allow age extension
    execute("CREATE EXTENSION IF NOT EXISTS age;")
    # load the age code
    execute("LOAD 'age';")
    # load the ag_catalog into the search path
    execute('SET search_path = ag_catalog, "$user", public;')
    # creates our AGE schema
    # USE: `execute("SELECT create_graph('age_schema');")`, as we need to use: `create_graph`
    # NOT: `ActiveRecord::Base.connection.create_schema('age_schema')`
    execute("SELECT create_graph('age_schema');")
  end
end

Now migrate:

bin/rails db:migrate

Now we see the warnings:

unknown OID 4089: failed to recognize type of 'namespace'. It will be treated as String.
unknown OID 2205: failed to recognize type of 'relation'. It will be treated as String.

Let’s log into our DB and see why these warning are happening:

# do this once
export PGPASSWORD=postgresPW

# now access the database
psql -d graphdb_age_development -U postgresUser -h localhost -p 5455

# let's check for our schema using `\dn`:
graphdb_age_development=# \dn

# and we see
        List of schemas
    Name    |       Owner
------------+-------------------
 ag_catalog | postgresUser
 age_schema | postgresUser
 public     | pg_database_owner
(3 rows)

# exit with `\q`
graphdb_age_development=# \q

So we thought we created one, but we really have 2 new schemas ag_catalog has our age configs the OIDs that couldn’t be found.

To fix this we add our graph schemas to config/database.yml - using (put ag_catalog BEFORE age_schema as we need to access the AGE infos before we use age_schema) thus we add: schema_search_path: 'ag_catalog,age_schema,public' This needs to be added to the DB Application config - so the PG config file will now look like:

# config/database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  username: postgresUser
  password: postgresPW
  host: localhost
  port: 5455
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  schema_search_path: 'ag_catalog,age_schema,public'
...

Now our config should be complete and we can use: bin/rails db instead of using: psql -d graphdb_age_development -U postgresUser -h localhost -p 5455

NOTE: if you load ag_catalog,age_schema at our top level of yaml file (as the documentation suggests) you will get the error: '{ schema_search_path => ag_catalog,age_schema,public }' is not a valid configuration. Expected 'ag_catalog,age_schema,public' to be a URL string or a Hash. (ActiveRecord::DatabaseConfigurations::InvalidConfigurationError)

Let Explore

First Exploration

Before we create a Rails strategy, let’s explore a bit using the Rails console by using: bin/rails c

Now let’s try to create some nodes using raw Cypher SQL. We can start with a very simple Data class to help use create People.

Person = Data.define(:first_name, :last_name, :given_name, :gender) do
  def initialize(first_name:, last_name:, given_name: nil, gender:)
    given_name = given_name || last_name
    super(first_name:, last_name:, given_name:, gender:)
  end

  def to_s
    %{:Person {first_name: '#{first_name}', last_name: '#{last_name}', given_name: '#{given_name}', gender: '#{gender}'}}
  end
end

zed = Person.new(first_name: 'Zed', last_name: 'Flintstone', gender: 'male')
#<data Person first_name="Zed", last_name="Flintstone", given_name="Flintstone", gender="male">

zed.to_s
# "p:Person {first_name: 'Zed', last_name: 'Flintstone', given_name: 'Flintstone', gender: 'male'}"

def create_person(person)
  schema_name = 'age_schema'
  node = 'node'

  sql = <<-SQL
    SELECT *
    FROM cypher('#{schema_name}', $$
        CREATE (#{node}#{person})
    RETURN #{node}
    $$) as (#{node} agtype);
  SQL

  result = ActiveRecord::Base.connection.execute(sql)
  json_data = result.to_a.first[node].split('::vertex').first

  JSON.parse(json_data)
end

result = create_person(zed)

{"id"=>844424930131972,
 "label"=>"Person",
 "properties"=>
 {"gender"=>"male", "last_name"=>"Flintstone", "first_name"=>"Zed", "given_name"=>"Flintstone"}}

exit

Second Exploration

Let’s drop our database and see if we can make a more useful Ruby class that might work better as an Entity (an Object with a unique ID):

bin/rails db:drop
bin/rails db:create
bin/rails db:migrate
bin/rails c

Ok this looks interesting - maybe we can make a regular class using:

# not necessary
ActiveRecord::Base.connection.execute('CREATE TYPE Person')

# without a namespace I get active record errors
class Person
end
zed = Person.new
`exec': ERROR:  relation "base_nodes" does not exist (PG::UndefinedTable) LINE 10:  WHERE a.attrelid = '"base_nodes"'::regclass`

# with a namespace things work again
module Node
  class Person
    attr_reader :age_hash, :id, :first_name, :last_name, :given_name, :gender

    AGE_TYPE = 'node'.freeze
    CLASS_NAME = 'Person'.freeze
    GRAPH_NAME = 'age_schema'.freeze

    def initialize(id: nil, first_name:, last_name:, given_name: nil, gender:)
      @id = id
      @given_name = given_name || last_name
      @first_name = first_name
      @last_name = last_name
      @gender = gender
    end

    def persisted? = id.present?

    def to_s
      %{:#{CLASS_NAME} {first_name: '#{first_name}', last_name: '#{last_name}', given_name: '#{given_name}', gender: '#{gender}'}}
    end

    def to_h = { id:, gender:, last_name:, first_name:, given_name: }

    def to_age
      {
        id:,
        label: CLASS_NAME,
        properties: {
          gender:, last_name:, first_name:, given_name:
        }
      }
    end

    def create
      age_result = ActiveRecord::Base.connection.execute(create_sql)
      json_data = age_result.to_a.first[AGE_TYPE].split('::vertex').first

      hash = JSON.parse(json_data)

      @id = hash['id']
      hash
    end

    private

    def create_sql
      <<-SQL
        SELECT *
        FROM cypher('#{GRAPH_NAME}', $$
            CREATE (#{AGE_TYPE}#{self})
        RETURN #{AGE_TYPE}
        $$) as (#{AGE_TYPE} agtype);
      SQL
    end
  end
end
zed = Node::Person.new(first_name: 'Zed', last_name: 'Flintstone', gender: 'male')

zed.to_s
zed.to_h
zed.persisted?
zed.create
zed.persisted?
zed.to_h
zed.to_age

Third Exploration

Can we create some Abstractions to make it easier to write two different Nodes - for example :Person and :Company ?

bin/rails db:drop
bin/rails db:create
bin/rails db:migrate
bin/rails c

Let’s make an AGE Module and a Node class and maybe we can use some of ActiveModel so we can easily access our attributes.

## Let's see if we can make some of the constants generic
module ApacheAge
  module Vertex
    def age_type = 'vertex'
    def age_label = self.class.name.split('::').last
    def age_graph = self.class.name.split('::').first.underscore
  end
end

module StoneAge
  class Person
    include ApacheAge::Vertex
  end
end

zed = StoneAge::Person.new
zed.age_label
# 'Person'
zed.age_graph
# 'stone_age'
zed.age_type
# 'vertex'

with this start we could expand Vertex and Person with ActiveModel:

module ApacheAge
  module Vertex
    include ActiveModel::Model
    include ActiveModel::Attributes
    include ActiveModel::Validations

    def age_type = 'vertex'
    def age_label = self.class.name.split('::').last
    def age_graph = self.class.name.split('::').first.underscore

    def persisted? = @id.present?
  end
end

module StoneAge
  class Person
    include ApacheAge::Vertex

    attribute :id, :integer
    attribute :first_name, :string
    attribute :last_name, :string
    attribute :given_name, :string
    attribute :gender, :string

    def initialize(id: nil, first_name:, last_name:, given_name: nil, gender:)
      super # without this `@attributes` is nil and creates
      self.id = id
      self.given_name = given_name || last_name
      self.first_name = first_name
      self.last_name = last_name
      self.gender = gender
    end
  end
end

zed = StoneAge::Person.new(first_name: 'Zed', last_name: 'Flintstone', gender: 'male')
zed.attributes
# {"id"=>nil, "first_name"=>"Zed", "last_name"=>"Flintstone", "given_name"=>"Flintstone", "gender"=>"male"}
zed.attributes.symbolize_keys
# {:id=>nil, :first_name=>"Zed", :last_name=>"Flintstone", :given_name=>"Flintstone", :gender=>"male"}
zed.persisted?
# false
zed.age_label
# 'Person'
zed.age_graph
# 'stone_age'
zed.age_type
# 'vertex'

With this basis let’s see if we can add to_s, to_h and to_age generically

module ApacheAge
  module Vertex

    def age_type = 'vertex'
    def age_label = self.class.name.split('::').last
    def age_graph = self.class.name.split('::').first.underscore

    def persisted? = @id.present?
    def to_h = attributes.symbolize_keys
    def age_properties = attributes.except('id').symbolize_keys

    def age_hash
      {
        id:,
        label: age_label,
        properties: age_properties
      }
    end

    def properties_to_s
      string_values =
        age_properties.each_with_object([]) do |(key,val), array|
          array << "#{key}: '#{val}'"
        end
      "{#{string_values.join(', ')}}"
    end

    def to_s
      ":#{age_label} #{properties_to_s}"
    end

    def age_node(age_alias = nil)
      age_alias ||=
        if @id.present?
          Digest::SHA256.hexdigest(@id.to_s).to_i(16).to_s(36).gsub(/[0-9]/,'')[0..9]
        end
      "(#{age_alias}:#{age_label} #{properties_to_s})"
    end
  end
end

module StoneAge
  class Person
    include ActiveModel::Model
    include ActiveModel::Attributes
    include ActiveModel::Validations
    include ApacheAge::Vertex

    attribute :id, :integer
    attribute :first_name, :string
    attribute :last_name, :string
    attribute :given_name, :string
    attribute :gender, :string

    def initialize(id: nil, first_name:, last_name:, given_name: nil, gender:)
      super # without this `@attributes` is nil and creates
      self.id = id
      self.given_name = given_name || last_name
      self.first_name = first_name
      self.last_name = last_name
      self.gender = gender
    end
  end
end

zed = StoneAge::Person.new(first_name: 'Zed', last_name: 'Flintstone', gender: 'male')
zed.age_hash
# {:id=>nil,
#  :label=>"Person",
#  :properties=>{:first_name=>"Zed", :last_name=>"Flintstone", :given_name=>"Flintstone", :gender=>"male"}}
zed.age_label
# 'Person'
zed.age_properties
# {:first_name=>"Zed", :last_name=>"Flintstone", :given_name=>"Flintstone", :gender=>"male"}
zed.age_node
#"(:Person {first_name: 'Zed', last_name: 'Flintstone', given_name: 'Flintstone', gender: 'male'})"
zed.age_node('zed')
#"(zed:Person {first_name: 'Zed', last_name: 'Flintstone', given_name: 'Flintstone', gender: 'male'})"

Finally let’s add create back in:

module ApacheAge
  module Vertex

    def age_type = 'vertex'
    def age_label = self.class.name.split('::').last
    def age_graph = self.class.name.split('::').first.underscore

    def persisted? = @id.present?
    def to_h = attributes.symbolize_keys
    def age_properties = attributes.except('id').symbolize_keys

    def age_hash
      {
        id: @id,
        label: age_label,
        properties: age_properties
      }
    end

    def properties_to_s
      string_values =
        age_properties.each_with_object([]) do |(key,val), array|
          array << "#{key}: '#{val}'"
        end
      "{#{string_values.join(', ')}}"
    end

    def to_s
      ":#{age_label} #{properties_to_s}"
    end

    def age_alias
      return nil if @id.blank?

      # we start the alias with a since we can't start with a number
      'a' + Digest::SHA256.hexdigest(@id.to_s).to_i(16).to_s(36)[0..9]
    end

    def age_node(alias_name = nil)
      alias_name = alias_name || age_alias
      "(#{age_alias}:#{age_label} #{properties_to_s})"
    end

    def create
      age_result = ActiveRecord::Base.connection.execute(create_sql)
      json_data = age_result.to_a.first[age_label.downcase].split("::#{age_type}").first
      json_data = age_result.to_a.first.values.first.split("::#{age_type}").first

      hash = JSON.parse(json_data)

      @id = hash['id']
      hash
    end

    def create_sql
      alias_name = age_alias || age_label.downcase
      <<-SQL
        SELECT *
        FROM cypher('#{age_graph}', $$
            CREATE (#{alias_name}#{self})
        RETURN #{alias_name}
        $$) as (#{age_label} agtype);
      SQL
    end
  end
end

module AgeSchema
  class Person
    include ActiveModel::Model
    include ActiveModel::Attributes
    include ActiveModel::Validations
    include ApacheAge::Vertex

    attribute :id, :integer
    attribute :first_name, :string
    attribute :last_name, :string
    attribute :given_name, :string
    attribute :gender, :string

    def initialize(id: nil, first_name:, last_name:, given_name: nil, gender:)
      super # without this `@attributes` is nil and creates
      self.id = id
      self.given_name = given_name || last_name
      self.first_name = first_name
      self.last_name = last_name
      self.gender = gender
    end
  end
end
zed = AgeSchema::Person.new(first_name: 'Zed', last_name: 'Flintstone', gender: 'male')
zed.persisted?
# false
zed.create
# {"id"=>844424930131980,
#  "label"=>"Person",
#  "properties"=>{"gender"=>"male", "last_name"=>"Flintstone", "first_name"=>"Zed", "given_name"=>"Flintstone"}}
zed.persisted?
# true
zed.age_hash
# {:id=>844424930131981,
#  :label=>"Person",
#  :properties=>{:first_name=>"Zed", :last_name=>"Flintstone", :given_name=>"Flintstone", :gender=>"male"}}

Now we can make a simple second Node Company

module AgeSchema
  class Company
    include ActiveModel::Model
    include ActiveModel::Attributes
    include ActiveModel::Validations
    include ApacheAge::Vertex

    attribute :id, :integer
    attribute :name, :string

    def initialize(id: nil, name:)
      super # without this `@attributes` is nil and creates
      self.id = id
      self.name = name
    end
  end
end
quarry = AgeSchema::Company.new(name: 'Bedrock Quarry')
quarry.create
# {"id"=>1125899906842625,
#  "label"=>"Company",
#  "properties"=>{"name"=>"Bedrock Quarry", "gender"=>"", "last_name"=>"", "first_name"=>"", "given_name"=>""}}

We probaly need to add find and find_by, but lets do that later. Let’s explore edges next.

Edges First Exploration

module ApacheAge
  module Vertex

    def age_type = 'vertex'
    def age_label = self.class.name.split('::').last
    def age_graph = self.class.name.split('::').first.underscore

    def persisted? = id.present?
    def to_h = attributes.symbolize_keys.to_hash
    def age_properties = attributes.except('id').symbolize_keys

    def age_hash
      {
        id: id,
        label: age_label,
        properties: age_properties
      }
    end

    def properties_to_s
      string_values =
        age_properties.each_with_object([]) do |(key,val), array|
          array << "#{key}: '#{val}'"
        end
      "{#{string_values.join(', ')}}"
    end

    def to_s
      ":#{age_label} #{properties_to_s}"
    end

    def age_alias
      return nil if id.blank?

      # we start the alias with a since we can't start with a number
      'a' + Digest::SHA256.hexdigest(id.to_s).to_i(16).to_s(36)[0..9]
    end

    def age_node(alias_name = nil)
      alias_name = alias_name || age_alias
      "(#{age_alias}:#{age_label} #{properties_to_s})"
    end

    def create
      return self if id.present?

      age_result = ActiveRecord::Base.connection.execute(create_sql)
      json_data = age_result.to_a.first[age_label.downcase].split("::#{age_type}").first
      json_data = age_result.to_a.first.values.first.split("::#{age_type}").first

      hash = JSON.parse(json_data)

      self.id = hash['id']
      # @id = hash['id']
      self
    end

    def create_sql
      alias_name = age_alias || age_label.downcase
      <<-SQL
        SELECT *
        FROM cypher('#{age_graph}', $$
            CREATE (#{alias_name}#{self})
        RETURN #{alias_name}
        $$) as (#{age_label} agtype);
      SQL
    end
  end
end

module AgeSchema
  class Person
    include ActiveModel::Model
    include ActiveModel::Attributes
    include ActiveModel::Validations
    include ApacheAge::Vertex

    attribute :id, :integer

    attribute :first_name, :string
    attribute :last_name, :string
    attribute :given_name, :string
    attribute :gender, :string

    def initialize(id: nil, first_name:, last_name:, given_name: nil, gender:)
      super # without this `@attributes` is nil and creates
      self.id = id
      self.given_name = given_name || last_name
      self.first_name = first_name
      self.last_name = last_name
      self.gender = gender
    end
  end
end


module AgeSchema
  class Company
    include ActiveModel::Model
    include ActiveModel::Attributes
    include ActiveModel::Validations
    include ApacheAge::Vertex

    attribute :id, :integer

    attribute :name, :string

    def initialize(id: nil, name:)
      super # without this `@attributes` is nil and creates
      self.id = id
      self.name = name
    end
  end
end

module ApacheAge
  module Edge
    def age_type = 'edge'
    def age_label = self.class.name.split('::').last
    def age_graph = self.class.name.split('::').first.underscore

    def persisted? = id.present?

    def to_h
      base_hash =  attributes.except('start_node', 'end_node')
      base_hash[:start_node] = start_node.to_h
      base_hash[:end_node] = end_node.to_h
      base_hash.symbolize_keys
    end

    def age_properties = attributes.except('id', 'start_node', 'end_node', 'start_id', 'end_id').symbolize_keys

    def age_hash
      {
        id: id,
        end_id: end_id,
        start_id: start_id,
        label: age_label,
        properties: age_properties
      }
    end

    def properties_to_s
      string_values =
        age_properties.each_with_object([]) do |(key,val), array|
          array << "#{key}: '#{val}'"
        end
      "{#{string_values.join(', ')}}"
    end

    def to_s
      ":#{age_label} #{properties_to_s}"
    end

    def age_alias
      return nil if id.blank?

      # we start the alias with a since we can't start with a number
      'a' + Digest::SHA256.hexdigest(id.to_s).to_i(16).to_s(36)[0..9]
    end

    def age_edge(alias_name = nil)
      alias_name = alias_name || age_alias
      "[#{age_alias}:#{age_label} #{properties_to_s}]"
    end

    def create
      return self if id.present?

      age_result = ActiveRecord::Base.connection.execute(create_sql)
      # json_data = age_result.to_a.first[age_label.downcase].split("::#{age_type}").first
      json_data = age_result.to_a.first.values.first.split("::#{age_type}").first

      hash = JSON.parse(json_data)

      self.id = hash['id']
      self.end_id = hash['end_id']
      self.start_id = hash['start_id']

      self
    end

    def create_sql
      self.start_node = start_node.create unless start_node.persisted?
      self.end_node = end_node.create unless end_node.persisted?
      <<-SQL
        SELECT *
        FROM cypher('#{age_graph}', $$
            MATCH (start_vertex:#{start_node.age_label}), (end_vertex:#{end_node.age_label})
            WHERE id(start_vertex) = #{start_node.id} and id(end_vertex) = #{end_node.id}
            CREATE (start_vertex)-[edge#{to_s}]->(end_vertex)
            RETURN edge
        $$) as (edge agtype);
      SQL
    end
  end
end

module AgeSchema
  class WorksAt
    include ActiveModel::Model
    include ActiveModel::Attributes
    include ActiveModel::Validations
    include ApacheAge::Edge

    attribute :role, :string

    attribute :id, :integer
    attribute :end_id, :integer
    attribute :start_id, :integer
    attribute :end_node #, :vertex
    attribute :start_node #, :vertex

    def initialize(id: nil, role:, start_node:, end_node:)
      super # without this `@attributes` is nil and creates
      self.id = id
      self.role = role
      self.end_id = end_node.id
      self.start_id = start_node.id
      self.start_node = start_node
      self.end_node = end_node
    end
  end
end

fred = AgeSchema::Person.new(first_name: 'Fred', last_name: 'Flintstone', gender: 'male')
quarry = AgeSchema::Company.new(name: 'Bedrock Quarry')
crane_ops = AgeSchema::WorksAt.new(role: 'Crane Operator', start_node: fred, end_node: quarry)
crane_ops.create_sql
crane_ops.create

Next, let’s work with the similarities in both Vertex and Node code. Let’s see if we can resolve that next.

Common Code Exploration

module ApacheAge
  module CommonEntity
    def age_label = self.class.name.split('::').last
    def age_graph = self.class.name.split('::').first.underscore
    def persisted? = id.present?
    def to_s = ":#{age_label} #{properties_to_s}"
    def base_to_h = attributes.to_hash
    def base_properties = attributes.except('id')

    def base_hash
      {
        id: id,
        label: age_label,
        properties: age_properties
      }.transform_keys(&:to_s)
    end

    def properties_to_s
      string_values =
        age_properties.each_with_object([]) do |(key,val), array|
          array << "#{key}: '#{val}'"
        end
      "{#{string_values.join(', ')}}"
    end

    def age_alias
      return nil if id.blank?

      # we start the alias with a since we can't start with a number
      'a' + Digest::SHA256.hexdigest(id.to_s).to_i(16).to_s(36)[0..9]
    end

    def execute_sql
      return self.age_hash if id.present?

      age_result = ActiveRecord::Base.connection.execute(create_sql)
      json_data = age_result.to_a.first.values.first.split("::#{age_type}").first

      JSON.parse(json_data)
    end
  end
end

module ApacheAge
  module Vertex
    include ApacheAge::CommonEntity

    def age_type = 'vertex'
    def to_h = base_to_h.symbolize_keys
    def age_properties = base_properties.symbolize_keys
    def age_hash = base_hash.with_indifferent_access

    def create
      return self if id.present?

      response_hash = execute_sql
      self.id = response_hash['id']

      self
    end

    def create_sql
      alias_name = age_alias || age_label.downcase
      <<-SQL
        SELECT *
        FROM cypher('#{age_graph}', $$
            CREATE (#{alias_name}#{self})
        RETURN #{alias_name}
        $$) as (#{age_label} agtype);
      SQL
    end
  end
end

module ApacheAge
  module Edge
    include ApacheAge::CommonEntity

    def age_type = 'edge'
    def age_hash = base_hash.merge(end_id:, start_id:)
    def age_properties = base_properties.except('start_node', 'end_node', 'start_id', 'end_id').symbolize_keys

    def to_h
      base_h = base_to_h.except('start_node', 'end_node')
      base_h['start_node'] = start_node.to_h
      base_h['end_node'] = end_node.to_h
      base_h.with_indifferent_access
    end

    def create
      return self if id.present?

      response_hash = execute_sql
      self.id = response_hash['id']
      self.end_id = response_hash['end_id']
      self.start_id = response_hash['start_id']

      self
    end

    def create_sql
      self.start_node = start_node.create unless start_node.persisted?
      self.end_node = end_node.create unless end_node.persisted?
      <<-SQL
        SELECT *
        FROM cypher('#{age_graph}', $$
            MATCH (start_vertex:#{start_node.age_label}), (end_vertex:#{end_node.age_label})
            WHERE id(start_vertex) = #{start_node.id} and id(end_vertex) = #{end_node.id}
            CREATE (start_vertex)-[edge#{to_s}]->(end_vertex)
            RETURN edge
        $$) as (edge agtype);
      SQL
    end
  end
end

module AgeSchema
  class Person
    include ActiveModel::Model
    include ActiveModel::Attributes
    include ActiveModel::Validations
    include ApacheAge::Vertex

    attribute :id, :integer

    attribute :first_name, :string
    attribute :last_name, :string
    attribute :given_name, :string
    attribute :gender, :string

    def initialize(id: nil, first_name:, last_name:, given_name: nil, gender:)
      super # without this `@attributes` is nil and creates
      self.id = id
      self.given_name = given_name || last_name
      self.first_name = first_name
      self.last_name = last_name
      self.gender = gender
    end
  end
end


module AgeSchema
  class Company
    include ActiveModel::Model
    include ActiveModel::Attributes
    include ActiveModel::Validations
    include ApacheAge::Vertex

    attribute :id, :integer

    attribute :name, :string

    def initialize(id: nil, name:)
      super # without this `@attributes` is nil and creates
      self.id = id
      self.name = name
    end
  end
end

module AgeSchema
  class WorksAt
    include ActiveModel::Model
    include ActiveModel::Attributes
    include ActiveModel::Validations
    include ApacheAge::Edge

    attribute :role, :string

    attribute :id, :integer
    attribute :end_id, :integer
    attribute :start_id, :integer
    attribute :end_node #, :vertex
    attribute :start_node #, :vertex

    def initialize(id: nil, role:, start_node:, end_node:)
      super # without this `@attributes` is nil and creates
      self.id = id
      self.role = role
      self.end_id = end_node.id
      self.start_id = start_node.id
      self.start_node = start_node
      self.end_node = end_node
    end
  end
end

fred = AgeSchema::Person.new(first_name: 'Fred', last_name: 'Flintstone', gender: 'male')
# fred.create
# fred.id

quarry = AgeSchema::Company.new(name: 'Bedrock Quarry')
# quarry.create
# quarry.id

works_at = AgeSchema::WorksAt.new(role: 'Crane Operator', start_node: fred, end_node: quarry)
works_at.create
works_at.id

fred.to_h
quarry.to_h
works_at.to_h

fred.age_hash
quarry.age_hash
works_at.age_hash

fred.age_properties
quarry.age_properties
works_at.age_properties

Unique Property Constraints

Things to consider.

  • Can we separate AGE properties from virtual attributes?
  • Can we support all the property data-types?, :string, :array, etc?

I think its easiest to create a rails schema and declare properties:

attribute :id, :integer
attribute :nick_name, :string
attribute :name, :string,
attribute :social_security_number, :integer
attribute :auth_roles, :array, default: ['user']

validates :name, required: true
validates :social_security_number, unique: true
validates :auth_roles, required: true

# class name is the label_name
# id is stored, but not a property
# nick_name is a virtual attribute
properties [:name, :social_security_number, :auth_roles]

Perhaps (how Neo4j works) - but that feels complicated:

property :nick_name, :string
property :name, :string, required: true
property :social_security_umber, :integer, unique: true
property :auth_roles, :array, required: true, default: ['user']

Rails Code

https://github.com/neo4jrb/activegraph/blob/8e2ba4d117f5702633b0aa7099c71923a100c40d/lib/active_graph/shared/property.rb

module ActiveGraph::Shared
  module Property
    extend ActiveSupport::Concern

    include ActiveGraph::Shared::MassAssignment
    include ActiveGraph::Shared::TypecastedAttributes
    include ActiveModel::Dirty
    ...
    module ClassMethods
      extend Forwardable

      def_delegators :declared_properties, :serialized_properties, :serialized_properties=, :serialize, :declared_property_defaults

      VALID_PROPERTY_OPTIONS = %w(type default index constraint serializer typecaster).map(&:to_sym)
      # Defines a property on the class
      #
      # See active_attr gem for allowed options, e.g which type
      # Notice, in ActiveGraph you don't have to declare properties before using them, see the ActiveGraph::Coree api.
      #
      # @example Without type
      #    class Person
      #      # declare a property which can have any value
      #      property :name
      #    end
      #
      # @example With type and a default value
      #    class Person
      #      # declare a property which can have any value
      #      property :score, type: Integer, default: 0
      #    end
      #
      # @example With an index
      #    class Person
      #      # declare a property which can have any value
      #      property :name, index: :exact
      #    end
      #
      # @example With a constraint
      #    class Person
      #      # declare a property which can have any value
      #      property :name, constraint: :unique
      #    end
      def property(name, options = {})
        invalid_option_keys = options.keys.map(&:to_sym) - VALID_PROPERTY_OPTIONS
        fail ArgumentError, "Invalid options for property `#{name}` on `#{self.name}`: #{invalid_option_keys.join(', ')}" if invalid_option_keys.any?
        build_property(name, options) do |prop|
          attribute(prop)
        end
      end
      ...
    end
  end
end

DB RESEARCH

https://stackoverflow.com/questions/75903997/how-can-i-declare-primary-key-in-apache-age

Only allow 1 SSN entry in SoftwareEngineer

Use this query:

CREATE OR REPLACE FUNCTION create_pk(properties agtype)
RETURNS agtype AS
$BODY$
SELECT agtype_access_operator($1, '"SocialSecurityNumber"');
$BODY$
LANGUAGE sql IMMUTABLE;

CREATE UNIQUE INDEX person_pk_idx ON staff_details."SoftwareEngineer"
(create_pk(properties));

Trigger A

n Apache AGE, you can achieve the uniqueness of the property SocialSecurityNumber using triggers. You will need to create the trigger function at first:

CREATE OR REPLACE FUNCTION check_unique_ssn()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM SoftwareEngineer
        WHERE SocialSecurityNumber = NEW.SocialSecurityNumber
    ) THEN
        RAISE EXCEPTION 'A SoftwareEngineer with the same SocialSecurityNumber already exists';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Then, you need to create the trigger and insert the values into it:

CREATE TRIGGER enforce_unique_ssn
BEFORE INSERT ON SoftwareEngineer
FOR EACH ROW
EXECUTE FUNCTION check_unique_ssn();

INSERT INTO SoftwareEngineer (name, SocialSecurityNumber, date)
VALUES ('Muneeb', '12345', NOW());

Using MERGE & COALESCE

SELECT * FROM cypher('staff_details', $$
    MERGE (e:SoftwareEngineer {
        social_security_umber: '12345'
    })
    SET e.name = 'Muneeb', e.date = COALESCE(e.date, timestamp())
    RETURN e
$$) as (e agtype);

SELECT * FROM staff_details."SoftwareEngineer";

MERGE documentation COALESCE documentation

Trigger B

There is a discussion on a similar issue on Github. Kindly check it out. From what I have tried and that works for your situation is:

Create vertex label (if it is not created already) before running the above CREATE INDEX query as

SELECT * FROM create_vlabel('staff_details', 'SoftwareEngineer');

Create Function “get_ssn” that will return the property SocialSecurityNumber from the “properties” column

CREATE OR REPLACE FUNCTION get_ssn(properties agtype)
RETURNS agtype
AS
$BODY$
select agtype_access_operator($1, '"SocialSecurityNumber"');
$BODY$
LANGUAGE sql
IMMUTABLE;
Create a unique index on the property "SocialSecurityNumber"

CREATE UNIQUE INDEX person_ssn_idx ON staff_details."SoftwareEngineer"(get_ssn(properties)) ;

Now when you try to add another node with the same SocialSecurityNumber, you get the error:

ERROR: duplicate key value violates unique constraint "person_ssn_idx" DETAIL: Key (get_ssn(properties))=("12345") already exists.

JoshInnis commented on Nov 29, 2021

A graph name is a schema and a label name is a table. Id and properties are columns in vertex table. Id, start_id, end_id, and properties are columns in the edge tables. Use the agtype_access_operator(properties, key) to get to get a property value.

Knowing all that you can use Postges’ standard DDL language to implement constraints, indices and unique values.

ALTER TABLE graph_name.label_name
ADD CONSTRAINT constraint_name
CHECK(agtype_access_operator(properties, "name_of_property") != '"Check against here"'::agtype);

pdpotter commented on Nov 30, 2021 Since indices require an immutable function, an additional function will still need to be created for them. When I create a get_id function with

CREATE OR REPLACE FUNCTION get_id(properties agtype)
  RETURNS agtype
AS
$BODY$
    select agtype_access_operator($1, '"id"');
$BODY$
LANGUAGE sql
IMMUTABLE;

and use it in an index with

CREATE UNIQUE INDEX person_id_idx ON mygraph.person(get_id(properties));

the creation of vertices with the same id will be prevented

ERROR: duplicate key value violates unique constraint "person_id_idx" DETAIL: Key (get_id(properties))=(2250) already exists. but the index will still not be used when trying to match vertices with a specific id:

SELECT * FROM ag_catalog.cypher('mygraph', $$EXPLAIN ANALYZE MATCH (a:person {id:2250}) return a$$) as (a agtype);

Is there a way to use indices when matching?

JoshInnis commented on Nov 30, 2021 Indices cannot currently be used while matching. There will need to be some re factoring done to allow the planner to realize opportunities where the indices can be used.

Constraints: You can create a uniqueness constraint on the SocialSecurityNumber property of the SoftwareEngineer label. This will enforce uniqueness and prevent duplicate nodes with the same SocialSecurityNumber from being created. Here’s an example of how to create a uniqueness constraint using Cypher:

CREATE CONSTRAINT ON (se:SoftwareEngineer) ASSERT se.SocialSecurityNumber IS UNIQUE;

Triggers: You can also use triggers to enforce the uniqueness constraint at the database level. A trigger can be created to check if a SoftwareEngineer node with the same SocialSecurityNumber already exists before inserting a new node. Here’s an example of how to create a trigger using Cypher:

CREATE TRIGGER check_unique_social_security_number
BEFORE INSERT ON SoftwareEngineer
FOR EACH ROW
BEGIN
    IF EXISTS (
        MATCH (se:SoftwareEngineer {SocialSecurityNumber: NEW.SocialSecurityNumber})
        RETURN se
    )
    THEN
        RAISE EXCEPTION 'A SoftwareEngineer with the same SocialSecurityNumber already exists.';
    END IF;
END;

With these constraints and triggers in place, if you try to insert a SoftwareEngineer node with a duplicate SocialSecurityNumber, it will result in an exception being raised, preventing the creation of the duplicate node.

Note: The examples provided assume that you have already created the SoftwareEngineer label and relevant properties in your graph schema. Adjust the Cypher statements accordingly based on your schema design.

Migration Exploration

  • Create a Node/Edge Label (‘Table’)

staff_details - Graph Name SoftwareEngineer - Label Name SocialSecurityNumber - Unique Property Name

SELECT * FROM create_vlabel('staff_details', 'SoftwareEngineer');

  • Create Unique Fields

CREATE CONSTRAINT ON (se:SoftwareEngineer) ASSERT se.SocialSecurityNumber IS UNIQUE;

  • Create Required Fields

Can this be done on a DB Level (or just within Rails)?

Relationship Explorations

Can we Automate queries for relationships between nodes? maybe something like:

# OUTGOING (within Person class)
one_link :outgoing :company, via: works_at
one_link :outgoing :employeer, node: Company, via: works_for, edge: WorksAt

many_links :outgoing :companies, via: works_at
many_links :outgoing :firms, node: Company, via: works_for, edge: WorksAt

# INCOMING (within Company class)
many_links :incoming :people, via: works_at
many_links :incoming :employees, node: Person, via: works_for, edge: WorksAt

Create code

From ‘Neo4j/ActiveGraph’

https://github.com/neo4jrb/activegraph/blob/8e2ba4d117f5702633b0aa7099c71923a100c40d/lib/active_graph/node/has_n.rb#L409

module ActiveGraph::Node
  module HasN

    extend ActiveSupport::Concern
    ...
    module ClassMethod
      ...
      def has_many(direction, name, options = {})
        name = name.to_sym
        build_association(:has_many, direction, name, options)

        define_has_many_methods(name, options)
      end

      def has_one(direction, name, options = {})
        name = name.to_sym
        build_association(:has_one, direction, name, options)

        define_has_one_methods(name, options)
      end

      private
      ...
    end
  end
end

Resources

Graph App Example Resources

Graph DB Design

Apache AGE SQL / Migration / Indexes

Rails with Apache AGE

AGE Management

AGE SQL

INTRESTSING UNTESTED RESOURCES

AGE Resources

Other Graph Resources

Bill Tihen
Bill Tihen
Developer, Data Enthusiast, Educator and Nature’s Friend

very curious – known to explore knownledge and nature