DEV Community: Omar Saad

Building a PostgreSQL Database Client with libpq in C: Connecting and Executing Queries

Omar Saad — Fri, 14 Jul 2023 10:00:09 +0000

In this tutorial, we will explore the basics of libpq, a powerful library that allows C application developers to seamlessly interact with the PostgreSQL database. Whether you're new to libpq or looking to enhance your understanding, this guide will provide you with the necessary knowledge to leverage its capabilities effectively.

Throughout this tutorial, we'll cover the fundamental aspects of libpq, starting from establishing connections to executing queries and handling the results. By the end, you'll have a solid foundation for incorporating PostgreSQL functionality into your C applications with ease.

Before we begin, make sure you have the PostgreSQL server installed and running on your system. With that in place, let's dive into the world of libpq and discover how it can simplify your interaction with the PostgreSQL database in your C applications.

Setting Up the Environment

Before we dive into using libpq, let's ensure that we have the necessary environment set up on our computer. Follow the steps below to prepare your environment for working with libpq:

Install PostgreSQL:
- Visit the official PostgreSQL website (https://www.postgresql.org) and download the appropriate version for your operating system.
- Follow the installation instructions provided by PostgreSQL to install the software on your computer.
- Make sure to remember the location where PostgreSQL is installed, as we'll need it later.
Choose a Directory:
- Decide on a directory where you want to create your C file for working with libpq. This directory can be anywhere on your computer.
Create a C File:
- Open a text editor or an integrated development environment (IDE) of your choice.
- Create a new file with a ".c" extension (e.g., libpq_example.c).
- Save the file in the directory you selected earlier.

Now, your environment is set up, and you have a dedicated C file to work with libpq. In the next sections, we will explore how to utilize libpq to interact with PostgreSQL from your C application.

Setting Up libpq

To begin working with libpq in your C program, you need to import the necessary libraries. Open your C program file and include the following libraries at the top:

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

These libraries provide essential functionalities for your program, including input/output operations, memory allocation and the libpq library itself.

If your integrated development environment (IDE) displays an error in the libpq-fe.h include line, don't worry. We will address this issue during the compilation process by linking the required libraries.

By including these libraries in your program, you're ready to proceed with utilizing libpq to interact with PostgreSQL effectively. In the upcoming sections, we will explore how to establish connections, execute queries, and handle the results using libpq in your C application.

Establishing a Connection

To establish a connection to the PostgreSQL database using libpq, we will use the PQconnectdb() function. This function opens a new database connection based on the parameters provided in the conninfo string.

The conninfo string contains one or more parameter settings in the format of keyword=value, separated by whitespace. You can use default parameters by passing an empty string, or specify custom parameters as needed. To include a null value or a value with spaces, enclose it in single quotes (keyword='value'). If necessary, escape single quotes within the value using a backslash (\'). Spaces around the equal sign are optional.

It's important to note that the PQconnectdb() function always returns a non-null PGconn object pointer, unless there is insufficient memory to allocate the object.

During the connection process, you can check the status of the connection using the PQstatus() function. If the status is CONNECTION_BAD, the connection procedure has failed. Conversely, if the status is CONNECTION_OK, the connection is ready.

To properly close the connection and free the memory used by the PGconn object, call the PQfinish() function. Even if the backend connection attempt fails (as indicated by PQstatus), it is essential to call PQfinish() to release the allocated memory. After calling PQfinish(), the PGconn pointer should not be used further.

Here's the complete code to establish a connection to the database using libpq:

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

int main(int argc, char *argv[]) {
    printf("libpq tutorial\n");

    // Connect to the database
    // conninfo is a string of keywords and values separated by spaces.
    char *conninfo = "dbname=your_db_name user=your_user_name password=your_password host=localhost port=5432";

    // Create a connection
    PGconn *conn = PQconnectdb(conninfo);

    // Check if the connection is successful
    if (PQstatus(conn) != CONNECTION_OK) {
        // If not successful, print the error message and finish the connection
        printf("Error while connecting to the database server: %s\n", PQerrorMessage(conn));

        // Finish the connection
        PQfinish(conn);

        // Exit the program
        exit(1);
    }

    // We have successfully established a connection to the database server
    printf("Connection Established\n");
    printf("Port: %s\n", PQport(conn));
    printf("Host: %s\n", PQhost(conn));
    printf("DBName: %s\n", PQdb(conn));

    // Close the connection and free the memory
    PQfinish(conn);

    return 0;
}

In this code, replace your_db_name, your_user_name, and your_password with the appropriate values for your PostgreSQL setup. The program prints the connection details, such as the port, host, and database name, to confirm the successful connection. Finally, the connection is closed using PQfinish() to release the memory allocated for the PGconn object.

Executing Queries

Once a successful connection to the database has been established, we can proceed to execute queries using libpq. The main function we'll use for query execution is PQexec().

The PQexec() function is used to submit a query to PostgreSQL and wait for the result. It returns a PGresult pointer, which encapsulates the query result returned by the database backend. In most cases, a non-null pointer is returned, except in situations such as out-of-memory conditions or critical errors preventing the query from being sent to the backend. If a null pointer is returned, it should be treated as a PGRES_FATAL_ERROR result. To obtain more information about the error, you can use the PQerrorMessage() function.

The PGresult structure should be maintained as an abstraction when working with the query result. It is recommended to use accessor functions instead of directly referencing the fields of the PGresult structure, as the fields may change in future versions of libpq.

After executing a query using PQexec(), you can check the result status using PQresultStatus(). The possible result statuses include:

PGRES_EMPTY_QUERY: The query string sent to the backend was empty.
PGRES_COMMAND_OK: The command completed successfully, but no data was returned.
PGRES_TUPLES_OK: The query executed successfully and returned tuples (rows).
PGRES_COPY_OUT: Data transfer (Copy Out) from the server has started.
PGRES_COPY_IN: Data transfer (Copy In) to the server has started.
PGRES_BAD_RESPONSE: The server's response was not understood.
PGRES_NONFATAL_ERROR: A non-fatal error occurred during query execution.
PGRES_FATAL_ERROR: A fatal error occurred during query execution.

If the query result status is PGRES_TUPLES_OK, you can use various functions to retrieve information about the returned tuples. Some useful functions include:

PQntuples(): Returns the number of tuples (rows) in the query result.
PQnfields(): Returns the number of fields (attributes) in each tuple of the query result.
PQfname(): Returns the field (attribute) name associated with the given field index. Field indices start at 0.
PQftype(): Returns the field type associated with the given field index. The returned integer represents an internal coding of the type. Field indices start at 0.
PQgetvalue(): Returns the value of a specific field (attribute) in a tuple of the query result. Tuple and field indices start at 0.

These functions provide essential capabilities for retrieving and working with the query results.

The full code for establishing a database connection and executing a query using libpq:

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

int main(int argc, char *argv[]) {
    printf("libpq tutorial\n");

    // Connect to the database
    // conninfo is a string of keywords and values separated by spaces.
    char *conninfo = "dbname=your_db_name user=your_user_name password=your_password host=localhost port=5432";

    // Create a connection
    PGconn *conn = PQconnectdb(conninfo);

    // Check if the connection is successful
    if (PQstatus(conn) != CONNECTION_OK) {
        // If not successful, print the error message and finish the connection
        printf("Error while connecting to the database server: %s\n", PQerrorMessage(conn));

        // Finish the connection
        PQfinish(conn);

        // Exit the program
        exit(1);
    }

    // We have successfully established a connection to the database server
    printf("Connection Established\n");
    printf("Port: %s\n", PQport(conn));
    printf("Host: %s\n", PQhost(conn));
    printf("DBName: %s\n", PQdb(conn));

    // Execute a query
    char *query = "SELECT * FROM your_table_name";

    // Submit the query and retrieve the result
    PGresult *res = PQexec(conn, query);

    // Check the status of the query result
    ExecStatusType resStatus = PQresultStatus(res);

    // Convert the status to a string and print it
    printf("Query Status: %s\n", PQresStatus(resStatus));

    // Check if the query execution was successful
    if (resStatus != PGRES_TUPLES_OK) {
        // If not successful, print the error message and finish the connection
        printf("Error while executing the query: %s\n", PQerrorMessage(conn));

        // Clear the result
        PQclear(res);

        // Finish the connection
        PQfinish(conn);

        // Exit the program
        exit(1);
    }

    // We have successfully executed the query
    printf("Query Executed Successfully\n");

    // Get the number of rows and columns in the query result
    int rows = PQntuples(res);
    int cols = PQnfields(res);
    printf("Number of rows: %d\n", rows);
    printf("Number of columns: %d\n", cols);

    // Print the column names
    for (int i = 0; i < cols; i++) {
        printf("%s\t", PQfname(res, i));
    }
    printf("\n");

    // Print all the rows and columns
    for (int i = 0; i < rows; i++) {
        for (int j = 0; j < cols; j++) {
            // Print the column value
            printf("%s\t", PQgetvalue(res, i, j));
        }
        printf("\n");
    }

    // Clear the result
    PQclear(res);

    // Finish the connection
    PQfinish(conn);

    return 0;
}

Make sure to replace your_db_name, your_user_name, your_password, and your_table_name with the appropriate values for your PostgreSQL setup. The program establishes a connection, executes a SELECT query, and displays the query result in a tabular format. Finally, it clears the result and closes the connection using the PQclear() and PQfinish() functions, respectively.

Compiling and Running Your Program

To compile and run your code, follow the steps below:

Ensure that the PostgreSQL bin directory is included in your environment variables' path. This allows the compiler to locate the necessary PostgreSQL libraries and executables.
Open the terminal or command prompt and navigate to the directory where your C file is located.
Use the following command to compile your code:

   gcc myprogram.c -o myprogram -I "path/to/postgres/include" -L "path/to/postgres/lib" -lpq

Replace myprogram.c with the name of your C file. The -I flag followed by the path to the PostgreSQL include directory specifies the location of the header files. The -L flag followed by the path to the PostgreSQL lib directory specifies the location of the library files. The -lpq flag tells the compiler to link against the libpq library.

For example:

   gcc myprogram.c -o myprogram -I "/usr/local/pgsql/include" -L "/usr/local/pgsql/lib" -lpq

Once the compilation is successful, you will have an executable file named myprogram in the same directory.
Run your program using the following command:

   ./myprogram

This command executes the myprogram executable.

Make sure to replace "path/to/postgres/include" and "path/to/postgres/lib" with the actual paths to the PostgreSQL include and lib directories on your system.

By following these steps, you will be able to compile and run your libpq program successfully.

Congratulations! You've Created a C Program to Connect to a PostgreSQL Database and Execute a Query

References

Here are the references for further reading and exploring libpq and PostgreSQL:

libpq - PostgreSQL C Library Documentation:
- Link: libpq - PostgreSQL C Library Documentation
PostgreSQL Documentation:
- The official documentation for PostgreSQL, which covers various aspects of working with PostgreSQL, including SQL syntax, administration, and client interfaces.
- Link: PostgreSQL Documentation

These references will serve as valuable resources to deepen your understanding of libpq and PostgreSQL as you continue to explore and develop your C applications.

Happy learning and coding!

Indexing in PostgreSQL: Boost Your Database Queries

Omar Saad — Sun, 09 Jul 2023 10:06:04 +0000

In this tutorial, we will explore the concept of indexing in PostgreSQL, a popular relational database system. Indexing is a technique that PostgreSQL provides to help us retrieve data quickly and efficiently. By using indexes effectively, we can significantly improve the performance of our database queries, making them run faster and smoother.

What is Indexing?

Imagine you're reading a book and you need to find a specific chapter. Instead of flipping through each page one by one, you can turn to the book's index, which lists the page numbers of each chapter. This way, you can quickly locate the desired chapter without much effort.

In databases, indexing works similarly. When you want to search for data based on an indexed attribute, you can first search within the data structure that holds the index. This data structure is often sorted, making the search process more efficient. By using indexes, we minimize the need for disk visits, which ultimately speeds up our queries.

How are Indexes Stored?

Indexes are typically stored in separate efficient data structures, such as B-trees, in order to enable fast retrieval of the indexed data. B-trees (short for balanced trees) are commonly used in databases because they provide efficient search and retrieval operations.

A B-tree is a self-balancing tree structure where each node can contain multiple keys and pointers to child nodes. The keys in a B-tree are arranged in a sorted order, allowing for efficient searching and traversal.

When an index is created on a specific column or set of columns, the database system builds the corresponding B-tree data structure to store the index. This structure organizes the indexed values in a hierarchical manner, which facilitates quick data access.

The B-tree structure allows for efficient searching by performing a series of comparisons to determine the path through the tree, ultimately leading to the desired data. This minimizes the number of disk accesses required to locate the indexed information, resulting in faster retrieval times.

By using B-trees and other efficient data structures, databases like PostgreSQL optimize index storage and retrieval, enabling speedy access to indexed data and improving overall query performance.

Types of Indexes in PostgreSQL

PostgreSQL offers various types of indexes to cater to different use cases. Here are the commonly used index types:

B-tree:
- B-tree indexes are the default index type in PostgreSQL.
- They are well-suited for handling range queries and provide efficient searching, insertion, and deletion operations.
- B-tree indexes work effectively for most common scenarios.
Hash:
- Hash indexes use a hash function to map keys to specific locations in the index.
- They are particularly efficient for equality-based lookups but may not perform well with range queries.
- Hash indexes work best when the indexed column has a high number of distinct values.
GiST (Generalized Search Tree):
- GiST indexes are versatile and support a wide range of data types.
- They provide customizable search strategies for specific data types, allowing efficient searching for non-standard criteria.
- GiST indexes are useful for geometric, text, and network data types, among others.
SP-GiST (Space-Partitioned Generalized Search Tree):
- SP-GiST indexes are an extension of GiST indexes and offer improved performance for certain types of data.
- They are designed to handle space-partitioning problems and can be advantageous for specific data structures, such as quad-trees or prefix trees.
GIN (Generalized Inverted Index):
- GIN indexes are specialized for handling complex queries involving array-like data types, full-text search, and other composite types.
- They allow efficient searching for values within arrays or composite types, making them suitable for scenarios where containment or similarity checks are required.

When creating an index in PostgreSQL, you can specify the desired index type based on your specific requirements. Each index type has its own advantages and considerations, so it's important to choose the appropriate index type for the given use case.

Disadvantages of Indexes

Although Indexes help to improve query performance in many cases but they also have disadvantages!

Additional Storage:
- Indexes require additional storage space, although it is typically smaller compared to the size of the table.
- The overhead of maintaining indexes should be considered when estimating storage requirements.
Slower Writes:
- Whenever a row is inserted or updated, the corresponding indexes need to be updated as well.
- This additional maintenance can slow down write operations, so it's essential to evaluate the trade-off between improved read performance and potentially slower writes.

It's crucial to understand that indexing is not a universal solution. It requires a thorough understanding of the data and careful consideration of the specific use case. As Stéphane Faroult and Peter Robson state in their book "The Art of SQL,"

Indexing is not a panacea: effective deployment rests on your complete understanding of the data you are dealing with and making the appropriate judgments.

Conclusion

In conclusion, indexes are valuable tools for improving query performance in PostgreSQL. However, it is essential to approach indexing with a clear understanding of why and when to use it. Consider the specific requirements and characteristics of your database before implementing indexes.

Optimization is an ongoing process when it comes to indexes. Regularly reviewing and optimizing indexes can help ensure that they continue to enhance query performance effectively. This may involve monitoring index usage, considering different index types, and making adjustments as needed.

Remember, indexing is not a one-size-fits-all solution. It requires careful consideration and optimization to strike the right balance between improved performance and any potential drawbacks. By employing indexing judiciously and continually optimizing it, you can maximize the benefits of indexes in PostgreSQL and enhance the overall efficiency of your database operations.

References

Trigonometric Functions in Apache AGE

Omar Saad — Wed, 28 Jun 2023 20:00:43 +0000

In this tutorial, we will delve into the trigonometric functions offered by Apache AGE. These functions can be utilized in our Cypher queries to enhance their functionality and calculations.

What is Apache AGE ?

Apache AGE is a graph database system that is built on top of PostgreSQL. It allows users to store, manage, and analyze large-scale graph data in a highly performant and scalable way.

Apache AGE combines the benefits of a powerful relational database management system (PostgreSQL) with the flexibility and scalability of a graph database. This means that users can store their data in a relational format and also perform graph-based queries and analyses on the data.

Prerequisites

PostgreSQL Version 11 or 12: Make sure you have PostgreSQL installed on your machine. Apache AGE relies on PostgreSQL, so it's important to have either version 11 or 12.
Apache AGE Installation and Configuration: Install and configure Apache AGE to seamlessly integrate with your PostgreSQL installation. If you haven't installed PostgreSQL and Apache AGE yet, we recommend following our comprehensive step-by-step guide for easy installation on Windows. You can access the tutorial here. This guide will walk you through the entire process and ensure a smooth setup.

Trigonometric Functions in Apache AGE

degrees

degrees() function in Apache AGE is used to convert radians to degrees.

Example:

SELECT *
FROM cypher('graph_name', $$
    RETURN degrees(3.14159)
$$) as (deg agtype);

The number of degrees of 3.14159 is returned which will be around 180.

radians

radians() function in Apache AGE is used to convert degree to radian.

Example:

SELECT *
FROM cypher('graph_name', $$
    RETURN radians(180)
$$) as (deg agtype);

The radian of 180 degrees will be returned which is pi.

pi() returns the mathematical constant pi.

SELECT *
FROM cypher('graph_name', $$
    RETURN pi()
$$) as (p agtype);

The constant pi should be returned.

sin

sin() returns the sine of the given number.

SELECT *
FROM cypher('graph_name', $$
    RETURN sin(0.5)
$$) as (s agtype);

The result of sin(0.5) will be returned.

cos

cos() returns the cosine of a given number.

SELECT *
FROM cypher('graph_name', $$
    RETURN cos(0.5)
$$) as (s agtype);

The result of cos(0.5) will be returned.

tan

tan() returns the tangent of a given number.

SELECT *
FROM cypher('graph_name', $$
    RETURN tan(0.5)
$$) as (s agtype);

The result of tan(0.5) will be returned.

In addition to the functions we discussed earlier, Apache AGE also provides other useful trigonometric functions, namely acos(), asin(), atan(), and atan2(). These functions offer various functionalities for trigonometric calculations. acos() calculates the arc cosine of a value, asin() computes the arc sine, atan() evaluates the arc tangent, and atan2() allows you to calculate the arc tangent of two given values. The usage of these functions is similar to what we previously explored, making them accessible for performing different trigonometric computations on your graph data within Apache AGE's Cypher queries.

Conclusion

In conclusion, Apache AGE offers a range of trigonometric functions that can be leveraged for performing calculations involving angles and trigonometric operations in graph analysis. By incorporating these functions into your Cypher queries, you can enhance your graph analysis workflows and gain deeper insights from your graph data.

Exploring Numeric Functions in Apache AGE: A Comprehensive Tutorial

Omar Saad — Sat, 24 Jun 2023 22:21:55 +0000

In this tutorial, we will delve into the numeric functions offered by Apache AGE. These functions can be utilized in our Cypher queries to enhance their functionality and calculations.

What is Apache AGE ?

Apache AGE is a graph database system that is built on top of PostgreSQL. It allows users to store, manage, and analyze large-scale graph data in a highly performant and scalable way.

Prerequisites

PostgreSQL Version 11 or 12: Make sure you have PostgreSQL installed on your machine. Apache AGE relies on PostgreSQL, so it's important to have either version 11 or 12.
Apache AGE Installation and Configuration: Install and configure Apache AGE to seamlessly integrate with your PostgreSQL installation. If you haven't installed PostgreSQL and Apache AGE yet, we recommend following our comprehensive step-by-step guide for easy installation on Windows. You can access the tutorial here. This guide will walk you through the entire process and ensure a smooth setup.

Numeric Functions in Apache AGE

rand()

The rand() function in Apache AGE generates a random floating-point number within the range of 0 (inclusive) to 1 (exclusive), denoted as [0, 1). The numbers produced by this function follow an approximate uniform distribution.

SELECT *
FROM cypher('graph_name', $$
    RETURN rand()
$$) AS (r agtype);

The above query will return a random number.

abs()

The abs() function in Apache AGE calculates and returns the absolute value of a given number.

Here's an example of using the abs() function in a Cypher query within Apache AGE:

SELECT *
FROM cypher('graph_name', $$
    RETURN abs(-5)
$$) AS (result agtype);

The above query will return the absolute value which is 5.

ceil()

The ceil() function returns the smallest floating-point number that is greater than or equal to the given number and equal to a mathematical integer.

Here's an example using the ceil() function in a Cypher query within Apache AGE:

SELECT *
FROM cypher('graph_name', $$
    RETURN ceil(0.1) 
$$) AS (ceil agtype);

The ceiling of 0.1 is returned which is 1.

floor()

The floor() function returns the greatest floating-point number that is less than or equal to the given number and equal to a mathematical integer.

Here's an example using the floor() function in a Cypher query within Apache AGE:

SELECT *
FROM cypher('graph_name', $$
    RETURN floor(0.1) 
$$) AS (flr agtype);

The floor of 0.1 is returned which is 0.

round()

The round() function returns the value of the given number rounded to the nearest integer.

Here's an example using the round() function in a Cypher query within Apache AGE:

SELECT *
FROM cypher('graph_name', $$
    RETURN round(3.141592) 
$$) AS (rounded_value agtype);

In the above query 3.0 is returned.

sign()

The sign() function in Apache AGE returns the sign of a given number. It indicates whether the number is positive, negative, or zero.

Here's an example using the sign() function in a Cypher query within Apache AGE:

SELECT *
FROM cypher('graph_name', $$
    RETURN sign(-5) 
$$) AS (sign_value agtype);

The sign() function returns the following values:

1 if the number is positive
-1 if the number is negative
0 if the number is zero

In this tutorial, we explored various numeric functions available in Apache AGE and learned how to incorporate them into our Cypher queries. We covered essential functions such as rand(), abs(), ceil(), floor(), round(), and sign(), each serving a distinct purpose in performing calculations and manipulating numeric values. By leveraging these functions, we can enhance the capabilities of our Cypher queries within Apache AGE, enabling us to work with random numbers, absolute values, rounding, ceiling, floor, and signum operations. With this newfound knowledge, you are now equipped to utilize these numeric functions effectively to analyze and manipulate numerical data in your Apache AGE graph databases.

References
Apache AGE documentation
Apache AGE Github

Contribute to Apache AGE

Apache AGE Github
Apache AGE Viewer Github

Building Graph Database Applications with Apache AGE and Node.js: A Step-by-Step Tutorial

Omar Saad — Thu, 22 Jun 2023 20:47:00 +0000

In this tutorial we will introduce how to use Apache AGE with your NodeJs application.

What is Apache AGE ?

Apache AGE is a graph database system that is built on top of PostgreSQL. It allows users to store, manage, and analyze large-scale graph data in a highly performant and scalable way.

Prerequisites

PostgreSQL Version 11 or 12: Make sure you have PostgreSQL installed on your machine. Apache AGE relies on PostgreSQL, so it's important to have either version 11 or 12.
Apache AGE Installation and Configuration: Install and configure Apache AGE to seamlessly integrate with your PostgreSQL installation. If you haven't installed PostgreSQL and Apache AGE yet, we recommend following our comprehensive step-by-step guide for easy installation on Windows. You can access the tutorial here. This guide will walk you through the entire process and ensure a smooth setup.
Node.js: Ensure that Node.js is installed on your system. You can download and install the latest version of Node.js from the official website: Node.js

Having Node.js installed will provide the necessary runtime environment for running your Node.js applications and executing JavaScript code.

Verify the installation by opening a terminal or command prompt and running the following command:

node --version

You should see the version number of Node.js printed in the terminal if it is installed correctly.

If Node.js is not installed, please download and install it using the appropriate installer for your operating system.

Now that you have Node.js installed, you're ready to proceed with the tutorial and explore the integration of Apache AGE with Node.js and PostgreSQL.

Apache AGE with NodeJs

1. To initialize a new Node.js project, you can run the npm init command in your project directory. This command will prompt you with a series of questions to gather information about your project and generate a package.json file. The package.json file is essential for managing your project's dependencies and scripts.

To run npm init, open your terminal or command prompt, navigate to your project directory, and execute the following command:

npm init

You will be prompted to enter various details such as the project name, version, description, entry point, test command, repository, and more. Feel free to provide the relevant information according to your project requirements. If you wish to skip any prompts, you can add the -y flag to automatically generate the package.json file with default values:

npm init -y

Once you have completed the npm init process, a package.json file will be created in your project directory, which will serve as a configuration file for your Node.js project.

2.To configure your application's environment variables, you'll need to create a .env file. This file will store the necessary variables for your application to connect to the PostgreSQL database. Here's an example of how the .env file should be structured:

POSTGRES_HOST=127.0.0.1
POSTGRES_PORT=5432
POSTGRES_DB=your_db_name
POSTGRES_USER=your_user_name
POSTGRES_PASSWORD=your_password

Replace your_db_name, your_user_name, and your_password with the actual values specific to your PostgreSQL setup. These variables specify the host, port, database name, username, and password required for establishing a connection between your Node.js application and the PostgreSQL database.

3.Include the following dependencies in package.json file.

"express": "^4.17.1",
"pg": "^8.5.1",
"dotenv": "^8.2.0"

Ensure that the formatting of your package.json file remains intact.

Once you have added the dependencies, save the changes to your package.json file. Your Node.js application will now be able to utilize these dependencies.

4. Create server.js file in your main directory. This file will be the entry point of your application.

const express = require('express');
require('dotenv').config();

const app = express();
const port = process.env.PORT || 3000;

// Define your routes and middleware here
//TODO

app.listen(port, () => {
  console.log(`Server running on port ${port}`);
});

5. Create a file named database.js in your project's directory. This file will handle the database connection settings.

import { Pool } from 'pg';
import dotenv from 'dotenv';

dotenv.config();

const POSTGRES_HOST = process.env.POSTGRES_HOST;
const POSTGRES_PORT = process.env.POSTGRES_PORT;
const POSTGRES_DB = process.env.POSTGRES_DB;
const POSTGRES_USER = process.env.POSTGRES_USER;
const POSTGRES_PASSWORD = process.env.POSTGRES_PASSWORD;

const client = new Pool({
  host: POSTGRES_HOST,
  port: POSTGRES_PORT,
  database: POSTGRES_DB,
  user: POSTGRES_USER,
  password: POSTGRES_PASSWORD,
  // If local, set ssl to false to avoid errors
  // ssl: true,
});
async function initAGE() {
    const sql = `CREATE EXTENSION IF NOT EXISTS age;
    LOAD 'age';
    client.query(sql);
    SET search_path to ag_catalog,"$user",public; `;
    try{
    const conn = await client.connect();
    await conn.query(sql);
    conn.release();
    } catch (err) {
        throw new Error(`Could not init agent. Error: ${err}`);
    }
}


export default {
    initAGE,
    client
}

6. Create product_model.js file in which we will implement the basic CRUD operations for our products application.

import client from "./database";


export class ProductStore {
    GRAPH_NAME = 'products';
    async getAllProducts() {
        try {
            const conn = await client.connect();
            const sql = `SELECT * FROM 
            cypher('${GRAPH_NAME}', $$
                MATCH (p:Product) RETURN p
                $$)
            as (result agtype)`;
            const result = await conn.query(sql);
            conn.release();
            return result.rows;
        } catch (err) {
            throw new Error(`Could not get products. Error: ${err}`);
        }
    }

    async createProduct(name,price,category) {
        try{
            const conn = await client.connect();
            const sql = `SELECT * FROM 
            cypher('${GRAPH_NAME}', $$
                CREATE (p:Product {name: '${name}', price: ${price}, category: '${category}'}) RETURN p
                $$)
            as (result agtype)`;
            const result = await conn.query(sql);
            conn.release();
            return result.rows;

        } catch (err) {
            throw new Error(`Could not add new product ${product.name}. Error: ${err}`);
        }
    }

    async getProductById(id) {
        try {
            const conn = await client.connect();
            const sql = `SELECT * FROM 
            cypher('${GRAPH_NAME}', $$
                MATCH (p:Product) WHERE id(p) = ${id} RETURN p
                $$)
            as (result agtype)`;
            const result = await conn.query(sql);
            conn.release();
            return result.rows;
        } catch (err) {
            throw new Error(`Could not get product ${id}. Error: ${err}`);
        }
    }

    async updateProduct(id, name, price, category) {
        try {
            const conn = await client.connect();
            const sql = `SELECT * FROM 
            cypher('${GRAPH_NAME}', $$
                MATCH (p:Product) WHERE id(p) = ${id} SET p.name = '${name}', p.price = ${price}, p.category = '${category}' RETURN p
                $$)
            as (result agtype)`;
            const result = await conn.query(sql);
            conn.release();
            return result.rows;
        } catch (err) {
            throw new Error(`Could not update product ${id}. Error: ${err}`);
        }
    }

    async deleteProduct(id) {
        try {
            const conn = await client.connect();
            const sql = `SELECT * FROM 
            cypher('${GRAPH_NAME}', $$
                MATCH (p:Product) WHERE id(p) = ${id} DELETE p
                $$)
            as (result agtype)`;
            const result = await conn.query(sql);
            conn.release();
            return result.rows;
        } catch (err) {
            throw new Error(`Could not delete product ${id}. Error: ${err}`);
        }
    }
}

7. We will edit our server.js file to add products routes.
For this example we will only implement GET and POST routes.
The updated server.js file:

const express = require('express');
require('dotenv').config();


const app = express();
const port = process.env.PORT || 3000;

// Define your routes and middleware here
const initAGE = require('./database').initAGE;
// Load AGE extension
initAGE();
app.get('/products', (req, res) => {
    try{
    const productModel = require('./product_model');
    const products = productModel.getProducts();
    res.send({
        message : 'Products retrieved successfully',
        data : products
    });
    return;
    } catch (err) {
        res.send({
            message : `Could not get products. Error: ${err}`,
            data : []
        });
        return;
    }

});

app.post('/products', (req, res) => {
    try{
    const productModel = require('./product_model');
    const products = productModel.createProduct(req.body.name, req.body.price, req.body.category);
    res.send({
        message : 'Products created successfully',
        data : products
    });
    return;
    } catch (err) {
        res.send({
            message : `Could not create products. Error: ${err}`,
            data : []
        });
        return;
    }

});

app.listen(port, () => {
  console.log(`Server running on port ${port}`);
});

8. Run npm install to install the project dependencies.

9. Run npm run start to start nodejs server.

10. Now you can test the server using postman or any other tools by making the following requests:

POST localhost:3000/products

The body of the POST request should be in the following format:

"name" : "Iphone 14",
"price": 1000,
"category" : "Mobile phones"

If we want to retrieve all the products than we should send the following request:

GET localhost:3000/products

This request should return all the products in our database.

Congratulations on successfully building your Node.js application integrated with PostgreSQL and Apache AGE! You've created a powerful combination that enables efficient data management and advanced graph-based querying. Well done!

In the upcoming tutorials, we will delve into building more complex applications to further deepen our understanding. We will explore advanced features, demonstrate practical use cases, and tackle more intricate scenarios. By expanding our knowledge and exploring these advanced concepts, we will be able to leverage the full potential of integrating Apache AGE.

References
Apache AGE documentation
Apache AGE Github

Contribute to Apache AGE

Apache AGE Github
Apache AGE Viewer Github

Adding Custom Functions to Apache AGE Source Code: A Step-by-Step Tutorial

Omar Saad — Sun, 04 Jun 2023 19:55:49 +0000

In this tutorial, we will walk you through the process of adding a new function to the Apache AGE source code. For the purpose of this tutorial, we will create a simple function that takes two integer numbers as arguments, adds them together, and returns the result.

Also in the upcoming tutorial, we will improve our function by enabling it to accept agtype as an input, allowing seamless integration with Cypher queries. Stay tuned for more!

What is Apache AGE ?

Apache AGE is a graph extension for PostgreSQL, designed to enhance its capabilities by adding graph database functionality. With AGE, users can utilize the power and flexibility of PostgreSQL while also leveraging graph-based data storage, querying, and analysis. This combination allows for efficient handling of graph data within a familiar PostgreSQL environment.

Prerequisites

PostgreSQL Version 11 or 12: Make sure you have PostgreSQL installed on your machine. Apache AGE relies on PostgreSQL, so it's important to have either version 11 or 12.
Apache AGE Installation and Configuration: Install and configure Apache AGE to seamlessly integrate with your PostgreSQL installation. If you haven't installed PostgreSQL and Apache AGE yet, we recommend following our comprehensive step-by-step guide for easy installation on Windows. You can access the tutorial here. This guide will walk you through the entire process and ensure a smooth setup.

Creating a Custom Function in Apache AGE Source Code

In this section of the tutorial, we will go through the steps to create a custom function that adds two integer numbers and returns the result. Follow these numbered steps:

Create a new file named math_functions.c in the src/backend/commands folder. This file will serve as the implementation for our custom mathematical function.
Open the math_functions.c file in your preferred text editor or integrated development environment (IDE).
Include the necessary header files for the function implementation. These may include postgres.h for PostgreSQL-specific declarations and fmgr.h for function manager definitions.

4. Define the adder function as follows:

 #include "postgres.h"
 #include "fmgr.h"

 PG_FUNCTION_INFO_V1(adder);

 Datum
 adder(PG_FUNCTION_ARGS)
 {
    elog(INFO, "Adder function called");

    if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
    {
        ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                        errmsg("Num1 and Num2 must not be NULL")));

    }

    // // Get the values of the two arguments
    int arg1 = PG_GETARG_INT32(0);
    int arg2 = PG_GETARG_INT32(1);
    int result = arg1 + arg2;

    PG_RETURN_INT32(result);

  }

