DEV Community: Nnaemeka Daniel John

Building BioDetect: Turning a Simple React App into a 3D Butterfly Experience with WebGi

Nnaemeka Daniel John — Sat, 28 Sep 2024 20:25:42 +0000

BioDetect: Bringing the Natural World Closer to You!

The initial version of the app

It all started as a simple, personal project, a little idea I had when Google released the Gemini API. I’m that guy who stares at every leaf, butterfly, and flower but has zero clue about what I'm looking at. So, I thought: “Why not build an app that can identify plants and animals with just a photo?”

That’s how BioDetect was born, a fun side project to help satisfy my curiosity about the natural world. I initially built it as an API bare-bones tool: just make an POST request with a picture, and boom, you'd get some basic information in JSON format. I wasn’t planning on making it super fancy, just a backend-focused tool because that’s my domain of expertise as a backend engineer.

Entering the Google Gemini Developer Competition

Months later, while browsing online, I stumbled upon the Google Gemini Developer Competition. I thought, “Why not give BioDetect a shot?” It had potential, but it needed to go through a serious glow-up. It had to evolve from a cool personal project to something... extraordinary.

I began rethinking the entire experience. How could I make it more engaging? More polished? More creative?

Iterating and Simplifying: The Minimalist Approach

First off, I decided to revamped the front end to be minimalistic, yet intuitive. A clean interface makes a world of difference. The goal was to keep the experience simple but immersive, so I kept distractions to a minimum and let the information stand out.

The Butterfly Moment: Adding 3D Model Using WebGi

But then came the real game-changer: a 3D animated butterfly. I thought, why not add a visual element that captures the essence of nature’s beauty? I wanted users to feel like they were part of an enchanting world as they scrolled through the app. So I found an incredible 3D butterfly model by TPackard on Sketchfab.

That’s when I dipped my toes into WebGi, a powerful library that allows for interactive 3D models. As a backend engineer, this was a new frontier for me, but I loved the challenge. Creating and integrating the 3D butterfly, which flutters beautifully across the screen as you scroll, was no small feat. I had to really dig into frontend technologies and push myself beyond my usual comfort zone.

Code Snippet for animations on scroll

The React frontend was my canvas, and WebGi was my paintbrush. I spent countless nights experimenting, tweaking, and polishing to make the 3D butterfly animate seamlessly on scroll. In the end, I think it was totally worth it. The butterfly adds such a unique and interactive flair, making BioDetect not just functional but delightful.

The finished product

The Tech Behind BioDetect

Here’s the breakdown of how BioDetect works:

Python FastAPI Backend: This is the core of the app. When a user uploads an image, it gets processed by FastAPI. The backend sends the image to Google’s Gemini-1.5-Flash API, which analyzes it and returns a detailed description.
Data Parsing: The API response is packed with rich insights, so I wrote logic to parse out the key details like species, characteristics, and habitat. This keeps the user-facing data concise yet informative.
React Frontend: The frontend is built with React, designed to showcase the image and information in a clean, digestible format. I used React Hooks to manage the state of the application, ensuring a smooth and dynamic user experience.
3D Animation with WebGi: I wanted to go beyond static information, so I integrated a 3D butterfly animation using WebGi. As users scroll through the page, the butterfly interacts with the content, creating a captivating effect that brings the natural world to life.

Pushing Myself as a Developer

Building BioDetect wasn’t just a coding exercise, it was a learning journey. As someone who lives and breathes backend development, diving into frontend technologies like React and WebGi was challenging but incredibly rewarding. I grew as a developer, learning how to balance both the front and back ends of a project while keeping the user experience at the forefront.

There were definitely moments of frustration (especially with the 3D butterfly), but there’s something deeply satisfying about seeing your vision come to life. And now, with BioDetect, I’ve created something that blends education, technology, and design in a way that feels both informative and magical.

Try BioDetect and Support My Journey

I’d love for you to check out BioDetect and let me know what you think. Whether you’re a nature enthusiast or just curious about how it works, I promise it’s a fun and educational experience.

➡️ Try BioDetect

➡️ Watch the Demo Video

➡️ Vote for BioDetect

I’m really excited to see how far this project can go, and I’m grateful for any support you can give by voting or sharing!

Looking for Opportunities!

Finally, I’m currently on the lookout for opportunities as a backend or fullstack developer. If you know of any openings or can refer me to someone who is hiring, I’d love to chat. Feel free to reach out!

Thanks for joining me on this journey, BioDetect wouldn’t be where it is without the initial curiosity, countless hours of development, and a little push from the Google Gemini competition. I can’t wait to see what’s next!

Let’s build something incredible together!

CoduimAI's New Cover-Agent: The Ultimate Guide

Nnaemeka Daniel John — Tue, 21 May 2024 05:42:00 +0000

The Cover-Agent test coverage tool just dropped!!

So, I'll be walking you through everything you need to know in order to get Cover-Agent up and running locally on your workstation.

Overview of Cover-Agent

First, let's understand what the Cover-Agent is and what it does.

The Cover-Agent is an opensource test coverage tool by CodiumAI that utilizes various Generative AI models to automate unit test creation for your projects.
Under the hood, the Cover-Agent is comprised of various components, namely;

The Test Runner: Which runs the test suite and generates code coverage reports.
The Coverage Parser: Which verifies that code coverage increases as tests are added, ensuring that new tests contribute to the overall test effectiveness.
The Prompt Builder: Which gathers the necessary data from the codebase and constructs the prompt to be passed to the Large Language Model.
The Caller: Which interacts with the LLM to generate tests based on the prompt provided.

Cover-Agent can currently be run locally on your terminal, although CodiumAI is working on making it compatible with popular CI platforms.

Installation

Prerequisites
Before you begin, ensure you have the following:

OPENAI_API_KEY: Required for calling the OpenAI API.
Python/PIP: Required for installing Cover-Agent.

To get Cover-Agent running as fast as possible, the easiest setup would be to install Cover-Agent as a Python Pip package using the command.

$ pip install git+https://github.com/Codium-ai/cover-agent.git

To ensure that Cover-Agent have been properly installed, you can run the cover-agent command on the terminal, and you should see something like this.

$ cover-agent
usage: cover-agent [-h] --source-file-path SOURCE_FILE_PATH --test-file-path TEST_FILE_PATH --code-coverage-report-path CODE_COVERAGE_REPORT_PATH --test-command TEST_COMMAND
                   [--test-command-dir TEST_COMMAND_DIR] [--included-files [INCLUDED_FILES ...]] [--coverage-type COVERAGE_TYPE] [--report-filepath REPORT_FILEPATH]
                   [--desired-coverage DESIRED_COVERAGE] [--max-iterations MAX_ITERATIONS] [--additional-instructions ADDITIONAL_INSTRUCTIONS] [--openai-model OPENAI_MODEL] [--prompt-only]
cover-agent: error: the following arguments are required: --source-file-path, --test-file-path, --code-coverage-report-path, --test-command

Next, you need to set your OpenAI key in your environment variables.

$ export OPENAI_API_KEY=**********************

Generating Tests using Cover-Agent

Now you are ready to run the Cover Agent to generate and validate unit tests.
You can execute it from the command line using the following command:

