DEV Community: ERINFOLAMI PETER

I Got Tired of Building Chat From Scratch in Django, So I Made a Package

ERINFOLAMI PETER — Fri, 13 Mar 2026 04:44:10 +0000

Every few months I'd end up in the same situation. New project, new client, same request: "can we add a chat feature?"

And every time, I'd spend days wiring up Django Channels, designing room models, figuring out how to track delivery, handle permissions, support multiple devices... all before writing a single line of actual product code.

So I finally built something to fix that.

The gap I kept running into

Django Channels is great but it's low level. It gives you WebSocket support and channel layers. What it doesn't give you is:

Room management (and different room types: one-to-one, group, and broadcast)
Message delivery and read receipts
Reactions, replies, and forwards
Object-level permissions for admins and moderators
Multi-device session support
Notification tracking for offline users

Every project I worked on needed all of these. Every project built them differently. None of them were quite right.

What I built

django-realtime-chat-messaging is a drop-in Django package that gives you a full WebSocket chat system with one endpoint, a clean event protocol, and enough flexibility to customize without rewriting everything.

Connecting from the frontend looks like this:

const ws = new WebSocket("ws://localhost:8000/messaging/?token=YOUR_TOKEN");

ws.send(JSON.stringify({
  event_type: "message.send",
  data: {
    room_id: "your-room-uuid",
    content: "Finally, chat that just works."
  }
}));

One connection. Everything flows through it.

What's included

Out of the box you get three room types (OneToOneChat, GroupChat, Channel) backed by a polymorphic Room table, full message lifecycle (create, reply, forward, edit, soft/hard delete), read receipts, delivery tracking, reactions, multi-device sessions, and object-level permissions via django-guardian.

There's also a notification scaffolding layer. The package tracks unread messages per user and dispatches them when the user connects. You plug in your own push provider. The package stays out of that.

The part I spent the most time on

Everything is swappable. Models, serializers, event handlers, permission handlers, even the consumer class and the WebSocket URL path are all configurable via settings.

REALTIME_CHAT_MESSAGING = {
    "CHAT_CONSUMER_CLASS": "myapp.consumers.CustomChatConsumer",
    "EVENT_HANDLER_CLASS": "myapp.handlers.CustomEventHandler",
    "WEBSOCKET_PATH": "chat/",
}

You can use it with zero config and have something working in under an hour, or you can swap out specific pieces as your needs grow. Nothing requires you to fork the package.

This is v0.1.0

First public release. The core is solid but I'm sure there are rough edges I haven't hit yet.

If you're a Django developer who has built chat before, I'd really value your take on the design decisions. If you're newer to WebSockets and want a real project to learn from, this might be a good one to dig into.

PyPI: pip install django-realtime-chat-messaging
Docs
GitHub

Happy to answer questions in the comments.

Basic Ansible tutorial (Installation and setup)

ERINFOLAMI PETER — Mon, 31 Jul 2023 20:47:59 +0000

Introduction

