DEV Community: Ken Woon

Introducing AgeSQL: Seamlessly Bridging the Gap Between SQL and Cypher in PostgreSQL

Ken Woon — Mon, 17 Jul 2023 22:35:21 +0000

Introduction

Are you looking for a powerful tool that combines the expressive capabilities of Cypher queries with the familiarity of SQL syntax? Meet AgeSQL, a command-line interface (CLI) client for PostgreSQL that extends its capabilities to support both traditional SQL and Cypher queries without the hassle of wrapping Cypher queries. AgeSQL leverages the AGE extension for PostgreSQL, which enables graph queries within the database, making it a versatile tool for working with graph data.

In this blog post, I'll share the details of my current project, AgeSQL, its key features, and how you can install and use it to harness the power of both SQL and Cypher in your PostgreSQL database.

Understanding the Need for AgeSQL

Working with graph databases can be both exciting and challenging. Traditional SQL is powerful for certain types of data analysis and manipulation, but when it comes to traversing and querying connected graph data, Cypher, a language specifically designed for graph databases, shines. AgeSQL was born out of the need to have a unified tool that brings together the strengths of SQL and Cypher, making it easier for users to interact with graph databases using a familiar interface.

What sets this apart from the current version of Apache AGE is that AgeSQL does not require Cypher queries to be wrapped in an SQL function clause, and can be directly written in Cypher syntax. The backend takes the raw user inputs and convert them into a wrapped clause that is compatible in AGE, which will then be sent to the server.

Addressing the Challenges

Building a CLI tool like AgeSQL comes with its share of challenges. Let's briefly touch on how we've addressed some of these challenges to make AgeSQL a robust and user-friendly tool:

Determining the graph to use: AgeSQL has a mechanism to identify the graph to be queried, allowing it to handle different graphs based on user input.
Determining the number of output parameters: AgeSQL intelligently determines the number of parameters to expect in the final result, which is crucial for processing and presenting query results accurately.
Determining the output type: AgeSQL identifies the appropriate output type for each parameter in the result, taking into account different data types supported by PostgreSQL, such as pg_float8 or agtype.

By addressing these challenges, AgeSQL provides a seamless and efficient experience for executing SQL and Cypher queries within the PostgreSQL database.

Key Features of AgeSQL

AgeSQL offers a variety of features that make it a powerful tool for working with graph data:

Seamless integration of Cypher queries into PostgreSQL: AgeSQL supports standard SQL queries in addition to Cypher queries. It internally converts Cypher queries into PostgreSQL functions, eliminating the need for manual function calls.
Execution of Cypher queries: With AgeSQL, you can execute Cypher queries directly within the PostgreSQL database. This allows you to perform graph traversal, pattern matching, and graph analysis operations.
Integration with traditional SQL: AgeSQL seamlessly wraps Cypher commands in SQL syntax, allowing you to execute them within PostgreSQL.
Support for graph-specific operations: AgeSQL supports various graph-specific operations, including traversing nodes and edges, retrieving properties, and performing graph analytics.
Familiar psql-like interface: AgeSQL provides an interface similar to PostgreSQL's psql, making it easy for users to interact with the database.

Installing and Using AgeSQL

To get started with AgeSQL, follow these steps:

Ensure you have PostgreSQL 15 installed with the AGE extension enabled.
Clone the PostgreSQL repository from GitHub and compile the source code.
Install AGE 12.1.0, a prerequisite for AgeSQL.
Clone the AgeSQL repository from GitHub and compile the source code.
Start the AgeSQL command-line interface.

The detailed step-by-step instructions could be found in the README section of the GitHub page. Once you're inside the AgeSQL CLI, you can run both SQL and Cypher queries effortlessly. For SQL queries, simply type them as you would in psql. For Cypher queries, remember to set the graph path first using the CREATE GRAPH command or the SET GRAPH_PATH command. AgeSQL seamlessly converts Cypher queries to PostgreSQL functions, so you can execute them without calling functions explicitly.