In the code above, we first include the necessary header files: postgres.h for PostgreSQL-specific declarations and fmgr.h for function manager definitions and utils/agtype.h for AGType functionality, are included.

The line PG_FUNCTION_INFO_V1(adder); defines the function information for the adder function. This macro is necessary for PostgreSQL to recognize the function.

The adder function is implemented to take two int arguments, arg1 and arg2. It calculates their sum and stores it in the result variable.

Then a check is performed to verify if both arguments are provided. If any of the arguments is not provided (i.e., NULL), an error is raised using the ereport function, specifying that both arguments must be provided.

Finally, PG_RETURN_INT32(result) is used to return the result of the addition as the output of the function.

Note: elog is used to print to the console you can use it to debug your code.

5. In the main project directory, locate the file named Makefile and open it. Within the Makefile, locate the OBJS section and add the following directory to it.

src/backend/commands/math_functions.o

Adding this entry to the OBJS section ensures that the object file is included in the compilation process when building the project.

6. Open the age--1.2.0.sql file and add the following signature to it:

-- Signature: adder(integer, integer)
-- Purpose: Adds two integers and returns the result.
CREATE  FUNCTION adder(int, int)
    RETURNS int
    LANGUAGE c
    AS 'MODULE_PATHNAME';

The above code adds a signature to the age--1.2.0.sql file. This signature defines a function named adder that takes two integer arguments. The function is implemented in the C language and its code resides in the shared library specified by 'MODULE_PATHNAME'.

