According toStackOverflow'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 whereCody 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-sourceobject-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 comprehensivePostgreSQL 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 andenhance 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
• PostgreSQLinstalled on your computer
• IDE or code editor (this tutorial will useVisualStudioCode(VSCode))

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

Next, you need to installSQLTool’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, clickSave Connection to save your connection settings. Then, clickConnect 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 anaccount 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 ausers table to store user information and aposts 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 ausers table to store information about the bloggers, including their ID, username, email, and the date they joined. You’ll also create aposts 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 theusers table, you would have something like this:

CREATETABLEusers(idSERIALPRIMARYKEY,usernameVARCHAR(50),emailVARCHAR(100),created_atTIMESTAMPDEFAULTCURRENT_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 theposts table will look like this:

CREATETABLEposts(idSERIALPRIMARYKEY,user_idINTREFERENCESusers(id),titleVARCHAR(255),contentTEXT,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);

AI assisted method
Leveraging Cody’schat 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, pressAlt + 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, clickAccept to save it.

You can then run the query by clicking on theRun 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 alast_login column to theusers 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 alast_login column, you would have something like:

ALTERTABLEusersADDCOLUMNlast_loginTIMESTAMP;

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 theusers table:

Add a last_login column to the users table

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

ALTERTABLEusersADDCOLUMNlast_loginTIMESTAMP;

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 tablesusers andposts 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:

SELECTu.username,p.titleFROMusersuJOINpostspONu.id=p.user_idWHEREp.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:

SELECTu.username,COUNT(p.id)ASpost_countFROMusersuJOINpostspONu.id=p.user_idWHEREEXTRACT(YEARFROMp.created_at)=EXTRACT(YEARFROMCURRENT_DATE)GROUPBYu.usernameHAVINGCOUNT(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-usecommands 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, pressAlt + C (or Opt + C), then selectConfigure Custom Commands

Next, select theNew Custom Command option

Then enterPostgresAdmin as the command name.

Next, selectAsk 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 theSelected Code andCurrent File box; this grants the newly created command access to the highlighted source code and the current file you are working with:

Finally, select theWorkspace 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 createdPostgresAdmin 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. WhileCody automates many processes, make sure to always review AI-generated queries, especially for critical operations involving complex logic or sensitive data. GiveCody a try today!