Examples

Creating graphs:

CREATE GRAPH graph_name1;

Creating vertices and relationships:

CREATE (a:label)-[e:RELTYPE]->(b:label);

Returning values:

RETURN a AS rslt ORDER BY b ASC SKIP 1 LIMIT 5;

Matching data:

MATCH (n:label), (m:label) WHERE property(n) <> property(m)
OPTIONAL MATCH (n)-[r]->(p), (m)-[s]->(q);

Conclusion

AgeSQL is a powerful CLI tool that bridges the gap between SQL and Cypher in PostgreSQL, allowing you to work seamlessly with graph databases using familiar SQL syntax while benefiting from the expressive power of Cypher without having the need to wrap it in an SQL clause. With its seamless integration and support for both SQL and Cypher queries, AgeSQL empowers developers and data analysts to tackle complex graph data with ease.

So, if you're working with graph databases and want a versatile tool that brings together the best of SQL and Cypher, give AgeSQL a try! Get started with AgeSQL today and unlock the potential of your PostgreSQL database for graph-based operations. Happy graph querying!

How I Learned Lex and Yacc with Apache AGE Functions

Ken Woon — Wed, 26 Apr 2023 23:32:31 +0000

Introduction

As a developer for the open source Apache AGE project, I have been learning to program using Lex and Yacc in order to create functionalities for the extension. Lex and Yacc are powerful tools that can help us generate parsers and scanners for our programming language. However, these tools can be difficult to learn and use without some guidance.

In the AGE Command Line Interface (CLI) project, we aim to simplify querying in Apache AGE by allowing users to code in Cypher without wrapping it inside a SQL query. In order to do this, we are using Lex and Yacc programs to convert Cypher clauses into functions recognizable by the psql CLI.

In this blog post, we'll take a closer look at how I used Apache Age functions, specifically the MATCH function to learn Lex and Yacc coding and start building my own program.

Language Processing

In order to understand Lex/Yacc, let's first talk about Language Processing as a whole. Language processing is a crucial component in the world of software development, and it involves several stages to transform source code into executable programs. The process typically involves four key stages: lexical analysis, syntax analysis, semantic analysis and code generation, and target code generation.

Lexical analysis involves breaking down the source code into a series of tokens. These tokens are then analyzed to identify their respective classes, such as identifiers, keywords, and operators. This stage is performed by a tool called a lexer or scanner, which uses a set of rules specified in a formal language like Lex to identify and extract the tokens.

Syntax analysis is the next step, which involves parsing the token stream to determine whether the code conforms to the grammar of the programming language. This stage is carried out by a parser, which uses a formal language like Yacc to generate a parse tree, representing the syntactic structure of the program.

Semantic analysis and code generation is the stage where the parse tree is analyzed to determine the meaning of the program and generate corresponding machine code. This stage involves a series of transformations on the parse tree, including type checking, scope resolution, and code generation.

Target code generation is the next stage, which involves generating machine code that can be executed on a particular hardware platform. This stage is typically carried out by a code generator, which translates the machine-independent code into machine-specific instructions.

We will be focusing on the first two steps, which are managed by writing Lex and Yacc files.

Compilation and Execution

In this blog post, I will be using Flex and Bison instead, which are basically just improved versions of Lex and Yacc. The bison and flex commands in the examples below can interchangeable with yacc and lex.

Before compiling, make sure both {filename}.y and {filename}.l exists in the same directory, with {filename} to be whatever name you desire. First run the command below in a terminal window.

bison -d {filename}.y

This creates a {filename}.tab.c parser file and the -d option generates the {filename}.tab.h header file, which may be referenced in the Lex file. Then, run the command below to compile the Lex file.

flex match.l

This generates a file called lex.yy.c. With the two generated C files, run the following command to compile them into an executable C program.

gcc lex.yy.c {filename}.tab.c -o {filename}