7. Navigate to the main project directory and execute the command make install to build the source code.

8. To reset the Apache AGE extension and start fresh, you can execute the following commands:

DROP EXTENSION age CASCADE;
CREATE EXTENSION age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

9. Run the following command to start the database server.

 pg_ctl -D /path/to/data/directory -l logfile -o  "-p <your_port_number>" start

Replace /path/to/data/directory with the actual path to your PostgreSQL data directory. This is the directory where the database files are stored.

Replace <your_port_number> with the port number on which you want the database server to listen.

Running this command will start the PostgreSQL database server using the specified data directory and port number. The server log will be written to the specified logfile.

10. Testing Our Function.

First start psql using the following command:

psql -U <your_username> -p <your_port_number> -d <your_database_name>

Then we will call our function with 1 an 2 as an arguments.

 SELECT * FROM ag_catalog.adder(1,2) AS res;

Output:

That's fantastic! Creating your first function in the Apache AGE source code is indeed a significant step towards contributing to the project. By adding custom functionality, you can enhance the capabilities of Apache AGE and make a valuable contribution to the community.

Contributing to open-source projects like Apache AGE not only allows you to expand your skills but also enables you to collaborate with other developers and make a positive impact on the project's development.

Congratulations on taking this important step, and best of luck with your future contributions to Apache AGE!

