Steps to manipulating Postgres database with AI

According to StackOverflow's 2024 developer survey, PostgreSQL's popularity has steadily risen, from 26% in 2017 to 49% in 2024, making it the most popular database management system and a go-to choice for developers and enterprises.

However, managing complex queries and database operations in PostgreSQL can be tedious and time-consuming, especially when dealing with complex syntax and logic. That is where Cody comes in—an AI programming assistant designed to help you quickly understand, write, review, and optimize complex database queries and operations.

This guide will explore how Cody can be paired with PostgreSQL to simplify tasks like optimizing queries and managing complex database operations such as table creation and data retrieval. To set the stage for how Cody can improve your PostgreSQL workflow, let’s take a brief look at PostgreSQL itself.

What is PostgreSQL?

PostgreSQL, also known as Postgres, is a powerful, open-source object-relational database management system (ORDBMS) known for reliability, feature robustness, and performance. Its compatibility with various platforms and programming languages further enhances its appeal, making it easier for developers to integrate PostgreSQL into different technology stacks. Developers and businesses also widely use PostgreSQL databases because they can handle complex queries and large amounts of data easily.

One of PostgreSQL's key strengths is its support for various data types. PostgreSQL supports common data types like integers and text and more advanced types like JSONB and arrays, making it adaptable to different application needs. This flexibility in handling PostgreSQL data types ensures that developers can structure their data in ways that best suit their projects.

Another advantage is the active PostgreSQL community, which continuously contributes to the database's development and improvement. The comprehensive PostgreSQL documentation the community provides is helpful, and it offers detailed guidance on how to insert data, delete data, and perform other important operations. Whether you are a PostgreSQL newbie or have years of experience, the documentation provides clear guidance on best practices, helping you manage operations and enhance your database performance.

With that in mind, let's move on to setting up the development environment, where you'll see how Cody can assist in managing your database operations.

Setting up the development environment

To follow along with this tutorial, you’ll need to have:

• Basic understanding of PostgreSQL
• PostgreSQL installed on your computer
• IDE or code editor (this tutorial will use Visual Studio Code (VSCode))

To proceed, connect your IDE (VSCode) to your PostgreSQL server. To do this, you need to install the SQLTools extension from the VSCode extensions section:

Next, you need to install SQLTool’s driver extension:

To complete the connection process, click on the SQLTools icon and provide the following:

• Connection name: identifier for a database connection
• Server address: server address of the PostgreSQL server
• Port: port to connect to the PostgreSQL server
• Database: name of the database
• Username: PostgreSQL server username

Localhost is used for the "server address" field because the PostgreSQL server runs locally.

Next, click Save Connection to save your connection settings. Then, click Connect to connect to PostgreSQL:

With that, your PostgreSQL setup is complete:

Now, to use Cody, you have to install and setup the Cody extension in VSCode:

Cody supports other IDEs, including IntelliJ (or any other JetBrains IDE) and Neovim

After installing the Cody extension, you’ll receive a prompt to authenticate. Create an account using one of these authentication methods - GitHub, GitLab, or Google.

To fully explore how Cody can enhance database operations, let's look into the specifics of creating, modifying, and even deleting tables in a PostgreSQL database.

Executing PostgreSQL operations

Using the scenario below, let's explore how to handle essential operations in PostgreSQL, starting with traditional SQL commands and then showing how Cody simplifies the process:

Build a blogging platform: For this platform, you'll need to set up a users table to store user information and a posts table to store blog entries. As the platform evolves, you'll need to modify your database structure by adding or deleting columns based on new features or removing tables that are no longer needed.

Creating tables in the Postgres database

First, you’ll need to create a users table to store information about the bloggers, including their ID, username, email, and the date they joined. You’ll also create a posts table to store blog entries, including the post content, title, and the ID of the user who created the post.

Manual method
Writing SQL commands to create tables requires a solid understanding of SQL syntax. When dealing with complex table structures, this can be challenging for beginners and error-prone for experienced developers. A minor syntax mistake can lead to errors that are sometimes difficult to debug.

To create the users table, you would have something like this:

     CREATE TABLE users ( 
          id SERIAL PRIMARY KEY, 
          username VARCHAR(50), 
          email VARCHAR(100), 
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
    );

In complex projects like a blogging platform, manually managing table relationships (such as foreign keys) adds an additional layer of complexity, increasing the chance of mistakes that may go unnoticed until runtime.