This generates an executable C with a filename you have specified after the -o option, which tells the compiler to output the file into the specified filename.

./{filename}

Run the command above to execute your newly created program and you have successfully built and executed your program!

For example, a complete compilation and execution for the file 'match.l' and 'match.y' will look like:

yacc -d match.y
flex match.l
gcc lex.yy.c match.tab.c -o match
./match

If any case that an error occurs, there are likely issues with either the Lex or Yacc file, which will be specified by the error message, along with the problematic line and character.

Lex Structure

The structure of Lex files will look something like the following:

%{
DECLARATIONS
%}

%%
PATTERNS    ACTIONS;
%%

FUNCTIONS

The first part is enclosed in %{ ... %} and includes C declarations such as #include.

The section enclosed with %% ... %% represents the pattern-action pairs that allows the parser to recognize specific tokens that are inputted by the user, and make an action or multiple actions based on the input.

The last section contains C functions that support the language processing. These functions may be called by the actions from the previous section.

Yacc Structure

The structure of Yacc files is similar to that of Lex files and will look something like the following:

%{
DECLARATIONS
%}

DEFINITIONS

%%
GRAMMARS : RULES;    {ACTIONS}
%%

FUNCTIONS

The first part is enclosed in %{ ... %} and includes C declarations such as #include.

The next section is for yacc definitions that begins with the % symbol, which consists of the %start, %token, %union, and %types definitions.

The section enclosed with %% ... %% represents grammars that are a set of productions. The left-hand side which are grammars is followed by :, and a ; to finish the set of rules or | to add more rules, and a right-hand side with actions that are enclosed in { ... }.

The last section contains C functions that support the language processing. These functions may be called by the actions from the previous section.

Working with Lex

In this example, we will try to replicate the 'MATCH' Cypher command commonly used in AGE.

In the first part, we will include the 'match.tab.h' header that will be generated after compiling the 'match.y' file. We will also create a variable yyerror with type void which the parser will call whenever a syntactical error occurs.

%{
#include "match.tab.h"

void yyerror(char* s);
%}

The next section is defined with patterns and their actions. The first pattern [ \t\n] defines whitespaces, tab spaces, and new lines, and tells the compiler to ignore these input. The MATCH and RETURN tokens are intuitive and simply returns MATCH and RETURN to the compiler. The following symbols are instead returned as key words that will be used in the Yacc file. The exit token will return the exit_command, and any string of characters starting with a alphabetic character followed by any alphabetic or numeric character will be recognized as a VAR. Anything else that are inputted by the user will cause a syntactical error.

%%
[ \t\n]                 ;
"MATCH"                 { return MATCH; }
"RETURN"                { return RETURN; }
"-"                     { return DASH; }
"("                     { return LP; }
")"                     { return RP; }
"["                     { return LB; }
"]"                     { return RB; }
"{"                     { return LC; }
"}"                     { return RC; }
"<"                     { return LT; }
">"                     { return GT; }
";"                     { return yytext[0]; }
"exit"                  { return exit_command; }
[a-zA-Z][a-zA-Z0-9]*    { return VAR; }
.                       { ECHO; yyerror("unexpected character"); }
%%

There is a function in the final part of the Lex file called yywrap(), which is required if multiple input files will be used.

int yywrap(void) {
    return 1;
}

Working with Yacc

In the first part, we will include the standard io and lib headers, as well as the 'match.tab.h' header that will be generated after compiling this file. We will also create a variable yyerror with type void which the parser will call whenever a syntactical error occurs.

%{
#include <stdio.h>
#include <stdlib.h>
#include "match.tab.h"

void yyerror(char* s);
%}

We will then have some Yacc definitions. %union will allow us to specify the different types that the analyzer can return, which are enclosed in { ... }. %start indicates the default production grammar to look for at the start of the program. %token describes the expected tokens that the analyzer will be receiving as the specified type. The <str> after %token stores the tokens into the union member 'str'.