In the upcoming tutorial, we will improve our function by enabling it to accept agtype as an input, allowing seamless integration with Cypher queries. Stay tuned for more!

References
Guide to AGE contribution and modifying the source code to add new functions
Apache AGE documentation
Apache AGE Github

Contribute to Apache AGE

Apache AGE Github
Apache AGE Viewer Github

Mastering Query Performance Analysis and Query Plan Understanding in Apache AGE

Omar Saad — Fri, 19 May 2023 13:09:56 +0000

Welcome to our tutorial on analyzing query performance and understanding the exact query plan executed by Apache AGE. In today's data-driven world, optimizing query execution is crucial for efficient data processing and extracting valuable insights. By diving into the intricacies of query analysis, you can uncover potential performance bottlenecks and make informed decisions to enhance the speed and resource utilization of your queries.

In this tutorial, we will guide you through the process of analyzing query performance in Apache AGE.

What is Apache AGE ?

Apache AGE is a graph database system that is built on top of PostgreSQL. It allows users to store, manage, and analyze large-scale graph data in a highly performant and scalable way.

Prerequisites

Before diving into the query performance analysis and query plan understanding in Apache AGE, make sure you have the following prerequisites in place:

PostgreSQL Version 11 or 12: Ensure that you have PostgreSQL installed on your machine. Apache AGE works in conjunction with PostgreSQL, so having either version 11 or 12 is necessary.
Apache AGE Installation and Configuration: Install and configure Apache AGE to work seamlessly with your PostgreSQL installation. This integration will enable you to leverage Apache AGE's powerful capabilities for query performance analysis and query plan understanding.