Creating the posts table will look like this:

    CREATE TABLE posts (
        id SERIAL PRIMARY KEY,
        user_id INT REFERENCES users(id),
        title VARCHAR(255),
        content TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

AI assisted method
Leveraging Cody’s chat feature simplifies table creation by interpreting natural language commands and generating the appropriate SQL. This reduces the need to remember the precise syntax, making the process more intuitive and significantly faster.

In VS Code, press Alt + K (or Opt + K) to open the command palette, and type the prompt below:

write a postgresql query to create a users table with id, username, 
email, and a created_at timestamp. 
Also, create a posts table with id, user_id, title, 
content, and created_at.

 

Once the code has been generated, click Accept to save it.

You can then run the query by clicking on the Run on active connection button.

Modifying tables

As your blogging platform expands, you may realize that you need to track users' last login times. To do this, add a last_login column to the users table.

Manual method
Modifying existing tables can be risky, as any mistake might result in data loss or unintended structural changes. As a developer, you must be very precise when altering tables to avoid breaking dependencies within the database schema.

To add a last_login column, you would have something like:

    ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

AI-assisted method
Cody enhances the experience of modifying tables by allowing you to simply describe what changes are needed. This saves time and ensures that the query generated will not overlook important details like data types or column constraints.

You can use the following prompt to modify the users table:

Add a last_login column to the users table

Cody generates the query for you, as demonstrated in this video:

 

    ALTER TABLE users
    ADD COLUMN last_login TIMESTAMP;

Writing complex queries

You often need to write complex queries to get meaningful data from your database. For instance, for the blogging platform, you might need to identify the most active users, analyze post engagement, or retrieve data filtered by specific criteria like date ranges or popularity. The PostgreSQL database offers powerful querying capabilities, but constructing these complex queries manually can be challenging.

Performing joins with conditions

Imagine wanting to retrieve a list of posts along with the usernames of the authors who posted them, but only if those posts have more than 100 likes. This involves combining data from two tables users and posts with an additional condition.

Manual method
To join tables with additional conditions requires understanding how to structure the query efficiently. Managing table joins with additional conditions can lead to complex queries, especially when dealing with large datasets or multiple join conditions. It’s easy to make mistakes when specifying the relationships between tables or writing the conditions.

Using the manual method to get the titles of posts and the usernames of the authors, but only for posts with more than 100 likes, you would have this:

    SELECT u.username, p.title
    FROM users u
    JOIN posts p ON u.id = p.user_id
    WHERE p.likes > 100;

AI assisted method
To do this with Cody, all you need to do is prompt it:

Query to get the titles of posts and the usernames of their authors, but only for posts with more than 100 likes.

Cody's response:

Combining aggregations and conditions

Consider finding users who have written more than five posts but only those published in the current year. This requires combining aggregation with conditional filtering.

Manual method
Managing complex conditions with aggregation functions can be challenging, as the logic must be applied before and after the aggregation.

Doing this manually would look like this:

    SELECT u.username, COUNT(p.id) AS post_count
    FROM users u
    JOIN posts p ON u.id = p.user_id
    WHERE EXTRACT(YEAR FROM p.created_at) = EXTRACT(YEAR FROM CURRENT_DATE)
    GROUP BY u.username
    HAVING COUNT(p.id) > 5;

AI assisted method (Cody custom commands)
With Cody, you can simplify this process by using its commands feature, which offers ready-to-use commands for common tasks such as:

• generating code documentation
• improving code quality
• fixing and generating new code

Additionally, for an efficient workflow, Cody allows you to create custom commands that can automate tasks related to your database interactions. To create a custom command:

First, press Alt + C (or Opt + C), then select Configure Custom Commands

Next, select the New Custom Command option

Then enter PostgresAdmin as the command name.

Next, select Ask for the command mode, which controls how Cody interacts with the editor.

Now enter the prompt below:

Provide query to find users who have written more than five posts this year.

Next, check the Selected Code and Current File box; this grants the newly created command access to the highlighted source code and the current file you are working with:

Finally, select the Workspace Settings to store the command for this workspace:

Now, you can use the custom command to find users who have written more than five posts, but only those published in the current year.

To do this, click the Cody extension icon and select your newly created PostgresAdmin command:

Manually writing complex queries offers full control but can be error-prone and time-consuming, especially for more intricate operations like joins, subqueries, and conditional filtering. Cody simplifies these processes by interpreting natural language descriptions and generating accurate queries. This reduces the likelihood of errors and speeds up the workflow, making managing even the most complex database operations easier.

Wrapping up

Using AI coding assistants like Cody to assist with PostgreSQL database operations can improve productivity and reduce the challenges associated with manual coding. This allows developers to focus more on higher-level tasks, such as optimizing the platform and implementing new features, instead of getting bogged down by complex SQL syntax.

However, it’s essential to approach AI-assisted database management with a balance of trust and caution. While Cody automates many processes, make sure to always review AI-generated queries, especially for critical operations involving complex logic or sensitive data. Give Cody a try today!