In the last article we talked about the power of ansible, it's importance and why you should consider using it in your company or on your next big project if you haven't been using it. Ansible is a configuration management tool that streamlines the process of controlling large number of servers. Ansible is just one of the other configuration tool out there, some of which includes puppet, chef.
In this tutorial we’ll discuss how to install Ansible on your machine (for this tutorial we'll be using a linux environment, ubuntu) and go over some basics of how to use this software.

Prerequisites

Before installing ansible you must have An Ansible Control Node which is the machine we'll install ansible on to configure other servers. The machine should have a non-root user with sudo privileges.

Installation

To install on ubuntu machine add the project official's PPA

sudo apt-add-repository ppa:ansible/ansible

Then update

sudo apt update

Install ansible

sudo apt install ansible

You can also install ansible via python (This works for any OS).

Make sure you have python installed on your machine then run the following to install ansible

python3 -m pip install --user ansible

Use python for windows.

We've successfully installed ansible on our machine

Using ansible

Now let's use ansible!, one of the simplest way is first by creating an inventory file in your working directory.

touch inventory

in the inventory file, write the ip address or the domain name of the ansible hosts (machines you want to remotely access). For testing purposes you can input the ip address of your current machine with no space or just localhost.
NOTE: The ansible hosts(machines ansible want to run commands remotely) must accept ssh connection and also must have set up the required ssh key, if you are using your local machine as the ansible host for testing purposes, you might need to add the public key of your machine to authorized_keys so ssh via identity-file can work

Then Run

ceejay@ceejay:~/ceejay/ansible_practice$ ansible all --key-file ~/.ssh/id_rsa -i inventory -m ping

localhost | SUCCESS => {

    "ansible_facts": {

        "discovered_interpreter_python": "/usr/bin/python3"

    },

    "changed": false,

    "ping": "pong"

}

-i - accepts the inventory file.
-m - The ansible module to use. There a lot of ansible modules, you can check them here. The ping module is basically just to test if ansible is able to ssh into the server.

You can also create an ansible config file ansible.cfg to define the basic configurations. Create this file in your working directory, this would override the default config file in /etc/ansible.
in the ansible.cfg file copy paste the following

[defaults]

inventory = inventory

private_key_file = ~/.ssh/id_rsa

Save this file and run

ceejay@ceejay:~/ceejay/ansible_practice$ ansible all -m ping

localhost | SUCCESS => {

    "ansible_facts": {

        "discovered_interpreter_python": "/usr/bin/python3"

    },

    "changed": false,

    "ping": "pong"

}

This method makes it cleaner and easier to run.

We've just seen how to use ansible, in the coming article we'll explore more possibilities with ansible, there's so much more powerful stuffs you can do with ansible.

AUTOMATING WITH ANSIBLE (CONFIGURATION TOOL)

ERINFOLAMI PETER — Fri, 28 Jul 2023 18:55:54 +0000

Introduction

In the rapidly evolving world of IT infrastructure management, efficiency, scalability, and automation have become critical factors for organizations to stay competitive. Traditional manual processes for provisioning and managing servers, particularly in the context of multiple database servers and enterprise databases, are not only time-consuming but also error-prone, leading to potential downtimes and security risks. This is where ansible comes in, it's an open source automation tool which solves the aforementioned issues.

Ansible simplifies and streamlines the process of managing complex IT environments by providing a robust, agentless, and declarative approach to configuration management, application deployment, and task automation. It has gained widespread popularity among system administrators, developers, and DevOps teams for its ability to orchestrate and automate repetitive tasks, such as server provisioning and database deployment, across a wide range of platforms.

Perks of utilizing ansible.

1. Simplified Automation: Ansible allows it users to define infrastructure as code using a simple, human-readable language known as YAML. This approach makes automation more accessible to both developers and system administrators, enabling them to easily describe the desired state of their database servers and manage them efficiently. You simply just write code for what should have been manually "ssh-ing" into 10-100 of servers manually and configuring them.

2. Agentless Architecture: Unlike many other automation tools, Ansible follows an agentless architecture, which means it doesn't require any additional software to be installed on the target servers. This minimizes the management overhead and security concerns associated with agents and simplifies the setup process for database servers.

3. Idempotent Execution: Ansible executes tasks in an idempotent manner, ensuring that the same configuration is applied consistently, regardless of the server's current state. This feature eliminates configuration drift and ensures that databases are provisioned consistently across the entire infrastructure. But you might be wondering isn't that too rigid? well, ansible also offers you the flexibility of applying specific changes to specific servers, based on different criterias which may include server roles(web server, file server, workstations, etc..), underlying operating system(centOS, ubuntu, windows, etc..) , conditions (updated, changed, present, etc..), etc...

4. Reusability and Modularity: Ansible promotes the use of roles and playbooks, which are reusable and modular components that encapsulate specific configurations and tasks. This allows teams to standardize the provisioning process, share best practices, and enhance collaboration across different projects.

5. Integration with Existing Tools: Ansible integrates seamlessly with various configuration management tools, cloud providers, and other DevOps technologies. This adaptability enables organizations to leverage their existing investments and incorporate Ansible into their existing workflows.

6. Scalability and Flexibility: Whether you need to provision a handful of database servers or manage a large-scale enterprise database environment, Ansible can scale to meet your needs. Its agentless nature and parallel execution capabilities make it well-suited for handling distributed and complex infrastructures.

7. Community and Ecosystem: Ansible boasts a vibrant and active community that continuously contributes to the development of modules and playbooks. This extensive ecosystem provides access to pre-built automation solutions for various databases, making it easier for teams to get started and accelerate their automation efforts.

Conclusion

By just pressing the "Enter" key you can deploy applications on hundreds to thousands of servers within seconds to minutes with guaranteed consistency across these servers. As said earlier provisioning infrastructures just by writing code (IAC - Infrastructure As Code) has made deployment automation easier, faster, and scalable. In the coming article we'll see how to install ansible and using it at the bearest minimum.

INTRODUCTION TO POSTGRES DEPLOYMENT (edb-deployment) TOOL (PART 2)

ERINFOLAMI PETER — Sat, 15 Jul 2023 14:36:11 +0000

INTRODUCTION

In the last article we talked about the edb-deployment tool, what it does, how to install and setup. In this article we'll see how to use this tool to deploy our PostgreSQL/EDB Postgres Advanced Server.

TOOL USAGE

This article would assume you've installed this tool. To deploy your project using edb-deployment tool there are 5 steps:

1. Configuring your cloud credentials:

This configuration depends on the cloud vendor (aws, gcloud, azure) being used. First you make sure the cloud vendor cli tool has been installed, and if you already installed using the setup command from edb-deployment then you need to update the PATH environment variable using

$ export PATH=$PATH:$HOME/.edb-cloud-tools/bin

You might want to add this line of code in an config init file like .bashrc for linux users. Then you can go ahead and configure your credentials according to the cloud vendor you intend to use. For aws you use the following command aws configure. You can look up other cloud vendors way of configuring here. You might also need to be conversant with using this cloud vendor cli tool and their services to setup your credentials correctly.

2. Configuring your project:
After setting up your cloud credentials and you are logged in then you go on to configure your project. edb-deployment gives you default configuration values relative to your cloud vendor. Configuration values like instance type, disk size, OS image, additional volumes, etc. To change the default values from edb you'll need to dump those values in a json file because it's in json format then you can edit this file to fill in the parameters. Here's the command to run to dump the default values

edb-deployment <CLOUD_VENDOR> specs > my_configuration.json

like i said the configuration values differs based on your cloud vendor. And if you are deploying on bare-metal server (where you need to configure the ip address yourself and other configurations) use the following command

$ edb-deployment baremetal specs --reference-architecture EDB-RA-1 > baremetal-edb-ra-1.json

The reference architectures types would be talked about in a bit.
Then you can edit the configuration files to suit your need.

3. Initializing your project:
After successfully creating your configuration file, you'll need to create your project from which you'll then deploy. In the last article the different sub commands provided by the edb-deployment tool was iterated and we'll be using one of those subcommands to initialize a new project. The configure command is used to initialize a new project and here is how to use it with the necessary flags required to succesfully create the project

edb-deployment <CLOUD_VENDOR> configure <PROJECT_NAME> \
  -a <REFERENCE_ARCHITECTURE_CODE> \
  -o <OPERATING_SYSTEM> \
  -t <PG_ENGINE_TYPE> \
  -v <PG_VERSION> \
  -u "<EDB_REPO_USERNAME>:<EDB_REPO_PASSWORD>" \
  -r <CLOUD_REGION> \
  -s my_configuration.json

PROJECT_NAME: Your project name.

REFERENCE_ARCHITECTURE_CODE: Reference architecture code name. Here are the allowed values: EDB-RA-1 for a single Postgres node deployment with one backup server and one PEM monitoring server, EDB-RA-2 for a 3 Postgres nodes deployment with quorum base synchronous replication and automatic failover, one backup server and one PEM monitoring server, EDB-RA-3 for extending EDB-RA-2 with 3 PgPoolII nodes, and HammerDB-TPROC-C for setting up a 2-tier configuration for benchmarking with an OLTP workload. Default: EDB-RA-1

OPERATING_SYSTEM: Operating system. Allowed values are: CentOS7, RockyLinux8, RedHat7 and RedHat8. Default: RockyLinux8

PG_ENGINE_TYPE: Postgres engine type. Allowed values are: PG for PostgreSQL, EPAS for EDB Postgres Advanced Server. Default: PG

PG_VERSION: PostgreSQL or EPAS version. Allowed values are: 11, 12, 13 and 14. Default: 14

"EDB_REPO_USERNAME:EDB_REPO_PASSWORD": EDB Packages repository credentials. (The edb package project in which you wish to deploy repo name and password). It is Required.

CLOUD_REGION: Cloud vendor region. Default value depends on Cloud vendor.

You can use the help command to see the full options

edb-deployment <CLOUD_VENDOR> configure --help

4. Provisioning the Cloud Resource:
Before deploying the project you'll need to provision the cloud resources, which is seemingly intuitive that when you want to deploy your project on any cloud provider, you'll need to first provision the servers and other resources(volumes, etc..) before deploying your project. Same functionality is provided with the edb-deployment tool, you can provision your resources using the following command.

$ edb-deployment <CLOUD_VENDOR> provision <PROJECT_NAME>

5. Deployment:

To deploy the project, use the deploy subcommand

$ edb-deployment <CLOUD_VENDOR> deploy <PROJECT_NAME>

CONCLUSION

We've seen how to deploy edb projects using the edb-deployment tool, Here is the official repo to the project feel free to visit, read more and raise issues if you encounter any!.

INTRODUCTION TO POSTGRES DEPLOYMENT (edb-deployment) TOOL (PART 1)

ERINFOLAMI PETER — Fri, 14 Jul 2023 14:47:22 +0000

INTRODUCTION

You might be wondering what is edb-deployment, well edb-deployment is a tool that gives you the superpower of provisioning cloud resources (like instances, bare-metal servers) in order to deploy your PostgreSQL servers and other tools for monitoring, backup/recovery, high availability etc... on popular cloud providers. The mainly supported cloud providers are AWS, Azure, GCP. It also allows you deploy reference architectures which is reliable and saves you the time and headache of designing a suitable architecture for your resources. Some of the reference architectures supported by edb can be found here. Edb-deployment pretty much does the heavy-lifting for you by just typing in the right commands on your command line.

PRE-REQUISITE

edb-deployment is a tool built on python, so to use this tool you need to have python3 and pip3 installed. You also need to install the cloud vendor cli tool(aws cli, azure cli, google cloud sdk) you intend to deploy on and any other additional deployment tools such as teraform, ansible. The basic pre-requisites here are python3 and pip3, you can pretty much install the other cli tools by running a setup command which we'll see in a bit.

HOW TO INSTALL

You can easily install through pip from pypi using the following command

$ pip3 install edb-deployment

And on a linux device make sure to use the sudo privileges (preferable)

$ sudo pip3 install edb-deployment

And you can also install direcly from the source code, but make sure you have git installed on your machine. To install run the following (assuming you already have github ssh setup)

$ git clone git@github.com:EnterpriseDB/postgres-deployment.git
$ cd postgres-deployment
$ sudo pip3 install . --upgrade

To confirm installation you can run the following command

$ edb-deployment --version

You can also enable command auto completion (this functionality allows you to type commands halfway and complete them with the tab key). This functionality of edb-deployment tool only works for linux shells(bash, ksh). Run the command to enable auto-completion

$ eval "$(register-python-argcomplete edb-deployment)"

After installation you might need to install cloud cli tool in which you intend on deploy with, for example if you need to deploy in aws you'll need the aws cli tool installed. To install the specific cli tool using edb make sure to have to following installed:
gcc (Linux only)
python3-devel (Linux only)
unzip
wget
tar
These packages should be installed through usual package manager (dnf, apt, brew, etc..). Also for debian machines install libffi-dev.
Having met the requirements you also need to install virtualenv(if not already installed) from pip, to allow creation of virtual environments.

$ sudo pip3 install virtualenv

Then run the following to install the necessary cloud vendor cli tools

edb-deployment <CLOUD_VENDOR> setup

CLOUD_VENDOR: aws, terraform, ansible, gcloud, azure

USAGE

Basic usage of this tool is straight forward. The format is

$ edb-deployment <CLOUD_VENDOR> <SUB_COMMAND> [<PROJECT_NAME>]

The supported cloud vendor are:
aws: Amazon Web Services
aws-pot: EDB POT (Proof Of Technology) on AWS Cloud
aws-rds: Amazon Web Services RDS for PostgreSQL
aws-rds-aurora: Amazon Aurora
azure: Microsoft Azure Cloud
azure-pot: EDB POT (Proof Of Technology) on Azure Cloud
azure-db: Microsoft Azure Database
gcloud: Google Cloud
gcloud-pot: EDB POT (Proof Of Technology) on Google Cloud
gcloud-sql: Google Cloud SQL for PostgreSQL

The valid sub commands you can use includes:

configure: New project initialization and configuration
provision: Cloud resources provisioning
destroy: Cloud resources destruction
deploy: Postgres and tools deployment
show: Show configuration
display: Display project inventory
passwords: Display project passwords
list: List projects
specs: Show Cloud Vendor default specifications
logs: Show project logs
remove: Remove project

CONCLUSION

With this basic installation and setup we can start provisioning servers and deploying our resources using different architectures on the cloud. In the next parts I'll walk you through on how to use this tool to deploy on cloud providers(e.g aws) with the basic syntax we've seen in this article.

APACHE AGE: Getting Started Part 4(AGE Query Formats)

ERINFOLAMI PETER — Mon, 26 Jun 2023 19:32:02 +0000

Introduction

We've seen the various ways we can install the apache age extension on our local machine. Now we need to understand the query format used in age, how to create a graph and delete a graph.

Query Format

AGE uses the format cypher(graph_name, query_string, parameters). The cypher() function is a function in the ag_catalog (ag_catalog is a schema that is loaded when you LOAD 'age') that returns a postgres SETOF records. As seen, the cypher() function takes in 3 arguments:

graph_name: Basically the name of the graph in the database (that has already been created) that you want to work on. We'll see how to create a graph and delete a graph in a minute
query_string: This takes a string of queries that allows you to perform operations on the graph. This string of queries is a special type of language called Cypher Query Language.
parameters: An optional map of parameters used for Prepared Statements. Default is NULL.

Creating and Deleting graphs

To use a graph_name in the cypher() function we talked about above we need to first create a graph. Creating a graph simply creates a schema in the database in which all the graph objects(vertices, edges, labels etc...) are stored.

To create a graph we use the create_graph() function from the ag_catalog schema/namespace which takes an argument which is the graph_name, this creates a schema with the graph_name and also some boiler plates objects in the created schema. Deleting a graph simply requires using the function drop_graph() also defined in the ag_catalog, which takes 2 arguments: graph_name, cascade.
The cascade argument takes a boolean value of either true or false on whether to delete labels and data that depends on the graph. According to the docs it's recommended to keep this as true.

Now let's create our first graph.

1. Listing out the schemas/namespace present in the database:

postgresDB=# SELECT schema_name

postgresDB-# FROM information_schema.schemata;

    schema_name     

--------------------

 ag_catalog

 information_schema

 public

 pg_catalog

 pg_toast_temp_1

 pg_temp_1

 pg_toast

(7 rows)

Here we see ag_catalog is present, ag_catalog is the namespace in which all the main age functions are stored. So to use any of the above function we'll need to make sure ag_catalog is present.

2. Creating a graph

To create a graph test_graph we'll run the query SELECT * FROM ag_catalog.create_graph('test_graph')

postgresDB=# SELECT * FROM ag_catalog.create_graph('test_graph');

NOTICE:  graph "test_graph" has been created

 create_graph 

--------------



(1 row)



postgresDB=#

Remember i said creating graph creates a namespace to store all the objects peculiar to the graph. To check if the graph was created we'll list our current shemas again.

postgresDB=# SELECT schema_name                           

FROM information_schema.schemata;

    schema_name     

--------------------

 test_graph

 ag_catalog

 information_schema

 public

 pg_catalog

 pg_toast_temp_1

 pg_temp_1

 pg_toast

(8 rows)



postgresDB=#

Now we see that our graph has been created.

3. Deleting a graph

Just like i said earlier, deleting a graph is as easy as simply calling the drop_graph() function. But we'll be passing in the true value for our second argument (cascade).

postgresDB=# SELECT * FROM ag_catalog.drop_graph('test_graph', true);

NOTICE:  drop cascades to 2 other objects

DETAIL:  drop cascades to table test_graph._ag_label_vertex

drop cascades to table test_graph._ag_label_edge

NOTICE:  graph "test_graph" has been dropped

 drop_graph 

------------



(1 row)



postgresDB=#

Now we've deleted the test_graph, notice how there are also other two objects deleted as a result of setting cascade to true, if we didn't set it to true, we'll have to manually delete those objects.

Going back to our query format, the second arguments which is the query_string is usually embedded in a "$$" delimeter which is used to signify the beginning and end of the query. The last argument we won't worry to much about for now.

The basic query format goes like this

SELECT * FROM ag_catalog.cypher('graph_name', $$
 "cypher query goes here"
$$) as (result agtype);

We'll talk more about agtype and cypher query language in the coming articles in this series

Now let's put all we've learned together and create a graph, run a simple query to return "hello world" on the graph and also delete the graph

postgresDB=# SELECT * FROM ag_catalog.create_graph('test_graph');

NOTICE:  graph "test_graph" has been created

 create_graph 

--------------



(1 row)

postgresDB=# SELECT * FROM ag_catalog.cypher('test_graph', $$

RETURN "Hello World"

$$) as (result ag_catalog.agtype);

    result     