If you haven't installed PostgreSQL and Apache AGE yet, we recommend following our step-by-step guide for easy installation on Windows. You can find the tutorial here. This guide will walk you through the installation process, ensuring a smooth setup for your query analysis journey in Apache AGE.

Creating a simple graph database schema

In this section, we will create a simple graph database schema specifically tailored for an online mobile phone store. This schema will serve as the foundation for our query analysis examples in this tutorial.

To begin, execute the following query to create a graph named mobile_phones_store:

SELECT create_graph('mobile_phones_store');

In our graph, we will have two types of vertices: 'User' and 'Product'. The 'User' vertex represents the customers of the online store, while the 'Product' vertex represents the mobile phones available for purchase.

Additionally, we will have one type of edge called 'PURCHASED'. This edge signifies the relationship between a 'User' and a 'Product' when a user makes a purchase.

Then we will populate our graph database with some data using the following queries:

Adding users to our database:

SELECT * FROM cypher('mobile_phones_store',
$$
MERGE(:User{name:"user1" , country:"USA" , birth_date:"10-10-1980"})

 $$) as (res agtype)
;

SELECT * FROM cypher('mobile_phones_store',
$$
MERGE(:User{name:"user2" , country:"Egypt" , birth_date:"7-7-2000"})

 $$) as (res agtype)