$ cover-agent \
  --source-file-path "<path_to_source_file>" \
  --test-file-path "<path_to_test_file>" \
  --code-coverage-report-path "<path_to_coverage_report>" \
  --test-command "<test_command_to_run>" \
  --test-command-dir "<directory_to_run_test_command>" \
  --coverage-type "<type_of_coverage_report>" \
  --desired-coverage <desired_coverage_between_0_and_100> \
  --max-iterations <max_number_of_llm_iterations> \
  --included-files "<optional_list_of_files_to_include>"

Running Cover-Agent

I have a Python project which auto-generates portfolios using the GitHub API. The API endpoints in this project are located in my app.py file.

I also have a test file test_app.py, for testing the API endpoints.

So with this setup I’m ready to run cover-agent from the terminal.

I ran the command below to generate relevant unit tests for my project.

$ cover-agent \
  --source-file-path "app.py" \
  --test-file-path "test_app.py" \
  --code-coverage-report-path "coverage.xml" \
  --test-command "pytest --cov=. --cov-report=xml --cov-report=term" \
  --test-command-dir "." \
  --coverage-type "cobertura" \
  --desired-coverage 70 \
  --max-iterations 10

Ensure you replace the file paths with the actual file path where your code and test files are located.

Changes

Cover-Agent appends the generated unit tests to your test file.

After generating tests for your project, you’ll notice some new files in your repo.

generated_prompt.md: Which contains the full prompt that is sent to the LLM.

coverage.xml: Which contains the test coverage scope.

run.log: Which contains a copy of the logger that gets dumped to your stdout.

test_results.html: Which contains the results table that contains the following for each generated test:

Test status

Failure reason (if applicable)

Exit code,

stderr

stdout

Generated test

Testing is no doubt one of the most important aspects of software development. And with a few steps you can up your testing game by utilizing the Cover-Agent tool to generate meaningful tests for your projects, ensuring test coverage for your codebase in no time.

Cover-Agent is an opensource software on GitHub, which means you can take a look at the inner workings of the tool, make meaningful contributions and help extend the capabilities of the tool.

References

Introduction to PR-Agent: Enhancing Productivity with AI 🚀

Nnaemeka Daniel John — Sun, 12 May 2024 11:38:46 +0000

In today's software development ecosystem, speed is king. Developers are constantly under a lot of pressure to deliver high-quality code quickly. But managing pull requests (PRs) can be a time-consuming and tedious task.

Good news!!!
PR-Agent can help!

How?! you may ask..

In this article, I'll fill you in on PR-Agent as well as its key features, benefits and use case scenarios.

PR-Agent's Purpose

PR-Agent is an innovative tool by CodiumAI that uses Artificial Intelligence and various learning models to speed up your PR workflow.

How does it do this?

Imagine you have a helpful and intelligent assistant who can analyze code changes, identify potential issues, and suggest improvements. That's essentially what PR-Agent does. It uses advanced AI to understand your code and provide valuable feedback in a clear and concise way.

Explanation of Automated Pull Requests

Pull requests are an important part of software development workflows. They allow developers to propose changes to a codebase, which are then reviewed and merged into the main branch. PR-Agent is your coding buddy who catches typos, suggests improvements, and makes reviewing code less of a chore.

PR-Agent uses automation to make pull request management faster and smoother.

Key Features of PR-Agent

Here are some of the few things that makes PR-Agent your new best friend in development:

🛠️ Review Automation: With the /review command, you no longer have to spend hours combing through lines of code. PR-Agent helps you review changes quickly, highlights potential issues and offers you suggestions for improvement. It frees you up to focus on the bigger picture and write awesome code.

🔍 Code Suggestions: PR-Agent doesn't just point out problems; it also suggests ways to make your code cleaner, more efficient, and easier to understand by others. It's like having an extra pair of really smart eyes on your project.

📝 Auto Description: Writing PR descriptions can be time-consuming and a pain point for most developers, and that is why PR-Agent automates the process of generating your PR descriptions, saving your time and ensuring that all the necessary information is included.

🤖 Question Answering: Are you stuck? or have question about your PR?

PR-Agent is there to help!

All you need to do is use the /ask command directly in your pull request, and it will help you clarify things. Whether you have a question about the code itself or how your changes might interact with other parts of the project, PR-Agent will provide insights that will keep you moving forward.

Explore more tools and commands

Benefits of Using PR-Agent

⏱️ Get More Done in Less Time: PR-Agent does the grunt work by automating repetitive tasks like reviews and help you write clear descriptions faster, so you can spend less time managing PRs and more time building awesome features.

📈 Increased Productivity: Imagine smoother code reviews, fewer roadblocks, and happier developers. That's the PR-Agent effect!
PR-Agent keeps your team moving at lightning speed.

🚀 Faster Feedback Loop: PR-Agent gives you instant insights and suggestions, so you can fix issues and iterate on your code quickly. This means faster project turnaround times and happier clients or users.

Use Cases and Scenarios

👩‍💻 Large-Scale Projects: Managing a massive codebase with a lot of PRs can feel very daunting. But PR-Agent can help you keep track of everything, ensuring timely reviews, and keep your project running smoothly, even with a large team of developers.

👨‍👩‍👦‍👦 Remote Teams: Distance doesn't have to slow you down. PR-Agent can bridge the gap and keep everyone on the same page, no matter where they're located.

🌐 Open Source Contributions: Contributing to open source projects is amazing, but reviewing a lot of PRs can be overwhelming. PR-Agent can help you manage contributions and engage with the community effectively.

PR-Agent represents the future of code review and collaboration, using the power of AI to 10x productivity and efficiency in software development.

PR-Agent is not just some fancy AI tool. It's your coding companion, that helps you write better code, collaborate effectively, and get things done faster.

With PR-Agent by your side, you can automate repetitive tasks and focus on building incredible software solutions.

References

Revolutionizing Pull Request Collaboration: CodiumAI PR-Agent VS GitHub Copilot For Pull Requests

Nnaemeka Daniel John — Tue, 12 Dec 2023 07:49:53 +0000

In the dynamic world of software development, tools that enhance productivity and streamline workflows are highly sought after, AI-powered tools have surfaced to enhance the efficiency and 10x the productivity of developers. Among these tools, CodiumAI's PR-Agent and GitHub Copilot stand out, offering distinct approaches to enhancing code quality and developer productivity.

AI for 10x Productivity

Before getting into the specifics of CodiumAI's PR-Agent and GitHub Copilot, it's important to acknowledge the transformative impact of AI on productivity. These tools leverage artificial intelligence to not only facilitate code review processes but also to significantly amplify the productivity of developers, allowing them to achieve more in less time.

This article will focus specifically on the Git solution provided by PR-Agent and GitHub Copilot solution for pull requests.

A Closer Look at PR-Agent by CodiumAI

1. Overview

PR-Agent, an open-source AI-powered tool by CodiumAI, aims at automating pull request analysis, feedback, and suggestions. Its development focus includes identifying potential code problems, improving code quality, and facilitating collaborative feedback from other developers.

2. Versatile Git Compatibility

From auto-generating descriptions to suggesting code improvements and answering free-text questions, PR-Agent seamlessly functions within Git and various integrated development environments (IDEs).

Notably, PR-Agent extends its support to all Git platforms, ensuring its adaptability to different version control systems. Some prominent Git platforms that PR-Agent seamlessly integrates with includes but are not limited to GitHub, GitLab, Bitbucket, CodeCommit, Azure Devops etc.