---------------

 "Hello World"

(1 row)



postgresDB=# SELECT * FROM ag_catalog.drop_graph('test_graph', true);

NOTICE:  drop cascades to 2 other objects

DETAIL:  drop cascades to table test_graph._ag_label_vertex

drop cascades to table test_graph._ag_label_edge

NOTICE:  graph "test_graph" has been dropped

 drop_graph 

------------



(1 row)



postgresDB=#

appending ag_catalog to every age function all the time can be pretty daunting, we can set the search_path to include ag_catalog, so by default it looks in the ag_catalog when a function is called.

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

SET

postgresDB=# SELECT * FROM create_graph('test_graph');

NOTICE:  graph "test_graph" has been created

 create_graph 

--------------



(1 row)



postgresDB=# SELECT * FROM cypher('test_graph', $$

RETURN "Hello World"

$$) as (result agtype);

    result     

---------------

 "Hello World"

(1 row)



postgresDB=# SELECT * FROM drop_graph('test_graph', true);

This way is easier and cleaner.

Conclusion

We've been able to see the basic query format in age including how to create a graph, what happens when you create a graph, running a basic query in the graph and how to delete a graph. It's important to note that while providing the graph_name in any of the functions we've talked about today, graph name must be put in single quotes, double quotes might throw an error. In the coming articles we'll talk about different datatypes and cypher queries.