;

You can repeat the above query pattern for any number of users you want to add. Simply change the user details such as name, country, and birth date accordingly.

Adding products to our database:

SELECT * FROM cypher('mobile_phones_store',
$$
MERGE(:Product{name:"Iphone 14", brand:"Apple", price:1000.00})
$$) AS (V agtype);

SELECT * FROM cypher('mobile_phones_store',
$$
MERGE(:Product{name:"Samsung S23", brand:"Samsung", price:800.00})
$$) AS (V agtype);

You can add any number of products you want.

Adding Relationships

SELECT * FROM cypher('mobile_phones_store',
$$
MATCH (u:User{name:"user1"}), (p:Product{name:"Iphone 14"})
MERGE (u)-[:PURCHASED]->(p)
$$) AS (res agtype);

In this example, we're adding a relationship of type "PURCHASED" between the user "user1" and the product "Product1". You can modify the query by changing the user and product names to create relationships between different users and products.

Query Execution and Analysis

Now that we have created a sample graph database and populated it with data, it's time to dive into query execution and analysis. In this section, we will explore various techniques and tools to analyze the performance of queries and understand the query plans executed by Apache AGE.

Why we need to know how our query is executed ?

Understanding how a query is executed is crucial for several reasons:

Query Performance Optimization: By knowing how a query is executed, you can identify performance bottlenecks and optimize the query for better execution. You can analyze the query plan, identify expensive operations, and make informed decisions to improve the query's performance.
Identifying Performance Issues: If a query is not performing well, understanding its execution process can help you pinpoint the areas causing the slowdown. It allows you to identify inefficient joins, excessive data scans, or suboptimal use of indexes. This knowledge enables you to address these issues and optimize the query accordingly.
Efficient Indexing: Understanding query execution helps you make informed decisions about indexing. You can analyze the query plan and identify which columns are frequently used in filters or joins. This knowledge allows you to create appropriate indexes on those columns, improving query performance by reducing the data retrieval and processing overhead.
Query Tuning and Optimization: When you know how a query is executed, you can tune and optimize it by rewriting the query, restructuring the schema, or applying query hints. This knowledge helps you make informed decisions to achieve better query performance and resource utilization.

How to know the execution plan of a query in Apache AGE ?

To know the execution plan in Apache AGE, you can use the EXPLAIN keyword. It provides detailed information about how the engine executes a query.

Let's take an example where we want to retrieve all users who have purchased any Apple products. To analyze the execution plan, you can run the following query:

SELECT * FROM cypher('mobile_phones_store',$$
EXPLAIN MATCH (u:User)-[r:PURCHASED]->(p:Product{brand: "Apple"}) 
RETURN u,r,p

$$) AS (u agtype,r agtype,p agtype);

The output of the query is:

The execution plan:

The query plan suggests that the execution involves a hash join operation and a nested loop operation.

1) Hash Join:

Estimated cost: The estimated cost of the hash join operation is between 1.09 and 3.29 (representing the overall expense of executing the join).
Expected rows: The hash join is expected to produce 2 rows as a result.
Width: The width of each row in the hash join operation is estimated to be 96 bytes. This refers to the approximate size of each row in terms of the amount of data it contains.
Hash condition: The join operation is performed based on the "start_id" column of the "PURCHASED" relation (which represents the relationship between users and products) and the "id" column of the "USER" relation. This means that the hash join will match rows where the "start_id" in the "PURCHASED" relation is equal to the "id" in the "USER" relation.

2) Nested Loop:

Estimated cost: 0.00 to 2.14
Expected rows: 2
Width: 108
Join filter: The join is filtered based on the "end_id" column of relation "r"(which is the "PURCHASED" edge) and the "id" column of relation "p" (which is the "Product" relation).

a. Seq Scan on "Product" p:

Estimated cost: 0.00 to 1.03
Expected rows: 1
Width: 79
Filter: The scan is filtered based on the brand property value which is "Apple" in our example.

"Seq Scan" stands for Sequential Scan, which is a type of table access method used in database systems. It refers to the process of scanning a table sequentially from the beginning to the end to retrieve the desired rows.

This means that a sequential scan is performed on the "Product" table (relation "p"). The database engine reads each row of the table in a sequential manner to check for the desired condition. In this case, the condition is applied to the "properties" column to filter the rows and select only those where the "brand" property matches "Apple".

The estimated cost represents the expected expense of performing the sequential scan operation, which can vary depending on factors such as the size of the table and the efficiency of the storage system. The expected rows and width provide estimates about the number of rows and the size of each row for the "Product" table in this particular scan operation.

b. Seq Scan on "PURCHASED" r:

Estimated cost: 0.00 to 1.05
Expected rows: 5
Width: 29

3) Hash:

Estimated cost: 1.04
Expected rows: 4
Width: 76
Seq Scan on "User" u:
Estimated cost: 0.00 to 1.04
Expected rows: 4
Width: 76

Overall, the execution plan suggests that the query involves joining the "Product" table with the "PURCHASED" table, filtered based on the brand value "Apple". The result is then joined with the "User" table based on their respective IDs. The exact costs, row estimates, and widths may vary depending on the data and database statistics.

Conclusion

In this tutorial, we learned how to analyze query performance and understand the execution plan in Apache AGE. By examining the execution plan using the EXPLAIN keyword, we can gain insights into the query's order of operations, join methods, and optimization techniques employed by the query planner.

By continuously refining our understanding of query execution and optimization techniques, we can leverage Apache AGE effectively and build efficient graph-based applications.

References

Apache AGE documentation
Apache AGE Github

Contribute to Apache AGE

Apache AGE Github
Apache AGE Viewer Github

Apache AGE: Combining the Best of Relational and Graph Databases for Effective Data Management

Omar Saad — Thu, 04 May 2023 21:24:50 +0000

In today's digital world, data is everywhere and is growing at an unprecedented rate. To make sense of this data and extract meaningful insights, it is important to choose the right database technology. Apache AGE is one such technology that has gained significant attention in the industry.

What is PostgreSQL ?

PostgreSQL is one of the most famous relational databases in the world that is designed to manage and store large amounts of structured data.

PostgreSQL is open-source and it is known for its reliability, robustness, and scalability. It provides a variety of advanced features, including support for transactions, stored procedures, and triggers.

PostgreSQL supports a wide range of data types, including text, numeric, and date/time data, as well as more complex data structures such as arrays, JSON, and XML.

What is Apache AGE ?

Apache AGE is a graph database system that is built on top of PostgreSQL. It allows users to store, manage, and analyze large-scale graph data in a highly performant and scalable way.

When to choose Relational Databases ?

Relational databases are ideal for applications that require data to be structured in a consistent way, such as those involving financial transactions or inventory management. They are designed to handle data that fits into tables with predefined columns and data types, making them a good fit for applications with clearly defined data models.

Relational databases also have strong consistency and ACID (Atomicity, Consistency, Isolation, and Durability) guarantees, making them well-suited for applications that require strict data integrity and consistency.

When to choose Graph Databases ?

Graph databases are a specialized type of database that are designed to store and manage graph data, which is data that can be represented as a collection of nodes and edges.

Graph databases are ideal for handling complex relationships, unstructured data, and high scalability and performance. They are highly flexible and allow for easy addition or modification of data without affecting the structure of the database. Additionally, built-in visualization tools make it easy to explore and understand the data. If your application involves any of these scenarios, a graph database may be the right choice for you.

When to choose Apache AGE ?