3. Quick Setup Procedure

For the quickest setup of PR-Agent on your repositories, I recommend running it as a GitHub Action. The installation guide can be found here.

If you're planning to use PR-Agent on both public and private repositories, consider running it as a GitHub App. The installation guide for GitHub App setup is available here.

Choose the option that best fits your repository needs, and follow the provided guides for a seamless PR-Agent setup.

4. Commands and Usage

PR-Agent prides itself on offering a comprehensive set of commands designed to empower developers in various aspects of pull request management, each serving a unique purpose in the code review and improvement process. Here's a detailed look at some key commands:

If you've installed Codium PR-Agent on a private repository, you can utilize commands like /describe. On the other hand, for public repositories, simply mention the name @CodiumAI-Agent in your Git provider environment, then request actions such as /review or /describe.

5. Use Case

In a recent scenario involving the Alxfolio project, a portfolio-generating site utilizing the GitHub API for fetching user data, I opened a pull request. Within this pull request, my focus was on enhancing the get_user function. The refactoring process involved code improvements, also, I globalized the GitHub API URL for better maintainability.

After submitting the pull request, the PR-Agent, set up as a GitHub Action, automatically conducted checks on the changes. As a result, it stands ready to accept commands from me, paving the way for a streamlined process for further review and interaction. The PR-Agent's automated checks help ensure the integrity of the pull request and enhance collaboration within the development workflow.

Auto Description (/describe):

Public Repo

@CodiumAI-Agent /describe

Private Repo

/describe

I generated a description for my pull request by commenting the /describe command on the pull request. This command efficiently generates a concise yet comprehensive PR description. It encompasses crucial details such as title, type, summary, code walkthrough, and labels.

Auto Review (/review):

Public Repo

@CodiumAI-Agent /review

Private Repo

/review

By commenting the /review command, I received adjustable feedback covering key aspects, including the PR's main theme, type, relevant tests, security considerations, and diverse suggestions for enhancing the overall content.

Question Answering (/ask ...):

Public Repo

@CodiumAI-Agent /ask "something"

Private Repo

/ask "something"

In the ongoing interaction with the Alxfolio project's pull request with PR-Agent, I leveraged the /ask command to seek insights. Specifically, I inquired about “The primary purpose of the get_user function”. The /ask command excels in addressing free-text questions related to the PR. It provided me with valuable information and clarifications about the code and its broader context, contributing to a more nuanced understanding of the changes introduced.

Code Refactoring (/improve):

Public Repo

@CodiumAI-Agent /improve

Private Repo

/improve

I applied the /improve command and it played a crucial role in suggesting opportunities for enhancing not only the get_user function but also other supporting functions within the functions.py file.

The /improve command is designed to identify areas for refactoring, focusing on aspects such as code quality, readability, and maintainability. Following these suggestions, I am prepared to commit these changes, ensuring that the codebase benefits from optimized practices.

Code Documentation (/add_docs):

Public Repo

@CodiumAI-Agent /add_docs

Private Repo

/add_docs

I used the /add_docs command to streamline the documentation process and it performed an automatic scan of my code, identifying undocumented functions and code blocks within the pull request. The command adds documentation to previously un-documented functions and classes. The automated detection and addition of documentation enhance the overall clarity and understanding of the codebase.

With the suggested documentation in place, I have the option to commit these changes, ensuring that the pull request not only introduces code enhancements but also maintains comprehensive and well-documented code practices.

You can refer to PR-Agent on Github to see more Commands.

6. Open-source Nature

One notable strength of PR-Agent lies in its open-source nature, inviting transparency and community collaboration. Developers have the freedom to inspect and modify the tool, fostering a culture of shared innovation and continuous improvement.

Exploring GitHub Copilot For Pull Requests

1. Overview

GitHub Copilot, a collaboration between GitHub and OpenAI, is another powerhouse in the AI-driven development landscape. GitHub Copilot for pull requests focuses on generating summaries with specific commands. Developers can initiate this feature during PR creation, by editing the pull request description post-creation, or by commenting in the pull request thread. The main commands include showcasing all types of content, generating a one-paragraph summary, creating a detailed list of changes, or even crafting a poetic description.

2. Marker Tags for Description Expansion

Copilot's output for pull request summaries consists of a concise three-sentence prose overview, providing a high-level understanding of the changes. Additionally, it generates 3–5 bulleted changes listed with links to the respective lines of code they refer to.
In addition to pull request summaries, Copilot introduces marker tags for enhancing pull request descriptions. Developers can use markers like copilot:all, copilot:summary, and copilot:walkthrough to guide Copilot in expanding descriptions into summaries of changes.

3. GitHub Integration

One of Copilot's key strengths lies in its seamless integration with GitHub and various code repositories. Operating directly within popular code editors like Visual Studio Code, it ensures developers have immediate access to its suggestions during the coding process.

4. Proprietary Nature

Unlike PR-Agent's open-source model, GitHub Copilot follows a proprietary approach. While this may restrict access to some extent, it also reflects a dedication to delivering polished features and an enhanced user experience.

Comparative Analysis Between CodiumAI's PR-Agent and GitHub Copilot For Pull Requests

1. Specialization vs. Generalization

PR-Agent specializes in comprehensive pull request reviews with a myriad of tools and commands, covering various aspects of the review process. On the other hand, GitHub Copilot, while powerful, is more focused on generating descriptions and summaries in response to specific commands.

2. Usability and Integration

Both PR-Agent and Copilot emphasize seamless integration into developer workflows. PR-Agent's compatibility with multiple Git platforms and IDEs, including GitHub, GitLab, Bitbucket, CodeCommit, Azure DevOps, etc, positions it as a tool suitable for a broad audience, regardless of the chosen development environment. Copilot, while seamlessly integrating with GitHub, its compatibility is limited exclusively to the GitHub platform.

3. Extensive Commands vs. Specific Descriptions

PR-Agent provides an extensive array of commands for nuanced interactions, ranging from code improvements to answering questions. In contrast, Copilot's commands are tailored specifically to generate pull request descriptions and summaries.

4. Flexibility and Customization

PR-Agent's open-source nature and versatile commands provide developers with flexibility. GitHub Copilot, with its proprietary model, GitHub Copilot, in the context of pull requests, streamlines its focus on providing concise, AI-generated descriptions.

5. Command Variety

PR-Agent supports a diverse range of commands, each serving a specific purpose in code review and improvement. Developers can utilize commands such as /describe, /review, /ask, /refactor, /generate, /summarize, /test, /debug, /optimize, and /document, enabling a comprehensive set of functionalities.

In contrast, GitHub Copilot for pull requests has a more singular focus, primarily offering concise AI-generated descriptions using markers such as copilot:all, copilot:summary, and copilot:walkthrough. While this streamlines its functionality for pull requests, it lacks the command variety found in PR-Agent.

6. Community Involvement

PR-Agent, being open-source, fosters community involvement. Developers can contribute to its development, report issues, and participate in enhancing its capabilities. GitHub Copilot, being a proprietary tool, operates without the same level of community engagement in terms of direct contributions to its codebase.

7. Cost and Accessibility

PR-Agent is free to use, being an open-source tool, making it accessible to developers without any cost barriers. On the other hand, GitHub Copilot follows a subscription-based model, costing $10 USD per month or $100 USD per year, potentially limiting its accessibility, especially for individual developers or small teams.