APACHE AGE: Getting Started Part 3(AGE INSTALLATION VIA DOCKER)

ERINFOLAMI PETER — Wed, 07 Jun 2023 20:50:07 +0000

Introduction

In the last article from the apache age series, we've been able to successfully install the apache age extension through installation directly from the source code on github. But you can also use the extension on your local machine through docker, this way you get to isolate the extension only on the docker container you spin up, and also wouldn't have to bother about barely installing a lot of dependencies on your local machine.

Pre-requisite

The pre-requisites remain the same which is having a compatible version(11) of postgreSQL installed (check here for guide) and also having docker installed on your local machine.

Note: It's recommended to use a linux environment.

Setting up and Installation

To install the age extension via docker is very easy. Once you have your pre-requisites installed follow this steps:

1. Pull the apache/age image from docker repository

docker pull apache/age

This may take several minutes, based on your internet connectivity.

2. Run the apache/age container

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

You can change the following names (myPostgresDb, postgresUser, postgresPW, postgresDB) to suit your preference but make sure you remember them.

3. Confirm the container is running
After running the command in the previous steps leaving all the arguments as they are. I can now confirm my docker container is running

ceejay@ceejay:~$ docker ps

CONTAINER ID   IMAGE        COMMAND                  CREATED         STATUS         PORTS                                       NAMES