%union { char *str; }
%start query
%token <str> MATCH RETURN DASH LP RP LB RB LC RC LT GT VAR exit_command

There will be 4 production grammars in total, with the query grammar being the default. This grammar will consist of the statement grammar, or the exit_command, which will exit the program. The statement grammar can either be the match_clause, the return_clause, or both together. The match_clause can have grammars that look like one from the list:

MATCH (u)
MATCH (u)-[r]-(v)
MATCH (u)<-[r]-(v)
MATCH (u)-[r]->(v)

The return_clause will only allow us to return one variable as of now, which will be fixed in the future.

%%
query : statement ';'                                                   { ; }
      | exit_command ';'                                                { printf("Exiting program...\n"); exit(EXIT_SUCCESS); }
      ;

statement : match_clause                                                { ; }
          | return_clause                                               { ; }
          | match_clause return_clause                                  { ; }

match_clause : MATCH LP VAR RP                                          { ; }
             | MATCH LP VAR RP DASH LB VAR RB DASH LP VAR RP            { ; }
             | MATCH LP VAR RP LT DASH LB VAR RB DASH LP VAR RP         { ; }
             | MATCH LP VAR RP DASH LB VAR RB DASH GT LP VAR RP         { ; }
             ;

return_clause : RETURN VAR                                              { ; }
              ;
%%

There are two functions in the final part of the Yacc file, which are the main() and yyerror() functions. The main() function simply returns yyparse(), which is a Yacc function that iterates through the grammar to continuously read the input and produce the corresponding action until manually exited or an error is encountered. The yyerror() is called whenever a syntax error occurs, which simply prints out the error.

int main(void) {
    return yyparse();
}

void yyerror(char *s) {
    fprintf(stderr, "%s\n", s);
}

Program Demo

After compiling and executing the program, you can try typing several different inputs to test if the program is working correctly.

Trying any of these from the list with interchangeable variable name will pass as successfully parsed.

MATCH (u);
MATCH (u)-[r]-(v);
MATCH (u)<-[r]-(v);
MATCH (u)-[r]->(v);
RETURN u;
MATCH (u) RETURN u;
MATCH (u)-[r]-(v) RETURN u;
MATCH (u)<-[r]-(v) RETURN u;
MATCH (u)-[r]->(v) RETURN u;
exit;

If you try any other combinations of recognized and unrecognized symbols, the program will spit an error message and exit the program.

As of now, there are no actions set to the production grammars, which will not show the users any output through successful parses, but will be updated in the future to make the program more functional.

Conclusion

Learning Lex and Yacc programming using Apache Age functions can be an exciting and rewarding journey for any programmer interested in language processing and compiler development. With the help of Apache Age functions, I have learnt how to create my own program and build a compiler that can transform source code into executable programs.

By mastering the various stages of language processing, including lexical analysis, syntax analysis, semantic analysis, code generation, and code improvement, you can gain a deep understanding of how programming languages work and develop skills that are highly sought after in the software industry.

I have used this tutorial on Lex and this tutorial on Flex to learn the basics of Lex and Flex programming.

The Basics of Querying with Cypher in PostgreSQL using Apache Age

Ken Woon — Sat, 11 Mar 2023 16:55:02 +0000

Introduction

Welcome to the world of graph databases! When it comes to modelling complex and highly connected data, graph databases have proven to be an efficient and intuitive solution. And one of the most popular graph databases out there is Neo4j, which uses a query language called Cypher.

But what if you could use Cypher to query data in PostgreSQL? Well, now you can! Thanks to the Apache Age extension, you can use Cypher to query a graph that is stored in a PostgreSQL database. This powerful combination allows you to take advantage of the benefits of graph databases while leveraging the maturity and stability of PostgreSQL.

In this blog post, I will walk you through the basics of querying with Cypher in PostgreSQL using the Apache Age extension. Whether you're new to graph databases or an experienced user, this post will help you get started on querying in Apache Age. So let's get started! If you have any doubts setting up your environment, check out my previous post about the installation process for both PostgreSQL and Apache AGE.