Conclusion

In conclusion, the comparison between PR-Agent and GitHub Copilot reveals distinct approaches to pull request summaries. PR-Agent distinguishes itself with a feature-rich arsenal of commands, a variety of tools, and compatibility across a broad spectrum of Git platforms, making it a comprehensive solution for robust pull request reviews. On the contrary, GitHub Copilot excels in its specialized role, focusing on generating descriptions and summaries through specific commands.

It's worth noting that PR-Agent is freely available, making it a cost-effective choice, while GitHub Copilot comes with associated costs. Personally, I find the comprehensive and free nature of CodiumAI's PR-Agent solution for GitHub to be a compelling factor, further solidifying its appeal for developers seeking a versatile and budget-friendly tool for pull request management. As these tools evolve, they continue to be instrumental in driving innovation, efficiency, and collaboration within the dynamic landscape of software development. Developers are encouraged to explore and harness the strengths of both tools, with PR-Agent emerging as a robust and accessible choice for GitHub repositories.

References

Guide: Creating Temporary Tables in PostgreSQL

Nnaemeka Daniel John — Mon, 28 Aug 2023 16:29:02 +0000

In this guide, we will take a look at the process of creating temporary tables in PostgreSQL.

Understanding Temporary Tables

Temporary tables in PostgreSQL are a special type of table that exist only for the duration of a database session. They are very useful when you need to store and process data temporarily without the need for permanent storage. Temporary tables are created within a specific schema and are automatically dropped at the end of the session or transaction.

Creating Temporary Tables

Let's dive into the steps of creating temporary tables in PostgreSQL:

Step 1: Connect to the Database
Before creating any table, you need to establish a connection to your PostgreSQL database using a client of your choice. This can be achieved using tools like psql, a popular command-line interface, or through graphical user interfaces like pgAdmin.

Step 2: Syntax for Creating Temporary Tables
The syntax for creating a temporary table is similar to that of a regular table, with one key difference: the use of the TEMPORARY keyword. Here's a basic template:

CREATE TEMPORARY TABLE table_name (
    column1 data_type,
    column2 data_type,
    -- Define other columns here
);

For example:

my_db=# CREATE TEMPORARY TABLE temp_table (
    id SERIAL PRIMARY KEY NOT NULL
    name VARCHAR(25) NOT NULL);
CREATE TABLE

Step 3: Populating the Temporary Table
Once you've defined the structure of your temporary table, you can populate it with data using the standard INSERT INTO statement. This allows you to work with the temporary data just like you would with any other table.

my_db=# INSERT INTO temp_table(name) VALUES('Daniel'),
my_db-# ('Mary'), ('George');
INSERT 0 3

my_db=# SELECT * FROM temp_table;
 id |  name
----+--------
  1 | Daniel
  2 | Mary
  3 | George
(3 rows)

Step 4: Performing Operations
Temporary tables can be used for a variety of purposes, such as:

1. Intermediate Calculations: When you need to perform complex calculations or data transformations, temporary tables provide a way to store intermediate results before arriving at the final output.

2. Session-Specific Data: Temporary tables are session-specific, which means different users can work with their own copies of the same temporary table without conflicting with each other's data.

3. Batch Processing: Temporary tables are commonly used in batch processing scenarios, where you can load data into a temporary table, manipulate it, and then insert the processed data into your main tables.

Step 5: Dropping Temporary Tables
Remember that temporary tables are automatically dropped at the end of your session or transaction. But, if you want to explicitly drop a temporary table before that, you can use the DROP TABLE statement:

DROP TABLE IF EXISTS table_name;

For example:

my_db=# DROP TABLE IF EXISTS temp_table;
DROP TABLE

Conclusion

Creating temporary tables in PostgreSQL offers a versatile and efficient way to manage and process data within the context of a session. These tables are particularly handy for intermediate data storage during complex calculations, batch processing, and more. By understanding the syntax and use cases of temporary tables, you can unlock a powerful tool in your PostgreSQL toolkit. Whether you're an analyst, developer, or database administrator, temporary tables can enhance your data manipulation capabilities and streamline your workflows.

References

PostgreSQL CREATE TABLES
PostgreSQL Installation Procedure
Visit Apache AGE Website: https://age.apache.org/
Visit Apache AGE GitHub: https://github.com/apache/age
Visit Apache AGE Viewer GitHub: https://github.com/apache/age-viewer

Guide: Creating Views in PostgreSQL

Nnaemeka Daniel John — Fri, 25 Aug 2023 17:38:40 +0000

Welcome to this informative blog post that delves into the fascinating world of creating views in PostgreSQL. If you're looking to enhance your database management skills, understanding how to create views is a crucial step. Views offer a way to simplify complex queries, improve security, and optimize data retrieval, and we'll explore the process step by step. So, let's dive in!

What are Views in PostgreSQL?

In PostgreSQL, a view is a virtual table that presents data from one or more base tables or other views. It's like a window into your data, allowing you to structure and present it in a way that makes sense for your specific use case. Views are particularly helpful when dealing with intricate queries or when you need to grant users access to specific data without exposing the entire database schema.

Getting Started

Before we jump into creating views, let's ensure you have the necessary environment set up:

Operating System: Ubuntu 22.04.2 LTS
PostgreSQL Version: postgresql-13.10
Development Environment For this tutorial, I've used a development environment within a WSL Ubuntu machine.

Creating Your First View

Let's start by creating a simple view to understand the process. Imagine we have a database containing information about products and their prices. Our goal is to create a view that displays products with their corresponding prices. Here's how you can achieve this:

- Defining the Query:
Open your preferred PostgreSQL client or terminal and connect to your database. Then, define the query that retrieves the desired information. In our case, the query might look like this:

my_db=# SELECT * FROM products;
 product_id | product_name | price
------------+--------------+-------
          1 | Product A    | 19.99
          2 | Product B    | 29.99
          3 | Product C    |  9.99
          4 | Product D    | 39.99
          5 | Product E    | 14.99

my_db=# CREATE VIEW product_prices AS
my_db-#    SELECT product_name, price
my_db-# FROM products;
CREATE VIEW

- Creating the View:
The above SQL statement creates a view named product_prices. It fetches the product_name and price columns from the products table and presents them in a new virtual table.

- Using the View:
Once the view is created, you can use it just like any other table in your database. For instance, you can run a simple query like:

my_db=# SELECT * FROM product_prices;
 product_name | price
--------------+-------
 Product A    | 19.99
 Product B    | 29.99
 Product C    |  9.99
 Product D    | 39.99
 Product E    | 14.99
(5 rows)

This query retrieves all the data from the product_prices view, making it easier to access the desired information.

Best Practices

To ensure your views are effective and maintainable, consider these best practices:

1. Keep it Simple: Views are meant to simplify complex queries. Avoid overcomplicating them; if a view becomes too intricate, it might defeat its purpose.

2. Security: Views can help you control data access. Grant permissions only to the views users need, safeguarding sensitive information.

3. Performance: While views are powerful, be mindful of performance implications, especially when dealing with large datasets. Optimize queries and indexes as needed.

Conclusion