6ea0a9f55c3e   apache/age   "docker-entrypoint.s…"   6 seconds ago   Up 4 seconds   0.0.0.0:5455->5432/tcp, :::5455->5432/tcp   myPostgresDb

ceejay@ceejay:~$

4. Connect to the postgres cli running on the container

ceejay@ceejay:~$ docker exec -it myPostgresDb bash

root@6ea0a9f55c3e:/# psql -d postgresDB -U postgresUser

psql (11.19 (Debian 11.19-1.pgdg100+1), server 11.13 (Debian 11.13-1.pgdg100+1))

Type "help" for help.



postgresDB=#

The docker exec connects to the running container, and the psql command connects to the postgres cli running on the container, the -d flag allows you to specify the database name, and the -U flags allows you to specify the username.
note: Remember to fill in the arguments according to what you set previously.

5. Load the Age extension
Now that we've successfully connected to the psql cli, we can now load in the age extension and also adding ag_catalog to search_path

postgresDB=# LOAD 'age';

LOAD

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

SET

postgresDB=#

Now we can start creating our graphs and running cypher queries.

Conclusion

With the aid of docker you can run the age extension in isolation, anytime you need to access the extension all you need to do is connect to the postgres cli on the container as shown earlier and run your queries. See you in the next episode!.

PostgreSQL Tutorial: Creating Tables, Inserting and Basic Querying.

ERINFOLAMI PETER — Sat, 29 Apr 2023 13:37:12 +0000

Introduction

PostgreSQL is an open-source object-relational database management system that provides a reliable, scalable, and secure platform for storing and managing structured data. PostgreSQL is known for its robustness, flexibility, and extensibility, which make it a popular choice for a wide range of applications, from small-scale web applications to large-scale enterprise systems. It supports a variety of data types, including numeric, character, date/time, and binary data, and offers advanced features such as support for transaction processing, multi-version concurrency control, and nested transactions.

In this article i'll be demonstrating how you can create databases, and also creating tables in this database, as well as querying the table for data. To get started we need to login into the postgres server.

1. Connecting the server

ceejay@ceejay:~$ psql

psql (12.14 (Ubuntu 12.14-1.pgdg22.04+1))

Type "help" for help.



ceejay=#

In case the above command doesn't work for you run the following command
sudo -u postgres psql. Then go ahead and create a user and grant the user required privileges.
You can follow the guide in this article to create a new user https://phoenixnap.com/kb/postgres-create-user

2. Creating a database.

Currently these are the databases available. (Yours might be different)

ceejay=# \l

                             List of databases

   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   

-----------+----------+----------+---------+-------+-----------------------

 ceejay    | ceejay   | UTF8     | en_NG   | en_NG | 

 postgres  | postgres | UTF8     | en_NG   | en_NG | 

 template0 | postgres | UTF8     | en_NG   | en_NG | =c/postgres          +

           |          |          |         |       | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_NG   | en_NG | =c/postgres          +

           |          |          |         |       | postgres=CTc/postgres

 test      | postgres | UTF8     | en_NG   | en_NG | 

(5 rows)

Now let's create a new database named Record

ceejay=# CREATE DATABASE Record;

CREATE DATABASE

ceejay=# \l

                             List of databases

   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   

-----------+----------+----------+---------+-------+-----------------------

 ceejay    | ceejay   | UTF8     | en_NG   | en_NG | 

 postgres  | postgres | UTF8     | en_NG   | en_NG | 

 record    | ceejay   | UTF8     | en_NG   | en_NG | 

 template0 | postgres | UTF8     | en_NG   | en_NG | =c/postgres          +

           |          |          |         |       | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_NG   | en_NG | =c/postgres          +

           |          |          |         |       | postgres=CTc/postgres

 test      | postgres | UTF8     | en_NG   | en_NG | 