Apache AGE is a unique database technology that combines the benefits of both relational databases and graph databases. It is built on top of PostgreSQL, which means that users can write SQL queries to retrieve data from tables, or write Cypher queries to retrieve data from graphs. This makes Apache AGE an excellent solution, especially for those who want to use a relational database alongside a graph database. Additionally, Apache AGE is an open-source project, which means that it is free to use.

Apache AGE provides a variety of libraries for different frameworks, such as Node.js, Python and Java, making it easy for developers to integrate it into their applications. It is also very user-friendly and easy to use, and the Apache AGE documentation provides all the necessary information to get started with the database technology.

You can download Apache AGE and start writing your first query by following
Apache AGE documentation.

Also If you want to visualize your queries you can use AGE Viewer
which is a very good tool to see the results of your queries.

Apache AGE Applications

Apache AGE can be used in a wide range of applications such as :

Social Media Applications
Recommendation Systems.
Fraud Detection.
Network analysis.
Bioinformatics.

The potential applications of Apache AGE are not limited to these examples. In fact, any data that can be represented as a graph can benefit from the capabilities of Apache AGE.

References
Apache AGE documentation
Apache AGE Github

Contribute to Apache AGE
Apache AGE Github
Apache AGE Viewer Github

Step by step guide to install Apache AGE Viewer

Omar Saad — Tue, 25 Apr 2023 22:22:25 +0000

In this post, we will discuss how to install AGE viewer from source code on windows machine.

Prerequisites
Before proceeding with the installation of AGE viewer from source code on your Windows machine, make sure you have the following prerequisites in place:

PostgreSQL: You should have PostgreSQL version 11 or 12 already installed on your machine. If you haven't installed it yet, you can follow this guide for step-by-step instructions: Step by Step Guide to Install PostgreSQL on Windows.
Apache AGE: Apache AGE should be installed and configured with PostgreSQL on your PC. If you haven't installed and configured Apache AGE yet, you can refer to the same guide mentioned above: Step by Step Guide to Install PostgreSQL + Apache AGE on Windows.
Git: You should have Git installed on your machine. If you don't have it installed, you can download and install it from the official Git website Git Installation.

4.Node.js: You should have Node.js installed on your machine. Node.js is a JavaScript runtime that AGE viewer relies on. You can download and install Node.js from the official Node.js website Node.js Installation.

By ensuring that you have PostgreSQL, Apache AGE, Git, and Node.js installed and configured correctly, you'll be ready to proceed with the installation of AGE viewer from source code on your Windows machine.

What is Apache AGE ?

Apache AGE is a graph database system that is built on top of PostgreSQL. It allows users to store, manage, and analyze large-scale graph data in a highly performant and scalable way.

What is Apache-Age Viewer

Apache-Age Viewer is a web based user interface that provides visualization of graph data stored in a PostgreSQL database with AGE extension. It is graph visualization tool, for Apache AGE [1].

How to install Apache AGE viewer ?

Choose a directory where you want to install the project, and then open the Command Prompt (CMD) terminal in that directory.
Clone the age-viewer project by running the following command in the terminal:



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

navigate to the project directory.



cd age-viewer

Install the required node modules using.



npm run setup

Now you can start the age-vewer project.



npm run start

After completing the installation and running the Apache AGE viewer application, your web browser will automatically open with the URL localhost:3000.
Next, you need to establish a connection to a database by filling out the required information, as shown in the image below:

Please ensure that you provide all the necessary details correctly, including the database host, port, username, password, and database name. Once you have entered the information, you can proceed to establish the connection to the database, which will enable the Apache AGE viewer application to access and retrieve data for analysis and visualization.

Congratulations! With Apache AGE viewer successfully installed, you can now run queries and visualize graph data to gain insights from your database. We hope you found this tutorial helpful and enjoyable. If you have any further questions or need additional assistance, feel free to ask. Thank you for following this installation guide!

References
age-viewer
Step by step guide to install PostgreSQL + Apache AGE on Windows

Getting Started with Apache AGE: Writing Your First Query for a Simple Database App

Omar Saad — Mon, 10 Apr 2023 15:35:38 +0000

In this tutorial, we will guide you through the process of creating a simple database using PostgreSQL and Apache AGE. PostgreSQL is a powerful relational database management system (RDBMS) that is widely used in the industry due to its performance, scalability, and reliability. However, when combined with Apache AGE, it adds graph database capabilities, making it even more useful in a variety of applications.

With Apache AGE, you can use SQL and Cypher queries to access and analyze your data. SQL is a standard query language that is widely used for relational databases, while Cypher is a graph query language that allows you to perform powerful graph traversals and pattern matching. By learning both query languages, you'll have a powerful set of tools to work with data in different ways, depending on the needs of your application.

Throughout this tutorial, you will learn the basics of creating tables, inserting data, and querying data using both SQL and Cypher. By the end of the tutorial, you'll have a solid understanding of how to use PostgreSQL and Apache AGE together, and you'll be ready to start building your own applications that leverage the power of both technologies.

Prerequisites

Before starting this tutorial, you need to make sure that you have installed PostgreSQL version 11 or 12 and Apache AGE. If you haven't installed them yet, you can follow the steps in this tutorial Step-by-Step Guide to Install PostgreSQL & Apache AGE on Windows..

To visualize the graph generated by the queries in this tutorial, you can use AGE Viewer. This is a useful tool to help you understand the graph structure and relationships between nodes and edges. It can also help you identify any issues with your queries and data.

AGE Graph Fundamentals: Vertices and Edges

In AGE, a vertex represents a fundamental entity in a graph, which can exist independently. Each vertex may be assigned a label, and may have zero or more outgoing edges, as well as zero or more incoming edges.

On the other hand, an edge in AGE encodes a directed connection between exactly two nodes, namely the source node and the target node. An outgoing edge represents a directed relationship from the perspective of its source node, while an incoming edge represents a directed relationship from the perspective of its target node. Each edge is assigned exactly one edge type.

Understanding the basics of vertices and edges is crucial to working with graphs in AGE. By leveraging these fundamental concepts, users can effectively model complex relationships and dependencies between various entities in their data, enabling them to extract meaningful insights and knowledge.

Database Entities

In this tutorial, we will be creating a basic online ordering application that allows users to place orders for products. The application will utilize two main types of vertices, namely User and Product, and one edge type, which is ORDERS. This means that a user can order one or more products through the ORDERS edge type. By utilizing these vertices and edges, we can represent the relationships and interactions between the various entities in our application.

Creating The Application

1. Configuring AGE with PostgreSQL

To configure AGE with PostgreSQL, you need to load the AGE extension and set the search path to ag_catalog. Here are the correct SQL queries:

LOAD 'age'
SET search_path to ag_catalog,"your_user_name",public;

The first query loads the AGE extension, while the second query sets the search path to ag_catalog and public. Make sure to replace "your_user_name" with your actual PostgreSQL username.

2. Creating a graph

To store our data, we need to create a new graph. This can be achieved by executing the following SQL query:

SELECT * FROM ag_catalog.create_graph('graph_name');

This query will create a new graph with the specified name.

3. Creating Users

First, we will create a 'User' vertex (node) to represent our user in the graph. We can also add some properties to the node such as name, email, phone, and address.

SELECT *
FROM cypher('online_orders', $$
    CREATE(:User{name:"user1" ,email : "test@test.com" , phone:"123456" , address : "Egypt"});
$$) AS (result agtype)

This query will create a new vertex with the label 'User' and the specified properties, and return the result in the 'result' column.

agtype is a data type in AGE that represents an Abstract Graph object. It can store graphs, vertices, edges, and other graph-related data structures. It is similar to the JSON data type in PostgreSQL but is optimized for graph data. The agtype data type is used extensively in AGE for storing and querying graph data.

Adding more users...

SELECT *
FROM cypher('online_orders', $$
    CREATE(:User{name:"user2" ,email : "test2@test.com" , phone:"123488" , address : "USA"})
$$) AS (result agtype)