In this comprehensive guide, we've explored the world of creating views in PostgreSQL. Views are an essential tool in a database administrator's toolkit, offering ways to streamline queries and enhance security. By defining, creating, and utilizing views effectively, you can simplify data retrieval and present information in a structured and user-friendly manner. Remember to adapt solutions to challenges and follow best practices to ensure your views serve your database needs efficiently.

So, go ahead and experiment with creating views in PostgreSQL. Unlock the potential to harness your data more effectively than ever before! Happy coding! 🚀

References

PostgreSQL CREATE VIEW
PostgreSQL Installation Procedure
Visit Apache AGE Website: https://age.apache.org/
Visit Apache AGE GitHub: https://github.com/apache/age
Visit Apache AGE Viewer GitHub: https://github.com/apache/age-viewer

Creating User-Defined Functions in PostgreSQL: A Journey into Customization

Nnaemeka Daniel John — Thu, 24 Aug 2023 19:22:02 +0000

In the realm of database management, PostgreSQL stands tall as one of the most powerful and versatile options available. Its extensibility allows developers to utilize its capabilities by creating their own functions, tailored to their specific needs. In this blogpost, we will take a look at creating custom functions within the PostgreSQL environment, understanding the intricacies of the process and the benefits it offers.

Why Create Custom Functions?

At times, the predefined functions offered by PostgreSQL might not fully align with the requirements of a particular application. This is where custom functions comes into play. These custom functions are carefully created to perform specific tasks that may involve complex calculations, data manipulations, or tailored business logic.

Consider a scenario where you need to compute a unique scoring algorithm for your application's users based on various data points. The predefined functions available in PostgreSQL might not encapsulate your precise requirements. Crafting a custom function empowers you to design a solution that aligns perfectly with your business logic.

Components of a Custom Function

Creating a custom function in PostgreSQL involves a mix of SQL and PL/pgSQL. PL/pgSQL is a procedural language specifically designed for PostgreSQL, allowing developers to embed procedural code within SQL statements. This blend of declarative SQL and procedural constructs forms the backbone of custom functions.

Let's walk through the creation of a custom function. Imagine a scenario where you need to calculate the total purchase amount for a given customer based on their order history. The predefined functions falls short, and it's time to activate your creativity.

CREATE OR REPLACE FUNCTION calculate_total_purchase(customer_id INT)
RETURNS NUMERIC AS $$
DECLARE
    total_purchase NUMERIC := 0;
BEGIN
    SELECT SUM(order_amount) INTO total_purchase
    FROM orders
    WHERE orders.customer_id = calculate_total_purchase.customer_id;

    RETURN total_purchase;
END;
$$ LANGUAGE plpgsql;

In the above query, we've created a function named calculate_total_purchase. This function accepts a customer_id as input and returns the total purchase amount as a NUMERIC. The way we use PL/pgSQL syntax inside the function lets us combine SQL queries, control actions, and variables together.

Calling Custom Functions

After you've created your custom function, you have can use it whenever you want. Using it is quite straightforward.

For example:
Given table orders;

my_db=# select * from orders;
 order_id | customer_id | order_amount
----------+-------------+--------------
        1 |           1 |       100.50
        2 |           1 |        75.20
        3 |           2 |        50.00
        4 |           3 |       200.75
        5 |           2 |       120.30
        6 |           3 |        50.50
        7 |           1 |       300.25
        8 |           2 |        75.60
        9 |           3 |       150.00

And to call the function calculate_total_purchase;

my_db=# SELECT calculate_total_purchase(2);
 calculate_total_purchase
--------------------------
                   245.90
(1 row)

Here, we've used our custom function called "calculate_total_purchase" and passed in the customer ID of 2. The function becomes active and starts going through the records of past orders made by that customer. It adds up all the amounts from those orders to figure out the total amount the customer has spent. Finally, it gives us this total amount as an outcome or result.

The Advantages of Custom Functions in PostgreSQL

When we delve into the world of custom functions in PostgreSQL, we uncover a wide range of benefits:

1. Tailored Solutions: Custom functions offer you the ability to create solutions that fit perfectly with your application's specific needs. This lets you open up new possibilities for how your application works.

2. Improved Performance: By optimizing your custom functions for specific tasks, you can make your application work faster and more efficiently.

3. Better Organization: When you wrap complex business logic within custom functions, you make your code easier to manage and understand. This keeps your application's codebase clean and maintainable.

4. Reusability: Once you've created custom functions, you can use them in different parts of your application. This promotes the reuse of code and reduces the need for repeating the same code over and over.

5. Adaptability: As your application grows and changes, your custom functions can change with it. This means they can always meet the new requirements, keeping your data management effective.

Conclusion

As I conclude this blogpost, the importance of custom functions in PostgreSQL remains strong. Every time you design a custom function, you breathe life into how your application handles data and give it a touch of your unique ingenuity.

Keep in mind that PostgreSQL is more than just a database; it's a vast canvas where your creative ideas can thrive. Use the power of custom functions to shape your data in ways that align with your imagination. Witness your applications move beyond the ordinary and step into the extraordinary.

References

PostgreSQL CREATE FUNCTION
PostgreSQL Installation Procedure
Visit Apache AGE Website: https://age.apache.org/
Visit Apache AGE GitHub: https://github.com/apache/age
Visit Apache AGE Viewer GitHub: https://github.com/apache/age-viewer

Guide: Creating Triggers in PostgreSQL

Nnaemeka Daniel John — Wed, 23 Aug 2023 16:08:02 +0000

In the world of relational databases, PostgreSQL stands out as a powerful and versatile option. It offers a lot of features that allow developers to implement sophisticated solutions for various data management challenges. One of these features, triggers, plays a crucial role in automating actions based on events occurring within a database. In this article, we will take a look at the triggers in PostgreSQL, exploring their purpose, implementation, and potential use cases.

Understanding Triggers

A trigger in PostgreSQL is a procedural code block that gets executed automatically in response to specific events occurring within a database table. These events can include data manipulation language (DML) actions like INSERT, UPDATE, and DELETE statements. Triggers provide a way to enforce business rules, maintain data integrity, and carry out complex actions without requiring explicit user intervention.

The Components of a Trigger

Before we dive into creating triggers, let's break down the essential components that make up a PostgreSQL trigger:

- Event: This is the database operation that triggers the execution of the trigger. Events can include INSERT, UPDATE, DELETE, or even a combination of these.

- Trigger Function: A trigger function is a block of code written in PL/pgSQL, which is PostgreSQL's procedural language. This function is executed when the trigger's associated event occurs.

- Trigger Constraint: This is an optional condition that determines whether the trigger function should be executed. It is specified using a WHEN clause in the trigger's definition.

Creating a Trigger

Now that we understand the components, let's walk through the process of creating a trigger step by step.

Step 1: Create a Trigger Function
First, we need to define the trigger function. This function encapsulates the logic that should be executed when the trigger's event occurs. Here's an example of a trigger function that logs a message when a new row is inserted into a table:

CREATE OR REPLACE FUNCTION log_insert() RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'New row inserted with ID: %', NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 2: Create the Trigger
Once the trigger function is defined, we can create the actual trigger that associates the function with a specific event on a table. Here's how you can create a trigger that fires after an INSERT operation on a table named "trigg_table":

CREATE TRIGGER after_insert_trigger
AFTER INSERT ON trigg_table
FOR EACH ROW
EXECUTE FUNCTION log_insert();