(6 rows)

Now we've created a database. Next we connect to the database.

3. Connect to the database

ceejay=# \connect record

You are now connected to database "record" as user "ceejay".

record=#

We just connected to the record database. But before we start creating tables in postgresql databases there's what we call schemas.

In PostgreSQL, a schema is a named logical container for database objects such as tables, indexes, views, and functions. Schemas are a way to organize and group database objects, and can be used to separate different applications or parts of an application that use the same database.

Each schema in a PostgreSQL database is a namespace that contains a collection of database objects. Schemas allow you to logically separate database objects and organize them in a hierarchical structure, which makes it easier to manage the database and avoid naming conflicts between different objects.

By default, PostgreSQL creates a schema named public when you create a new database. However, you can create additional schemas as needed, and assign database objects to different schemas to organize them according to their purpose or ownership. This can help to improve security, as you can grant or revoke privileges on a per-schema basis, and control access to specific parts of the database.

Maybe for personal practice it's not really important to create a personalized schema for your objects(Tables, functions, triggers...), But it's a good practice to always group your objects into schemas for production database. To check the schemas present in your current database.

record=# \dn

  List of schemas

  Name  |  Owner   

--------+----------

 public | postgres

(1 row)

As said earlier the default schema for newly created databases is the public schema. So if we don't specify a schema it just creates them in the public namespace. For the sake of demonstration we'll be using the public schema. But if you need to create a schema and create let's say a table in that schema you can just work with the following commands in psql.

create the schema.

CREATE SCHEMA <schema_name>;

After creating the switch to the schema using

SET search_path TO <schema_name>;

Then you can create tables.

4. Create Tables
Let's create a table user_table

record=# CREATE TABLE user_table(