You can use the following query to verify the inserted users:

SELECT * FROM cypher('online_orders' , $$
MATCH(u:User) RETURN u
$$) AS (result agtype);

This query uses the MATCHclause to retrieve all the User nodes in the online_orders graph and returns them in the result set. The AS result clause renames the result set to result.

4. Creating Products

Next, we can create a 'Product' vertex (node) to hold all product-related data such as name and price (additional properties can also be added).

SELECT *
FROM cypher('online_orders', $$
    CREATE (:Product{name:"Product 1", price: 10.99}),
           (:Product{name:"Product 2", price: 5.99}),
           (:Product{name:"Product 3", price: 7.50})
$$) AS (result agtype);

This query creates three products in our graph.

You can use the following query to verify the inserted products:

SELECT * FROM cypher('online_orders' , $$
MATCH(u:Product) RETURN u
$$) AS (result agtype);

This query uses the MATCHclause to retrieve all the Product nodes in the online_orders graph.

5. Creating Edges
In this step, we will create the ORDERS relationship (edge) to represent the relation between users and a product. Whenever a user wants to make an order, we will create this relationship between the user and the desired product(s). Additionally, we can add some properties to edges, just like we did with the nodes. In this example, we will add the current time and the quantity in the edge.

To make an order for Product 1 with a quantity of five for user1 that we created previously, execute the following query:

SELECT * FROM cypher('online_orders' , $$
    MATCH (u:User{name:'user1'}) , (p:Product{name:"Product 1"})
    CREATE((u)-[o:ORDERS{time:timestamp() , quantity:5}]->(p))
    RETURN u,o,p
$$) AS (u agtype, o agtype, p agtype);

After inserting the data, the AGE viewer can be used to visualize the graph and its relationships.

Based on the data shown in the AGE viewer, it appears that user1 has an ORDERSrelationship with Product 1.

If you want to display all the data in the graph you can run the following query:

You can run the following query to display all the data in the graph:

SELECT *
FROM cypher('online_orders', $$
    MATCH (u:User), (p:Product)
    OPTIONAL MATCH (u)-[o:ORDERS]->(p)
    RETURN u, o, p
$$) AS (u agtype, o agtype, p agtype);

Note that this will return all users, their orders, and the products they ordered. However, for larger graphs with many nodes and edges, this query can be slow and resource-intensive. It is recommended to use filters and limit the number of results returned when querying large graphs.

Note: This is a simple example for illustration purposes only. In a real-world scenario, there would be more complex data models and relationships between nodes and edges.

Great! You have created a simple graph using AGE and PostgreSQL. This was just a simple example to demonstrate the basic steps involved in creating and querying a graph. There are many other features and capabilities of AGE that you can explore to build more complex and sophisticated graphs to meet your specific requirements. We hope this tutorial has been helpful in getting you started with AGE and PostgreSQL.

References
AGE - Documentation

Contribute to Apache AGE
Apache AGE website: https://age.apache.org/
Apache AGE Github: https://github.com/apache/age

Step by step guide to install PostgreSQL + Apache AGE on Windows

Omar Saad — Sat, 08 Apr 2023 14:44:06 +0000

In this post, we will discuss how to install PostgreSQL and Apache AGE on a Windows machine in a step-by-step manner. By the end of this guide, you'll have a fully functional PostgreSQL database and Apache AGE graph database, ready to use for your data-driven applications.

What is PostgreSQL ?
PostgreSQL is one of the most famous relational databases in the world that is designed to manage and store large amounts of structured data.

PostgreSQL supports a wide range of data types, including text, numeric, and date/time data, as well as more complex data structures such as arrays, JSON, and XML.

What is Apache AGE ?
Apache AGE is a graph database system that is built on top of PostgreSQL. It allows users to store, manage, and analyze large-scale graph data in a highly performant and scalable way.

How to install PostgreSQL and Apache Age ?
Currently, Apache AGE does not support Windows. Therefore, in this tutorial, we will use Windows Subsystem for Linux (WSL) as an alternative.

1. Install WSL.
To enable WSL on your computer, follow these steps: First, go to the Control Panel, and then select Programs. Next, select Turn Windows features on or off. From the list of features, enable both Virtual Machine Platform and Windows Subsystem for Linux, as shown in the image

After enabling WSL, the next step is to install a Linux distribution for Windows. To do this, open the Microsoft Store and search for 'Linux.' You will see several options available, and you can choose the one you prefer. For the purposes of this tutorial, we will be using 'Ubuntu 22.04.2 LTS'.

2. Installing PostgreSQL from source code.
Please note that currently, Apache AGE only supports PostgreSQL versions 11 and 12, so we must install one of these versions. Before proceeding, we need to install some dependencies. To do this, open a bash terminal by navigating to any directory and typing 'bash' in the address bar, and then run the appropriate command.



sudo apt install git libreadline-dev zlib1g-dev bison flex build-essential

Next, create a directory where you want to install PostgreSQL and clone the Postgres repository into it.



mkdir postgres-AGE-project
cd postgres-AGE-project
git clone https://git.postgresql.org/git/postgresql.git

Navigate to the 'postgresql' directory and switch to a compatible version branch. For the purposes of this tutorial, we'll be using version 12.



cd postgresql
git checkout REL_12_STABLE

Compile the source code and specify the directory where you want the binaries to be installed. Note that this process may take some time to complete.



./configure –prefix=/usr/local/pgsql-12
make

After the compilation process is complete, run the following command to add permissions for the binaries directory and replace user by your username. Once this is done, proceed to install PostgreSQL



sudo mkdir /usr/local/pgsql-12
sudo chown user /usr/local/pgsql-12
make install

To ensure that PostgreSQL can be accessed from anywhere in the terminal, it is necessary to set up the following environment variables
These commands will add the PostgreSQL binaries directory to the system path and set the PGDATA directory to the location of the PostgreSQL data files.



export PATH=/usr/local/pgsql-12/bin/:$PATH
export PGDATA=/usr/local/pgsql-12/bin/data

Now that you have installed PostgreSQL, you can create a new cluster, which is a collection of databases managed by a single instance of the PostgreSQL server. To create a new cluster, run the following command:



initdb

After initializing the cluster, you can start the PostgreSQL server by running the following command:



pg_ctl -D /usr/local/pgsql-12/bin/data -l logfile start

Now you can start PostgreSQL by running the following command:



psql postgres

Congratulations! You have successfully installed and started PostgreSQL.

To exit PostgreSQL run \q

To stop the server run the following command:



pg_ctl -D /usr/local/pgsql-12/bin/data -l logfile stop

If you would like more detailed information about the installation process, you can visit the following link: PostgreSQL documentation

3. Installing Apache AGE from source code.
To install Apache AGE from source code, go to the main directory of the postgres-AGE-project that we created earlier and execute the following command to clone the AGE project.



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

To proceed with the installation, navigate to the project directory and switch to the latest stable release, which is version 1.1.0.



cd age
git checkout release/PG12/1.1.0

Set the environment variable PG_CONFIG. we need to set the PG_CONFIG environment variable to specify the location of the PostgreSQL pg_config utility. pg_config is a command-line utility that provides information about the installed version of PostgreSQL, including the location of files and libraries required for compiling and linking programs against PostgreSQL. Apache AGE uses pg_config to determine the location of the PostgreSQL header files and libraries needed to compile the AGE extension.



export PG_CONFIG=/usr/local/pgsql-12/bin/pg_config

Then install AGE extension by running this command:



make install

Now you have AGE installed to add the extension in your postgres server run the following SQL commands:



CREATE EXTENSION age;

Then every time you want to use AGE you must load the extension.



LOAD 'age';

Great job! You have successfully installed Apache AGE on your system.

You can find further information on AGE installation and usage on the official Apache AGE documentation at AGE documentation.

References
PostgreSQL
AGE - Installation giude

Contribute to Apache AGE
Apache AGE website: https://age.apache.org/
Apache AGE Github: https://github.com/apache/age