Step 3: Test the Trigger
With the trigger in place, you can now test its functionality by performing an action that triggers the event. In this case, inserting a new row into the "trigg_table" table will cause the trigger function to execute and log the inserted row's ID.

For example: Given table

my_db=# CREATE TABLE trigg_table(id SERIAL PRIMARY KEY NOT NULL, name VARCHAR(20));
CREATE TABLE
my_db=# INSERT INTO trigg_table(name) VALUES('Daniel');
NOTICE:  New row inserted with ID: 1
INSERT 0 1

As you can see, a NOTICE was raised indiating the insertion of a new row into the trigg_table table.

Use Cases for Triggers

Triggers can be immensely useful in various scenarios. Here are a few common use cases:

- Audit Trail: Triggers can be employed to track changes made to critical data. By capturing before-and-after values, you can maintain an audit trail of all modifications.

- Data Validation: Triggers can enforce complex data validation rules that go beyond simple constraints, ensuring that only valid data is inserted, updated, or deleted.

- Derived Data: When certain columns depend on others, triggers can automatically calculate and update these derived values whenever the base data changes.

- Notifications: Triggers can be used to send notifications or alerts based on specific events, keeping stakeholders informed in real-time.

- Archiving: Triggers can facilitate data archiving by moving old records from one table to another based on predefined criteria.

Best Practices

While triggers offer powerful automation capabilities, they should be used judiciously. Here are a few best practices to keep in mind:

- Keep Logic Simple: Triggers should focus on lightweight tasks. Complex operations are better suited for application code.

- Document Thoroughly: Clearly document the purpose, behavior, and logic of your triggers. This will aid in maintenance and troubleshooting.

- Testing is Crucial: Thoroughly test triggers before deploying them in production to avoid unexpected behaviors or performance bottlenecks.

- Monitor Performance: Triggers can impact database performance, especially if they involve complex operations. Regularly monitor their impact on query performance.

Conclusion

Triggers in PostgreSQL provide a mechanism for automating actions based on database events, making them a powerful tool for maintaining data integrity, enforcing business rules, and reducing manual intervention. By understanding their components, creating well-defined trigger functions, and adhering to best practices, developers can harness the full potential of triggers to streamline their database operations and achieve more efficient data management.

References

PostgreSQL CREATE TRIGGER
PostgreSQL Installation Procedure
Visit Apache AGE Website: https://age.apache.org/
Visit Apache AGE GitHub: https://github.com/apache/age
Visit Apache AGE Viewer GitHub: https://github.com/apache/age-viewer

Guide: Creating Stored Procedures in PostgreSQL

Nnaemeka Daniel John — Tue, 22 Aug 2023 11:32:49 +0000

One of the powerful tools at your disposal when using PostgreSQL is the creation of stored procedures, it can help streamline your interactions with your PostgreSQL database. In this article, we'll delve into the ins and outs of creating stored procedures in PostgreSQL, covering everything from their benefits to step-by-step implementation.

Benefits of Stored Procedures

Stored procedures offer a range of benefits that contribute to enhanced database management and application development:

1. Improved Performance
Stored procedures are precompiled and stored in the database. This means that when you call a stored procedure, the database doesn't need to re-parse and compile the SQL statements each time. This can significantly improve the execution speed of your queries.

2. Modularity and Encapsulation
Stored procedures allow you to encapsulate complex logic and operations into reusable units. This promotes modularity in your codebase, making it easier to manage and maintain your database-related tasks.

3. Enhanced Security
By using stored procedures, you can restrict direct access to tables and views, reducing the risk of unauthorized data manipulation. This adds an additional layer of security to your database.

4. Reduced Network Traffic
When you execute a stored procedure, you send a single request to the database server, reducing the amount of network traffic compared to sending individual SQL queries. This can be especially advantageous when dealing with remote database servers.

5. Transaction Management
Stored procedures allow you to group multiple SQL statements into a single transaction. This ensures that either all the statements within the procedure are executed successfully, or none of them are, maintaining data integrity.

Creating a Stored Procedure in PostgreSQL

Let's dive into the practical steps of creating a stored procedure in PostgreSQL:

Step 1: Connect to the Database
First, ensure you have the necessary permissions to create a stored procedure in the target database. Connect to the PostgreSQL database using a tool like psql or a graphical interface like pgAdmin.

Step 2: Define the Procedure
Use the CREATE OR REPLACE PROCEDURE statement to define your stored procedure. This statement allows you to create a new procedure or replace an existing one. Below is a basic template:

CREATE OR REPLACE PROCEDURE procedure_name(parameter1 datatype, parameter2 datatype)
LANGUAGE plpgsql
AS $$
DECLARE
    -- Declare local variables if needed
BEGIN
    -- Your SQL logic here
EXCEPTION
    WHEN OTHERS THEN
        -- Handle exceptions if needed
        RAISE EXCEPTION 'exception';
END;
$$;

Step 3: Implement the Logic
Within the BEGIN and END; block, write the SQL logic that constitutes your procedure. You can use SQL statements, conditionals, loops, and more to achieve your desired functionality.

For example: Given a customer_orders table:

my_db=# select * from customer_orders;
 order_id | customer_id | order_date | total_amount
----------+-------------+------------+--------------
        1 |           1 | 2023-08-01 |       150.00
        2 |           2 | 2023-08-02 |        75.50
        3 |           1 | 2023-08-03 |       200.00
        4 |           3 | 2023-08-04 |        50.00
        5 |           2 | 2023-08-05 |       120.75

We can calculate the sum of total_amount for the specified customer_id using a SELECT statement and store it in the total variable using the INTO clause.

CREATE OR REPLACE PROCEDURE calculate_total_amount_by_customer(customer_id_arg INT)
LANGUAGE plpgsql
AS $$
DECLARE
    total NUMERIC := 0;
BEGIN
    SELECT SUM(total_amount) INTO total
    FROM customer_orders
    WHERE customer_id = customer_id_arg;

    RAISE NOTICE 'Total amount spent by customer %: %', customer_id_arg, total;
EXCEPTION
    WHEN OTHERS THEN
        -- Handle exceptions if needed
        RAISE EXCEPTION 'Error calculating total amount for customer %', customer_id_arg;
END;
$$;

In this query, the RAISE NOTICE statement is used to print a message indicating the total amount spent by the customer.

The EXCEPTION block handles any exceptions that might occur during the execution of the procedure. If an exception is raised, an error message is raised using the RAISE EXCEPTION statement.

Step 4: Execute the Procedure
Once your stored procedure is defined, execute it using the CALL procedure_name(parameters); statement. This will trigger the execution of the procedure, and you'll see the result if the procedure is designed to return a value.

We can execute our above calculate_total_amount_by_customer procedure example by running;

my_db=# CALL calculate_total_amount_by_customer(1);
NOTICE:  Total amount spent by customer 1: 350.00
CALL

As you can see the NOTICE was raised displaying the total amount spent by the customer id that was passed into the procedure.

Step 5: Managing Errors
To handle errors within your stored procedure, you can use EXCEPTION blocks to catch and handle exceptions that might occur during execution.

Conclusion

Stored procedures are a versatile tool for managing and optimizing your PostgreSQL database. They offer benefits such as improved performance, modularity, security, and transaction management. By following the steps outlined in this guide, you can create and deploy your own stored procedures to enhance your database operations. As you become more comfortable with stored procedures, you'll find that they empower you to create efficient and organized database interactions, contributing to the overall success of your applications.