Cypher Syntax

The syntax for Cypher is designed to be easy to read and write, making it accessible to both developers and non-technical users. The language is built around the concept of pattern matching, which allows you to describe complex relationships between nodes and edges in your graph data.

In Cypher, nodes are represented by parenthesis (), while labels or tags are indicated by a colon : followed by the label name, which groups nodes by roles or types. For example, a node representing a person who is male could be labelled as:

(:Person:Male)

Nodes can also have properties, which are enclosed in curly braces {} and follow the label, such as:

(:Person {name: 'Jake'})

Relationships, on the other hand, are indicated by hyphens - or square brackets [], and connect two nodes together. The direction of the relationship is specified using < and > to indicate the direction of the arrow. For example, if Jake likes Jane, we can represent this with:

(:Person {name: 'Jake'})-[:LIKES]->(:Person {name: 'Jane'})

If the colon is dropped -[LIKES]->, this will instead represent a variable (alias) instead of a relationship type and all types of relationships will be searched.

Similar to nodes, relationships can also have properties, which are enclosed in curly braces {} and follow the relationship type, such as:

-[:LIKES {type: 'as a friend'}]->

Finally, it's worth noting that aliases can be used to refer to nodes and relationships throughout your queries. To use an alias, simply name the node or relationship before the label, such as the a from (a:Person), the r from [r:LIKES] and the b from (b:Person). These can be referred to later in your query using the aliases you've defined, like so:

(a)-[r:LIKES {type: 'as a friend'}]->(b)

Basic Queries

In Apache AGE, Cypher cannot be used in an expression and the query must exist in the SQL FROM clause of a query. For example, to execute a Cypher query, the typical layout would be as follows:

SELECT *
FROM cypher('graph_name', $$
    /* Cypher query here, for example:
    MATCH (a:Person)
    RETURN a
    */
$$) AS (person agtype);

Cypher queries are written in between the dollar signs $$ $$ of the cypher() command, which requires a graph name in the beginning to specify which graph is being worked on. Aliases have to be specified for the RETURN outputs from the Cypher query. The syntax can be generalized as cypher(graph_name, query_string, parameters). AGE uses a custom data type called agtype, which is the only data type returned by AGE.

In order to start working with graphs in Apache Age using Cypher, the first step is to create a graph. This can be done using the create_graph function, which takes the name of the graph as its argument:

SELECT create_graph('graph_name');

To delete a graph, use drop_graph, which also takes the name of the graph as an argument:

SELECT drop_graph('graph_name', true);

Once you have a graph to work with, you can start creating nodes or vertices using the CREATE clause. To create a simple node, use CREATE (n). To create a node with a label, use CREATE (:Person). You can also add properties to nodes using the curly brace syntax, such as CREATE (:Person {name: Jack}), as we saw in the previous section. Here is an example of a complete query:

SELECT *
FROM cypher('People', $$
    CREATE (:Person {name: Jack}),
        (:Person {name: Jane})
$$) AS (person agtype);

The MATCH clause is used to specify patterns that Cypher will search for in the database. To get all vertices in the graph, use MATCH (n). To get all vertices with a specific label, use MATCH (n:Person). To get related vertices, use MATCH (:Person {name: 'Jack'})-[]-(:Person), where the symbol -[]- means related to, without regard to type or direction of the relationship. You can also match on specific edge types using a variable with MATCH (:Person {name: 'Jack'})-[:LIKES]->(:Person {name: 'Jane'}). A full query will look something like:

SELECT *
FROM cypher('People', $$
    MATCH (a:Person {name: 'Jack'})-[r:LIKES {type: 'as a friend'}]->(b:Person {name: 'Jane'})
    RETURN a.name, r.type, b.name
$$) AS (he agtype, likes agtype, her agtype);