record(# id SERIAL PRIMARY KEY,

record(# first_name VARCHAR(20) NOT NULL,

record(# last_name VARCHAR(20) NOT NULL,

record(# age INTEGER NOT NULL,

record(# married BOOLEAN NOT NULL DEFAULT FALSE

record(# );

CREATE TABLE

record=#

We start by using the command CREATE TABLE to create a table. The command is case insensitive (i.e you can use create table)
We the define the various fields which would serve as the column heading for the user_table. The line id SERIAL PRIMARY KEY simply means define a field name id with data type of SERIAL and a primary key constraint. There are different data types in postgreSQL you can read more on the official documentation here

The Serial datatype is an integer type(numbers) only that it'll auto-increment for every entry into the database. So you don't have to manually set it. The Primary Key constraint means this is going to be a unique field used to uniquely identify a row in the table.

VARCHAR is simply for variable length character, and we set the max length for both the first name and last name to be 20. The NOT NULL is a flag that simply means the field cannot be empty, which means when we add a new row in the table we cannot omit this field, there must be a set value.
We can also have default values as seen in the married field.

There many more datatypes and constraints that you can use on tables, (e.g FOREIGN KEY, UNIQUE etc...) we won't go deep into those yet.

Now that we've created a table let's insert data into the created table.

5. Insert Rows into the table
Let's insert few rows into our table.

-> Inserting one row

record=# INSERT INTO user_table (first_name, last_name, age, married) VALUES ('John', 'Doe', 23, TRUE);

INSERT 0 1

-> We can insert multiple rows

record=# INSERT INTO user_table (first_name, last_name, age, married) VALUES ('chris', 'hemsworth', 32, TRUE), ('tom', 'hanks', 52, TRUE), ('Tom', 'holland', 23, FALSE), ('Jason', 'bourne', 33, FALSE);

INSERT 0 4

record=#

-> Inserting while neglecting the married field (we allow postgres to set the default value).

record=# INSERT INTO user_table (first_name, last_name, age) VALUES ('johnny', 'depp', 42);

INSERT 0 1

record=#

Now that we have some rows in our user_table how do we get the rows ?

6. Basic Database Querying

-> To show all the fields of the rows inserted

record=# INSERT INTO user_table (first_name, last_name, age) VALUES ('johnny', 'depp', 42);

INSERT 0 1

record=# SELECT * FROM user_table;

 id | first_name | last_name | age | married 

----+------------+-----------+-----+---------

  1 | John       | Doe       |  23 | t

  2 | chris      | hemsworth |  32 | t

  3 | tom        | hanks     |  52 | t

  4 | Tom        | holland   |  23 | f

  5 | Jason      | bourne    |  33 | f

  6 | johnny     | depp      |  42 | f

(6 rows)



record=#

Notice the how we didn't need to manually set the id field while inserting and yet still present in the table.

-> Get just the first_name and last_name

record=# SELECT first_name, last_name FROM user_table;

 first_name | last_name 

------------+-----------

 John       | Doe

 chris      | hemsworth

 tom        | hanks

 Tom        | holland

 Jason      | bourne

 johnny     | depp

(6 rows)



record=#

-> Changing the column header names to "FIRST NAME" and "LAST NAME"

record=# SELECT first_name AS "FIRST NAME", last_name AS "LAST NAME" FROM user_table;

 FIRST NAME | LAST NAME 

------------+-----------

 John       | Doe

 chris      | hemsworth

 tom        | hanks

 Tom        | holland

 Jason      | bourne

 johnny     | depp

(6 rows)



record=#

-> Get the first 3 results

record=# SELECT first_name AS "FIRST NAME", last_name AS "LAST NAME" FROM user_table LIMIT 3;

 FIRST NAME | LAST NAME 

------------+-----------

 John       | Doe

 chris      | hemsworth

 tom        | hanks

(3 rows)



record=#

-> Get rows where the first name is tom

record=# SELECT first_name AS "FIRST NAME", last_name AS "LAST NAME" FROM user_table WHERE first_name LIKE 'tom';

 FIRST NAME | LAST NAME 

------------+-----------

 tom        | hanks

(1 row)



record=# SELECT first_name AS "FIRST NAME", last_name AS "LAST NAME" FROM user_table WHERE first_name ILIKE 'tom';

 FIRST NAME | LAST NAME 

------------+-----------

 tom        | hanks

 Tom        | holland

(2 rows)



record=#

The first query is case sensitive while the second is insensitive to the casing of the text.

-> We can also order our outputs based on a different column, default is using the id column, let's change this to the first_name.

record=# SELECT * FROM user_table ORDER BY first_name;

 id | first_name | last_name | age | married 

----+------------+-----------+-----+---------

  2 | chris      | hemsworth |  32 | t

  5 | Jason      | bourne    |  33 | f

  1 | John       | Doe       |  23 | t

  6 | johnny     | depp      |  42 | f

  3 | tom        | hanks     |  52 | t

  4 | Tom        | holland   |  23 | f

(6 rows)



record=#

Conclusion

Creating, inserting, and querying a database can be as easy as those shown in this article and but can also get really complicated. But understanding the basics is the first step into becoming a pro at constructing a sophisticated table structure. Learn more about the different constraints, different table relationships, then delving more into how to make complex queries, aggregations, regex etc.

APACHE AGE: Getting Started Part 2(AGE Installation)

ERINFOLAMI PETER — Sat, 22 Apr 2023 18:15:09 +0000

Introduction

In this part of the series I'll be walking you through how to install the age extension on top of the running instance of your postgres database. The pre-requisites to this installation is having postgres installed on your machine check here for guide, and also you should have git installed.

Setting up and installation

Like i said in the previous article we'll be working with postgresql version 12, so we'll also try to install the version of AGE that's compatible with postgres 12. Before we proceed to the installation there are various methods the age extension can be installed, first is via the github repo, second is downloading the source code directly from the release page, third is through the use of docker. We'll be installing via github.

1. To get started we'll first clone the official apache github repository.

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

2. After cloning successfully cd into the directory and change into the PG12 release branch

cd age
git checkout release/PG12/1.1.1

3. Run pg_config

ceejay@ceejay:~$ pg_config

BINDIR = /usr/lib/postgresql/12/bin

DOCDIR = /usr/share/doc/postgresql-doc-12

HTMLDIR = /usr/share/doc/postgresql-doc-12

INCLUDEDIR = /usr/include/postgresql

PKGINCLUDEDIR = /usr/include/postgresql

INCLUDEDIR-SERVER = /usr/include/postgresql/12/server

LIBDIR = /usr/lib/x86_64-linux-gnu

PKGLIBDIR = /usr/lib/postgresql/12/lib

LOCALEDIR = /usr/share/locale

MANDIR = /usr/share/postgresql/12/man

SHAREDIR = /usr/share/postgresql/12

SYSCONFDIR = /etc/postgresql-common

PGXS = /usr/lib/postgresql/12/lib/pgxs/src/makefiles/pgxs.mk

CONFIGURE = '--build=x86_64-linux-gnu' '--prefix=/usr' '--includedir=/usr/include' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--disable-option-checking' '--disable-silent-rules' '--libdir=/usr/lib/x86_64-linux-gnu' '--runstatedir=/run' '--disable-maintainer-mode' '--disable-dependency-tracking' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt' '--mandir=/usr/share/postgresql/12/man' '--docdir=/usr/share/doc/postgresql-doc-12' '--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' '--datadir=/usr/share/postgresql/12' '--bindir=/usr/lib/postgresql/12/bin' '--libdir=/usr/lib/x86_64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/' '--with-extra-version= (Ubuntu 12.14-1.pgdg22.04+1)' '--enable-nls' '--enable-thread-safety' '--enable-debug' '--enable-dtrace' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-gssapi' '--with-ldap' '--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' 'AWK=mawk' 'MKDIR_P=/bin/mkdir -p' 'PROVE=/usr/bin/prove' 'PYTHON=/usr/bin/python3' 'TAR=/bin/tar' 'XSLTPROC=xsltproc --nonet' 'CFLAGS=-g -O2 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer' 'LDFLAGS=-Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects -flto=auto -Wl,-z,relro -Wl,-z,now' '--enable-tap-tests' '--with-icu' '--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-14' 'CLANG=/usr/bin/clang-14' '--with-systemd' '--with-selinux' 'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security'

CC = gcc

CPPFLAGS = -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2

CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer

CFLAGS_SL = -fPIC

LDFLAGS = -Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects -flto=auto -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-14/lib -Wl,--as-needed

LDFLAGS_EX = 

LDFLAGS_SL = 

LIBS = -lpgcommon -lpgport -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -ledit -lcrypt -lm 

VERSION = PostgreSQL 12.14 (Ubuntu 12.14-1.pgdg22.04+1)

Note: if you get an error that command not found then you should add your postgres installation directory to PATH. If you followed my previous article the default location is /usr/lib/postgresql/12/. You can add to path by using the following command export PATH="/usr/lib/postgresql/12/bin:$PATH"

4. Add the PG_CONFIG environment variable and install

export PG_CONFIG="/usr/lib/postgresql/12/bin/pg_config"
sudo make install

You can also combine the two above into one command

sudo make PG_CONFIG="/usr/lib/postgresql/12/bin/pg_config" install

This might take a while

After installation connect to a database on your postgres server. We created a db test in last article. we can connect to it

ceejay@ceejay:~/age$ psql test

psql (12.14 (Ubuntu 12.14-1.pgdg22.04+1))

Type "help" for help.



test=#

We can then create and load the extension into our database

test=# CREATE EXTENSION age;

CREATE EXTENSION

test=# LOAD 'age';

LOAD

test=#

We then add the ag_catalog to search_path, to simplify our queries

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

SET

test=#

You can check the list of extensions installed with the following

test=# \dx

                 List of installed extensions

  Name   | Version |   Schema   |         Description          

---------+---------+------------+------------------------------

 age     | 1.1.1   | ag_catalog | AGE database extension

 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

(2 rows)



test=#

Conclusion

Now we've been able to successfully install the apache age extension on our postgres database. In the next parts of the series, we'll be playing with the graph extension itself and see the cool and powerful stuffs we can do with it. if you run into errors or issues while installing let me know in the comment section. See you in the next episode!.

APACHE AGE: Getting Started Part 1(PosgreSQL Installation)

ERINFOLAMI PETER — Fri, 14 Apr 2023 09:06:01 +0000

Introduction

In this article i'll be walking you through the installation of the Apache AGE PostgreSQL extension and then in my upcoming articles we'll try out some cool stuffs with the extension together. But before we go into the installation process I'd assume you know what a database is? and also you know what SQL is? and finally you know about PostgreSQL. If you don't, you can get started with some of these:
Quick introduction to SQL
Getting started with PostgreSQL

What then is Apache AGE?

Apache AGE is an open-source graph database management system that provides a powerful and scalable solution for storing, managing and analyzing large-scale graph data. It is built as an extension of PostgreSQL and supports complex graph queries and provides fast query response times, making it an excellent choice over traditional relational database management systems. With Apache AGE, users can gain deeper insights into data and make more informed decisions. So we can simply say Apache AGE gives you the super power of expressing your database graphically.

Cool? 😉

Before we get to play around with it let's get started with installing it on our local machine.

Setting up

Like i said earlier Apache AGE is a PostgreSQL extension so to use it we need to get PostgreSQL installed on your machine. Apache AGE is only compatible with 3 versions of PostgreSQL (11, 12, 13(only compatible with the newest release)). For this article series we'll be working with the version 11/12, we'll talk about the new Apache AGE release that's compatible with the PostgreSQL 13 later when we are comfortable with the previous versions. Before we start installing postgres we'll need to install some dependencies on our machine for Apache AGE to work on your local machine. It is important to know that Apache AGE would only work on a Linux Operating System distribution. If you use Windows OS consider installing a virtual machine or try out

wsl

Run the following command to install the aforementioned dependencies

Ubuntu

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

Fedora

dnf install gcc glibc bison flex readline readline-devel zlib zlib-devel

Centos

yum install gcc glibc glib-common readline readline-devel zlib zlib-devel flex bison

Now to installing PostgreSQL.
For demonstration I'll be working with PostgreSQL 12, there are more than one ways to install postgres on your computer, one way is by compiling directly from the source code, you can go through this guide to try that.
But I'll be installing via the ubuntu postgresql apt repository

Note:
This only works on an ubuntu machine (Linux) to get it working on other operating systems or distributions you might want to check out the official documentation for your OS and distributions here

for ubuntu >= 18.04:
Run the following code:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql-12

After installation (if you get any errors let me know in the comments), you need to add the bin directory path to the PATH env variable if not already added. By default the installation path is /usr/lib/postgresql/<version> for version 12 it'll be /usr/lib/postgresql/12/

To add to PATH you can run export PATH="/usr/lib/postgresql/12/bin/:$PATH"

You might want to consider adding that to your .bash_profile or .bash_rc.

Also make sure you confirm that postgres service is running sudo systemctl status postgresql if it's inactive or not running, you can run sudo systemctl start postgresql to start it up.

To login to your postgres database for the first time, you need to use the default admin user created by postgres. So to go into postgres you need to run sudo -u postgres psql

ceejay@ceejay:~$ sudo -u postgres psql

psql (12.14 (Ubuntu 12.14-1.pgdg22.04+1))

Type "help" for help.



postgres=# \conninfo

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

postgres=#

Let's go ahead and create a database
sudo -u postgres createdb <dbname>

We can also create a user
sudo -u postgres createuser <username>

Create a password for the user

ceejay@ceejay:~$ sudo -u postgres psql

psql (12.14 (Ubuntu 12.14-1.pgdg22.04+1))

Type "help" for help.

postgres-# \password ceejay

Enter new password for user "ceejay": 

Enter it again: 

postgres-#

For me the user created earlier is ceejay so for you change it to your the user you created earlier.

Let's check if our database was actually created.

postgres-# \l

                             List of databases

   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   

-----------+----------+----------+---------+-------+-----------------------

 postgres  | postgres | UTF8     | en_NG   | en_NG | 

 template0 | postgres | UTF8     | en_NG   | en_NG | =c/postgres          +

           |          |          |         |       | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_NG   | en_NG | =c/postgres          +

           |          |          |         |       | postgres=CTc/postgres

 test      | postgres | UTF8     | en_NG   | en_NG | 

(4 rows)

Conclusion

You can do a lot more with postgresql, consider reading the documentation to learn more, for example you can try creating another database with the newly created user, noticed from above we used the default admin postgres to do that with the sudo -u postgres createdb <db_name> line, i was able to create it because the admin has all the privileges to do so, so you can consider granting the new user the privileges to create a new database. In the next article of this series we'll go ahead and install the Apache AGE extension on top of our postgres. If you encountered any errors or have any questions leave them in the comment section below.
Next we'll be installing the AGE extension.