References

PostgreSQL CREATE PROCEDURE
PostgreSQL Installation Procedure
Visit Apache AGE Website: https://age.apache.org/
Visit Apache AGE GitHub: https://github.com/apache/age
Visit Apache AGE Viewer GitHub: https://github.com/apache/age-viewer

What are Indexes?: Creating Indexes in PostgreSQL

Nnaemeka Daniel John — Mon, 21 Aug 2023 14:35:18 +0000

In this article, we'll explore the significance of indexes and I will guide you through the process of creating them to enhance the performance of your PostgreSQL database.

Understanding the Role of Indexes

Indexes are crucial tools that significantly impact the efficiency of database operations. Imagine you have a massive library with countless books, and you need to find a specific book. Without an organized system, you'd have to search through every book one by one. This is the same way a database searches for information in the absence of indexes – it scans through every record sequentially, which can be very time-consuming and resource-intensive.

Indexes act as a roadmap, helping the database quickly locate the data you need. They provide an optimized way to access information, reducing the time it will take to retrieve data and improving the overall performance of your database queries.

Types of Indexes in PostgreSQL

PostgreSQL offers various types of indexes, each one with its unique characteristics. Here are some commonly used ones:

B-Tree Indexes
B-Tree indexes are the default indexes in PostgreSQL. They're well-suited for queries that involve comparison operators such as =, <, >, <=, and >=. B-Tree indexes organize data in a balanced tree structure, allowing for efficient search operations.
Hash Indexes
Hash indexes are ideal for equality-based searches. They work by hashing the indexed column's values and storing them in a hash table. Even though they are fast for equality searches, they are less effective for range queries or pattern matching.
GiST (Generalized Search Tree) Indexes
GiST indexes are versatile and support various data types. They are especially useful for complex data types like geometric shapes and full-text search. GiST indexes provide multiple search strategies, making them a powerful option for diverse querying needs.
GIN (Generalized Inverted Index) Indexes
GIN indexes are designed for columns containing arrays or complex types. They allow efficient searching for elements within arrays and are great for scenarios where your data involves multiple values per record.

Creating Indexes in PostgreSQL

Now that we've covered the importance of indexes and their types, let's talk about how you can create indexes in PostgreSQL.

Suppose you have a table named customer_orders with columns like order_id, customer_id, and order_date. To create an index on the customer_id column, follow these steps:

Connect to Your Database Ensure you're connected to your PostgreSQL database using a tool like psql or a graphical interface like pgAdmin.

my_db=# select * from customer_orders;
 order_id | customer_id | order_date | total_amount
----------+-------------+------------+--------------
        1 |           1 | 2023-08-01 |       150.00
        2 |           2 | 2023-08-02 |        75.50
        3 |           1 | 2023-08-03 |       200.00
        4 |           3 | 2023-08-04 |        50.00
        5 |           2 | 2023-08-05 |       120.75

Write the Index Creation Query Use the CREATE INDEX statement to create an index. In our example, you would run:

my_db=# CREATE INDEX idx_customer_id ON customer_orders(customer_id);
CREATE INDEX

Monitor and Optimize

Once the index is created, PostgreSQL will automatically use it to optimize relevant queries. However, keep in mind that while indexes improve read performance, they can slow down write operations. Regularly monitor your database's performance and consider indexing columns that are frequently used in WHERE clauses or JOIN operations.

The Best Practices and Considerations

While indexes are powerful tools, it's important to use them judiciously. Here are some best practices:

Choose Columns Wisely: Index columns that are frequently used in queries, but avoid excessive indexing as it can lead to unnecessary overhead.
Analyze Query Patterns: Understand your application's query patterns to determine which columns need indexes the most.
Monitor and Maintain: Regularly analyze and optimize your indexes. Unused or redundant indexes can negatively impact performance.
Keep Data Consistent: Remember that indexes reflect the state of your data. If data is frequently updated, indexes may need to be rebuilt periodically.

Conclusion

In this comprehensive guide, we've explored the world of PostgreSQL indexes, their types, and how to create them to enhance the performance of your database. By strategically using indexes and following best practices, you can achieve significant improvements in query execution times and overall database efficiency. So go ahead and unleash the power of indexes in PostgreSQL to take your application's performance to new heights!

References

PostgreSQL CREATE INDEX
PostgreSQL Installation Procedure
Visit Apache AGE Website: https://age.apache.org/
Visit Apache AGE GitHub: https://github.com/apache/age
Visit Apache AGE Viewer GitHub: https://github.com/apache/age-viewer

Guide: Installing Apache A Graph Extension (AGE) on PostgreSQL Source Code

Nnaemeka Daniel John — Thu, 27 Jul 2023 21:56:57 +0000

Hey there, fellow tech enthusiasts! Today, I want to take you on a journey to explore an exciting tool that can supercharge your PostgreSQL experience – Apache AGE. 🚀

What is Apache AGE, you might ask? Well, it's an awesome extension that allows you to harness the power of graphs within your PostgreSQL database. And trust me, it's going to open up a whole new world of possibilities for your data.

So, let's dive right in and get started with the installation process. Don't worry; it's not as complex as it sounds!

Setting Up PostgreSQL

Before we jump into Apache AGE, make sure you have PostgreSQL installed on your system. If you haven't done that yet, don't fret! Head over to this blogpost Installing PostgreSQL FROM Source Code (Full Installation Guide), and follow the on-screen instructions. Easy peasy!

Apache AGE Installation

Now that PostgreSQL is up and running, it's time to bring in the graph magic – Apache AGE. Here's how you can do it:

Open up your terminal or command prompt (you know the drill!).
Clone the Apache AGE repository from Github Apache AGE

username@ubuntu:/home$ git clone git@github.com:apache/age.git

Navigate into the AGE repo and install AGE by running PG_CONFIG by running sudo make PG_CONFIG=~/path_to/postgresql-13.10/bin/pg_config install.

username@ubuntu:/home/age$ sudo makePG_CONFIG=~/Desktop/postgresql-13.10/bin/pg_config install

Ensure you replace the path with the actual path to your postgres directory.

Run install check to ensure that AGE installed without any issues. make PG_CONFIG=~/path_to/postgresql-13.10/bin/pg_config install check Ensure you run this command without sudo.

Activating Apache AGE on PostgreSQL

Once the installation is complete, it's time to integrate Apache AGE with PostgreSQL. Here's how:

Open your postgresql.conf file in the data directory.

user@ubuntu:/home/postgresql-13.10/data$ vi postgresql.conf

Search and set set_preload_librabries = 'age'.
Search and set search_path = 'ag_catalog, "user", public'.

Exploring AGE on PostgreSQL

After setting up our postgresql.conf file, we can now start Postgres.

Run bin/pg_ctl -D data -l logfile start to start the postgres server.
Run bin/psql --port=5432 my_db to connect to our database.
Once connected to our database, we can now run AGE cypher queries on PostgreSQL.

psql (13.10)
Type "help" for help.

my_db=# 
my_db=# SELECT * FROM cypher('my_db', $$ MATCH (u)
my_db$# RETURN u
my_db$# $$) AS (u agtype);
 u
---
(0 rows)

my_db=#

Final Thoughts