To delete graph elements, the DELETE clause can be used. To delete a vertex, use DELETE n. To delete all vertices and edges in the graph, use:

SELECT *
FROM cypher('People', $$
    MATCH (n:People)
    DETACH DELETE n
$$) as (n agtype);

To delete edges only, use:

SELECT *
FROM cypher('People', $$
    MATCH (:People {name: 'Jack'})-[r:LIKES]->()
    DELETE r 
$$) AS (n agtype);

The SET clause is used to update labels on nodes and properties on vertices and edges. To set a property, use SET n.age = 25:

SELECT *
FROM cypher('People', $$
    MATCH (a:Person {name: 'Jack'})
    SET a.age = 25
$$) AS (n agtype);

The REMOVE clause can be used to remove properties from vertices and edges, such as REMOVE n.age:

SELECT *
FROM cypher('People', $$
    MATCH (a:Person {name: 'Jack'})
    REMOVE a.age
$$) AS (n agtype);

Finally, the RETURN clause allows you to specify which parts of the pattern you're interested in. This could be nodes, relationships, or properties on either of these. When you want to return all vertices, edges, and paths found in a query, you can use the * symbol:

SELECT *
FROM cypher('People', $$
    MATCH (a: Person {name: 'Jack'})-[r]->(b)
    RETURN *
$$) AS (person1 agtype, relationship agtype, person2 agtype);

In this section, we learned how to perform basic Cypher queries in Apache Age. We covered the creation and deletion of graphs, as well as the creation, deletion, and modification of nodes and relationships. With this knowledge, we can start building more complex queries to extract insights and knowledge from our data. To find out more about each clauses and other unmentioned clauses, as well as functions available in Apache AGE, check out the official documentation available online.

Conclusion

By learning these fundamental concepts and commands, you can start to leverage the power of graph databases and extract valuable insights and knowledge from your data. With Cypher and Apache Age, you can model your data as a graph, and query it using a familiar, intuitive syntax. Whether you're working on social networks, recommendation engines, fraud detection, or any other problem that can be modelled as a graph, Apache Age can help you store, manage, and query your data efficiently and effectively.

I hope this blog post has been helpful in getting you started with Cypher queries in Apache Age. Remember, this is just the beginning as there are many more advanced features and techniques to explore as you become more proficient in using this powerful tool. So keep learning, keep practicing, and keep discovering new insights with Cypher and Apache Age.

This post is written based on the Neo4j Getting Started Guide and the Apache AGE Master Documentation

How to Install PostgreSQL and Apache AGE on MacOS

Ken Woon — Thu, 23 Feb 2023 22:52:16 +0000

Introduction

PostgreSQL is one of the world's most popular open-source relational database management systems. Its high scalability, reliability, and robustness have made it a top choice for enterprise-level applications. Apache AGE is an extension of PostgreSQL that brings graph database features to the table. While they are available on various operating systems, getting started on a Mac can be challenging for some. In this blog post, I will provide you with a step-by-step guide on how to download and compile PostgreSQL and Apache AGE on MacOS, so you can start using this powerful database system for your projects. Whether you're a beginner or an experienced developer, this guide will help you get started with PostgreSQL and AGE on your Mac. So, let's dive in!

Pre-Installation Procedure

1. Before following the process, make sure Xcode is installed, which can be downloaded for free from the Mac App Store. This provides the necessary development tools and libraries required for building PostgreSQL.

2. After installing Xcode, install Homebrew, a package manager that allows easy installation and management of software packages, by running the following command on Terminal:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

3. Install the GCC library which is necessary for compiling PostgreSQL:

brew install gcc

4. Install Git, a version control system that developers use to manage source code changes. You will need this to pull the source codes from GitHub. Run the command on Terminal:

git --version

It should prompt you to install it if Git is not already installed.

By completing these pre-installation procedures, you'll have everything you need to install and compile PostgreSQL on your Mac. Let's move on to the next steps.