Congratulations! You've successfully installed Apache AGE on your PostgreSQL database, and now you're equipped with the power of graphs to tackle complex data relationships like a pro. 🚀

Feel free to experiment with graph queries and let your creativity flow! Apache AGE is an incredible tool that can revolutionize the way you interact with your PostgreSQL data.

So go ahead, embrace the graph revolution, and unlock the true potential of your database with Apache AGE. Happy graphing! 😎

References

Installing PostgreSQL FROM Source Code (Full Installation Guide)
Visit Apache AGE Website: https://age.apache.org/
Visit Apache AGE GitHub: https://github.com/apache/age
Visit Apache AGE Viewer GitHub: https://github.com/apache/age-viewer

Installing PostgreSQL FROM Source Code (Full Installation Guide)

Nnaemeka Daniel John — Fri, 21 Jul 2023 22:06:34 +0000

Hey there, tech enthusiasts! Today, we're diving into the exciting world of PostgreSQL and its full installation from source code. PostgreSQL, also known as Postgres, is a powerful open-source relational database management system that has earned its reputation for robustness, reliability, and extensibility. While installing PostgreSQL from source code might sound a bit daunting, fear not! I've got you covered with this very easy-to-follow guide.

Gather the Prerequisites

Before we begin, we need to ensure that we have all the necessary tools and libraries on our system:

C Compiler: PostgreSQL is written in C, so a C compiler is a must. Popular choices include GCC (GNU Compiler Collection) and Clang.
Build Tools: Ensure you have essential build tools like make and autoconf installed. These tools help in compiling the source code efficiently.
Libraries: PostgreSQL requires certain libraries like readline and zlib. We need to install these development libraries on our system.

Depending on your operating system this is the command used to install the needed dependencies on Ubuntu;
sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison.

Download the Source Code

Head over to the official PostgreSQL website PostgreSQL Source Download and download the source code of the desired version. If you intend to use the Apache AGE extension with PostgreSQL, it is advisable to download version 11, 12 or 13 which AGE currently support.

Extract the Source Code

Once the download is complete, navigate to the directory where the source code is saved and extract it using the following command:

tar xvfz postgresql-13.10.tar.gz

Configure PostgreSQL

Once you're done extracting, navigate into the extracted directory, and run configure.

cd postgresql-13.10

The configuration step ensures that PostgreSQL is tailored to your system. I'll recommend a basic configuration to get started:

./configure --prefix=$(pwd) --enable-cassert --enable-debug

The --prefix install all files under the directory PREFIX instead of /usr/local/pgsql. In this case, our present working directory.
The --enable-debug compiles all programs and libraries with debugging symbols. This means that you can run the programs in a debugger to analyze problems.
The --enable-cassert enables assertion checks in the server, which test for many “cannot happen” conditions. This option is not recommended for production use, but you should have it on for development work or when running a beta version.

Compile and Install

Now, let's compile the source code and install PostgreSQL on our system.

gmake && gmake install

Alternatively, you can use make && make install.
The gmake command will compile the source code, and gmake install will install PostgreSQL. You can use sudo to ensure that the installation has the required permissions.

Initialize the Database Cluster

After the installation, right in our postgres directory:

user@ubuntu:/home/postgresql-13.10$ bin/initdb my_db

You can use the bin/initdb my_db command to initialize a database cluster named my_db.

Start PostgreSQL Service

We can now start the PostgreSQL service using;

user@ubuntu:/home/postgresql-13.10$ bin/pg_ctl -D my_db -l logfile start

By default, the postgres server runs on port 5432, but you can change that in the postgresql.conf file.

Create a PostgreSQL Database

Once our server is started, we can now create a new database using the command below;

user@ubuntu:/home/postgresql-13.10$ bin/createdb --port=5432 new_db

The above command creates a new database called new_db in our database cluster my_db.

And you can start writing queries by connecting to the database;

user@ubuntu:/home/postgresql-13.10$ bin/psql --port=5432 my_db
my_db=# \dt
my_db=# \q

You can stop the database server using the command;

user@ubuntu:/home/postgresql-13.10$ bin/pg_ctl -D my_db stop

Congratulations! You have successfully installed PostgreSQL from source code.

Conclusion

You've accomplished an impressive feat - installing PostgreSQL from source code! Embracing the power of PostgreSQL allows you to handle complex data needs and build robust applications. Remember, the journey of a thousand queries begins with a single installation, and you're now well-equipped to harness the potential of this incredible database management system.

So, take charge, explore the vast PostgreSQL documentation, and let your data-driven dreams soar! Happy coding!

References

PostgreSQL Installation Procedure
PostgreSQL Source Download
Visit Apache AGE Website: https://age.apache.org/
Visit Apache AGE GitHub: https://github.com/apache/age
Visit Apache AGE Viewer GitHub: https://github.com/apache/age-viewer

DEV Community: Nnaemeka Daniel John

Building BioDetect: Turning a Simple React App into a 3D Butterfly Experience with WebGi

Entering the Google Gemini Developer Competition

Iterating and Simplifying: The Minimalist Approach

The Butterfly Moment: Adding 3D Model Using WebGi

The Tech Behind BioDetect

Pushing Myself as a Developer

Try BioDetect and Support My Journey

Looking for Opportunities!

Links:

CoduimAI's New Cover-Agent: The Ultimate Guide

Overview of Cover-Agent

Installation

Generating Tests using Cover-Agent

Running Cover-Agent

Changes

References

Introduction to PR-Agent: Enhancing Productivity with AI 🚀

PR-Agent's Purpose

Explanation of Automated Pull Requests

Key Features of PR-Agent

Benefits of Using PR-Agent

Use Cases and Scenarios

References

Revolutionizing Pull Request Collaboration: CodiumAI PR-Agent VS GitHub Copilot For Pull Requests

AI for 10x Productivity

A Closer Look at PR-Agent by CodiumAI

1. Overview

2. Versatile Git Compatibility

3. Quick Setup Procedure

4. Commands and Usage

5. Use Case

6. Open-source Nature

Exploring GitHub Copilot For Pull Requests

1. Overview

2. Marker Tags for Description Expansion

3. GitHub Integration

4. Proprietary Nature

Comparative Analysis Between CodiumAI's PR-Agent and GitHub Copilot For Pull Requests

1. Specialization vs. Generalization

2. Usability and Integration

3. Extensive Commands vs. Specific Descriptions

4. Flexibility and Customization

5. Command Variety

6. Community Involvement

7. Cost and Accessibility

Conclusion

References

Guide: Creating Temporary Tables in PostgreSQL

Understanding Temporary Tables

Creating Temporary Tables

Conclusion

References

Guide: Creating Views in PostgreSQL

What are Views in PostgreSQL?

Getting Started

Creating Your First View

Best Practices

Conclusion

References

Creating User-Defined Functions in PostgreSQL: A Journey into Customization

Why Create Custom Functions?

Components of a Custom Function

Calling Custom Functions

The Advantages of Custom Functions in PostgreSQL

Conclusion

References

Guide: Creating Triggers in PostgreSQL

Understanding Triggers

The Components of a Trigger

Creating a Trigger

Use Cases for Triggers

Best Practices

Conclusion

References

Guide: Creating Stored Procedures in PostgreSQL

Benefits of Stored Procedures

Creating a Stored Procedure in PostgreSQL

Conclusion

References