Installing PostgreSQL

In this section, I will guide you through the installation process of PostgreSQL on your MacOS device, step-by-step. From cloning the PostgreSQL repository from GitHub to starting the database server and writing PostgreSQL commands. The following commands are all used inside the Terminal.

1. Change the directory to where you want to install Postgres. I will use my desktop for this tutorial.

cd ~/Desktop

2. Create a directory and name it whatever you like.

mkdir PostgreSQL

3. Head to the newly created directory.

cd PostgreSQL

4. Clone the PostgreSQL repository from GitHub.

git clone https://git.postgresql.org/git/postgresql.git

5. After it is finished, go to the postgresql folder inside your current directory.

cd postgresql

Note
If you would like to use a different version of PostgreSQL (for instance if you want to use Apache AGE that currently only supports versions 11 and 12), follow these steps. If you are fine using the latest version, skip to step 8.

6. git branch -a will show a list of branches for the PostgreSQL repo. The format should look like remotes/origin/{release version}. Copy the {release version} part of your desired version. I will be using the REL_12_STABLE branch for this tutorial.

7. Navigate to the desired branch.

git checkout REL_12_STABLE

8. Run the configure query from the folder. The /usr/local/pgsql can be replaced with any directory of your choice. Later when compiling your code, it will be directed to install your binary files in that specific directory.

./configure --prefix=/usr/local/pgsql --enable-cassert --enable-debug CFLAGS="-glldb -ggdb -O0 -g3 -fno-omit-frame-pointer"

The options and flags --enable-cassert --enable-debug CFLAGS="-glldb -ggdb -Og -g3 -fno-omit-frame-pointer are required if you need to use debugging tools for development.

This command will generate all the make files for you. If there are any messages saying that a certain library is missing, go ahead and install them using Homebrew, replacing {library name} with the name of the library required: brew install {library name}

9. Run the following command to compile the PostgreSQL. -j12 is an option to compile the code in parallel using multiple cores, in this case, 12 cores. You can change the number to however many cores you have/like.

make -j12

This process will take a while.

10. Make a directory at the same location specified during the ./configure command. For this example, it would be /usr/local/pgsql.

sudo mkdir /usr/local/pgsql

The sudo grants administrative privileges. It should prompt you with a password. Go ahead and enter your computer admin user password and hit enter.

11. Change the owner of the directory to give permission for the current user to configure the directory. Make sure to change {user name} to your own.

sudo chown {user name} /usr/local/pgsql

12. The files are now ready to be installed using:

make install

13. The next step will be to set the path. Again, the path will depend on the directory you have set previously. Locate the /bin/ folder in your specified path.

export PATH=/usr/local/pgsql/bin/:$PATH

14. To specify where you want to create your cluster, use the following command, using any directory you like:

export PGDATA=/usr/local/pgsql/bin/data

15. Initialize your cluster:

initdb

16. To start the database server and provide the log file:

pg_ctl start -l log

17. Run the following to complete the installation and start writing in PostgreSQL:

psql postgres

If you would like to get out of writing PostgreSQL commands, type \q. Stop the PostgreSQL server using:
pg_ctl stop -l log

Congratulations! You have successfully installed PostgreSQL on your MacOS device. With PostgreSQL installed, you can now start building robust and scalable databases for your applications.

Installing Apache AGE

Now it is time to move on to Apache AGE. Installing it might seem a bit intimidating, but fear not, as I will guide you through it. In this section, I will walk you through the installation procedure for Apache AGE on your MacOS.

1. Change the directory to where you want to install AGE. I will use my desktop for this tutorial.

cd ~/Desktop

2. Create a directory and name it whatever you like.

mkdir AGE

3. Head to the newly created directory.

cd AGE

4. Clone the AGE repository from GitHub.

git clone https://github.com/apache/age

5. After it is finished, go to the age folder inside your current directory.

cd age

6. Navigate to the AGE_PG12.1.0_ALPHA branch which is compatible for PostgreSQL version 12. This step can be skipped if you have PostgreSQL version 11 installed.

git checkout AGE_PG12.1.0_ALPHA

7. In order to compile and install AGE in the specified version of your Postgres, you will need to pass pg_config inside the bin folder of your installed PostgreSQL directory to the make command.

sudo make PG_CONFIG=/usr/local/pgsql/bin/pg_config install

It should prompt you with a password. Go ahead and enter your computer admin user password and hit enter. Run installcheck if you would like to run regression tests. There will be an error but it can be ignored.

make PG_CONFIG=/usr/local/pgsql/bin/pg_config installcheck

8. Go back to your installed PostgreSQL directory.

cd /usr/local/pgsql

9. Initialize a database with a name of your choice. I will name this database test.

bin/initdb test

10. Before starting the database, there are some options that have to be configured. Use the text editor in Terminal to edit the postgresql.conf file with the command:

vim test/postgresql.conf

The PostgreSQL configuration file should appear.

11. While in the configuration file, type /port and hit enter. It should bring you to the line that says #port = 5432. Press the 'i' key on your keyboard to enter INPUT mode, then delete the #. Change the port number if you would like to (you might have to change it if there is another instance of server running on the same port). Take note of this port number for later use. Hit 'ESC' to exit INPUT mode.

12. While in the configuration file, type /preload and hit enter. It should bring you to the line that says #shared_preload_libraries = ''. Press the 'i' key on your keyboard to enter INPUT mode, then add in the word age between the apostrophes. It should look like #shared_preload_libraries = 'age'. Hit 'ESC' to exit INPUT mode.

13. While in the configuration file, type /search and hit enter. It should bring you to the line that says #search_path = '"$user", public'. Press the 'i' key on your keyboard to enter INPUT mode, then add in the phrase ag_catalog, before the "$user". It should look like #search_path = 'ag_catalog, "$user", public'. Hit 'ESC' to exit INPUT mode.

14. While in the configuration file, type :wq to save and exit the document.

15. Now, start the database server using:

bin/pg_ctl -D test -l logfile start

16. Create the database with the same port number from the configuration file.

bin/createdb --port=5432 test

17. Open a command line to the database.

bin/psql --port=5432 test

You should now have an interface with the database that allows you to enter SQL commands.

18. Create the AGE extension and load it during the first time you set up the database.

CREATE EXTENSION age;
SET search_path TO ag_catalog;
LOAD 'age';

Age is now installed into PostgreSQL. You are now able to enter Cypher queries into SQL commands.

Exit the interface with \q and shut the server down with bin/pg_ctl -D test -l logfile stop.

Congratulations! Now that you have successfully installed Apache AGE, you can start using it to work with your PostgreSQL database. AGE brings in a whole new level of flexibility and performance to your database queries, making it an indispensable tool for big data analytics and real-time data processing.

Next Steps

Now that you have installed PostgreSQL and Apache AGE on your MacOS device, you may be wondering what's next. Here are a few resources and the next steps to help you continue your journey:

PostgreSQL documentation: The PostgreSQL documentation provides comprehensive information on using and managing PostgreSQL. It is a valuable resource for learning about PostgreSQL's features and functionalities. You can access the documentation at https://www.postgresql.org/docs/.

PostgreSQL tutorial: If you're new to PostgreSQL, you can take a look at the PostgreSQL tutorial to learn the basics of PostgreSQL. You can access the tutorial at https://www.postgresqltutorial.com/.

Apache AGE documentation: The Apache AGE documentation provides various guides on how to utilize the tool to its fullest. There are information about clauses, functions, and using Cypher in SQL. You can access the documentation at https://age.apache.org/age-manual/master/index.html

I hope these resources and the next steps will help you to continue your data analysis journey. Good luck, and happy Postgres-ing!

This post is written based on a webinar held by Bitnine and this video guide by Bitnine.