AI Dev Tips #5: AI/ChatGPT Postgres Query Generation from Schema

AI creates QUERIES for your DB schema and Gherkins in 5 minutes.

Let’s take a look translate natural language prompts into basic Postgres database queries using ChatGPT or Claude.

This tutorial will guide you through the steps to create SQL queries quickly, making your development process faster and more efficient.

ChatGPT, Anthropic’s Claude and many other AI LLM chat’s can handle this. I will be using ChatGPT.

Quick Reminder

We are mainly using this process for prototyping. You may be able to use these ideas in production if your use case is fairly simple. Production DBs are can be more complex, requiring a lot more tables/schemas, that you would need to breakdown into smaller tasks.

However, this type of example does get you up and running VERY FAST for prototypes, MVPs (minimum viable product), POCs (proof of concept)!

In this tutorial:

• Recap of creating a schema and fake data with ChatGPT or Claude.
• Creating SQL queries from product scenarios using natural language prompts.
• Making a big list of simple queries based solely on the schema.
• Making some queries based on Gherkins
• Testing and refining those queries in a PostgreSQL database.
• Making Gherkins based on the query/schema (reverse-engineer)
• Tips and best practices for using AI effectively in your development workflow.

Recap

In our previous articles, we used ChatGPT to generate a database schema.

If you did not do this yet — you should, because you need it!

We created some fake data and tested it. Here’s a quick overview of the steps we took:

• Schema Creation: We asked ChatGPT to create a schema for a PostgreSQL database. I used a book store, but you could use something different. It provided us with CREATE TABLE statements for various entities. See: AI Dev Tips 1: Business Idea to PostgreSQL Database Schema in 5 Minutes

• Schema Validation: We copied these statements into pg-sql.com, a tool that helps us validate SQL queries and schemas online. There are other tools you can use too, but this is simple, online and free with no signup
• Fake Data Population: After creating the tables, we generated fake data to populate our database, allowing us to test simpleSELECT queries and other SQL operations. See: AI Dev Tips #4 — ChatGPT Creates Fake Data for our DB Schema

Fake data creation from AI Dev Tips #44

• (OPTIONAL, not needed for this tutorial) Schema to ERD diagrams: We even made some Entiry Resource Diagrams (ERD) with Mermaid. See: AI Dev Tips 3: Create ER Diagrams from a DB Schema with Mermaid Markdown

So at this point you should have a schema and data to work with in pg-sql.com or another tool you are using. Again, pg-sql.com is optional, it’s just an easy free web tool to use for this, but you can use others.

Step-by-Step: Creating SQL Queries from Scenarios

Now, let’s dive into today’s topic: creating SQL queries based on product scenarios and Gherkins using ChatGPT.

Define Your Scenarios

A scenario is a specific requirement that defines what data you need to retrieve from the database. we will start with simple scenarios then try Gherkins later.

Here are some example scenarios provided by a product manager:

• Retrieve a complete list of registered users.
• Retrieve all available products priced below a certain amount.
• Retrieve all orders made by specific users.

Input the Schema into the AI/ChatGPT to remind it

Before asking ChatGPT to generate queries, input the schema again to ensure it is aware of the database structure. This will make the query generation more accurate.

Here’s how to do it:

1. Copy the Schema: Copy the CREATE TABLE statements from your previous session.
2. Input the Schema: Paste the schema into the ChatGPT prompt, with a note stating:

ChatGPT 4o prompt:
I am working with this schema. All queries should relate directly to it. Do not change the schema unless directed. Simply tell me your ready when in memory. <paste in you schema CREATE TABLE and INSERTs that you made earlier>

Also, you could possibly copy-paste the schema from the Schema button of pg-sql.com however, it may not link the tables correctly for foreign keys.

Result:

btw, quick promo: Check out my site SystemsArchitect.io for more Cloud Engineering info, and there is also this link to the store on there too: https://store.systemsarchitect.io

Generate SQL Queries

Now, let’s generate SQL queries based on the scenarios:

IMPORTANT — HEADS UP: Your Postgres queries generated may be different as I am using my schema with a book store for the examples. Obviously if you have a different schema, then you will get different queries and answers.

Simple Query Example
Start with a basic query to get a feel for how ChatGPT responds.

Prompt:

ChatGPT 4o:
Please create a query I can run on my PostgreSQL database for this scenario: retrieve a complete list of registered users.

Put that into pg-sql.com and make sure it runs correctly.

Remember: you need to make sure you have inserted and created you tables from the schema (as in previous steps from the articles linked above)

More Complex Query
Try a scenario that requires user input or specific conditions.

ChatGPT 4o
Please create a query for retrieving all available products priced below a certain amount.

Make sure to replace your_amount or it will not work!

Test and Refine Your Queries

After generating queries with ChatGPT, as we are seeing above, test them in pg-sql.com. Recap:

1. Input the Query: Copy the query from ChatGPT and paste it into the SQL tool.
2. Run the Query: Execute the query to ensure it returns the expected results.
3. Refine if Necessary: If the query doesn’t work or you receive an error (e.g., column not found), modify the query based on the error message and test again. Feel free to ask ChatGPT, Claude or the AI for help in fixing it.

Create Queries from Gherkins

As I have mentioned in past stories, it is a good best practice to use Gherkins whenever possible. Although it adds a very slight amount of additional work, it’s worth it if you ever scale up to more team members, for automation, testing and documentation.

For more info about Gherkins, see: Why use “Gherkins” in Software Development?

Here’s a Gherkin we can use as a test:

Feature: Retrieve all available products priced below 20

Scenario: Product manager retrieves a list of books priced below a specified amount
  Given the bookstore database contains a collection of books
   When the product manager queries the list of books with a price below "your_amount"
   Then the system returns a list of books with all attributes in that table
   And each book in the list has a price that is less than 20

Output: You get the same DB query as we did above.

Generate Multiple Queries at Once

For efficiency, we can also try to put in multiple Gherkins and it you can ask ChatGPT to generate multiple queries at once.

Now do you see why we like Gherkins?

The PRODUCT MANAGER/Analyst is giving us the “code” for the query!!!!

• Input Multiple Scenarios: Copy and paste all scenarios into ChatGPT and request corresponding SQL queries for each.
• Review Output: ChatGPT will provide a list of queries, which you can then test individually.

ChatGPT 4o:
Create queries from these Gherkins:

Feature: Count the number of books created on each day within a date range

  Background:
    Given the database is filled with test data

  Scenario: Count books created between January 1, 2022, and January 10, 2022
    When I request a count of books created for each day between January 1, 2022, and January 10, 2022
    Then I should receive a list where each entry contains a date within that range and the number of books created on that date

Feature: Get the list of users

  Scenario: Retrieve a complete list of registered users
    When I request a list of all registered users
    Then I should receive a complete list of all users in the system

  Scenario: Retrieve all available products priced below a certain amount
    When I request a list of products priced below a set limit
    Then I should receive a list containing only the products priced below the specified limit
affecting results.

Feature: Get orders by a user

  Scenario: Retrieve all orders made by a specific user
    Given a specific user is chosen
    When I request a list of all orders made by this user
    Then I should receive a list containing only orders made by the chosen user

Output:

* REMEMBER

You may have to change variables to test this , for example above “your_amount” and “your_user_id”

I have done so.

In the pg-sql.com tool:

See there was one order showing in the query result, as expected.

Create Gherkins from Queries

ChatGPT 4o prompt:
Can you give me a gherkin for this query:
SELECT SUM(quantity) FROM OrderItems WHERE book_id = 1;

As you can see, if we have a list og queries, we can reverse-engineer the Gherkins!!!

This opens an incredible amount of opportunities for documentation and collaboration with non-technical users.

Enjoy!

Tips and Best Practices

• Keep Prompts Concise: Shorter prompts are more likely to generate accurate results. If your prompt is too long, ChatGPT might not complete the response.
• Be Specific: The more detailed you are with your prompt, the more tailored the query will be. This can help avoid generic outputs.
• Use Gherkin Scenarios: If you have access to product managers or UX team members, collaborate with them to create Gherkin scenarios. These structured descriptions can help in crafting more precise prompts.
• Validate Scenarios: Always validate scenarios with your team to ensure the queries meet the business requirements. This reduces the chances of errors and miscommunications.

By following this tutorial, you’ve learned how to use ChatGPT to quickly generate SQL queries from natural language prompts, helping to streamline your development workflow. Remember to share this tutorial with friends and colleagues, and subscribe for more tips on leveraging AI in development.

Check soon for the next AI Dev Tips tutorial going in to much more complex queries.

I have a couple new Ebooks you may want to check out:

Ebook: Buy my Cloud Audit ebook / store: SystemsArchitect Store

About me

I’m a cloud architect, senior developer and tech lead who enjoys solving high-value challenges with innovative solutions.

I’m always open to discussing projects. If you need help, have an opportunity or simply want to chat, you can reach me on X/Twitter @csjcode and same username gmail.

I’ve worked 20+ years in software development, both in an enterprise setting such as NIKE and the original MP3.com, as well as startups like FreshPatents, SystemsArchitect.io, API.cc, and Instantiate.io.

My experience ranges from cloud ecommerce, API design/implementation, serverless, AI integration for development, content management, frontend UI/UX architecture and login/authentication. I give tech talks, tutorials and share documentation of architecting software. Also previously held AWS Solutions Architect certification.

Cloud Ebook Store — check for 50% off sales cloud architect and engineering books, “Cloud Metrics” (800 pages+) and “Cloud Audit” (800 pages+) and more — https://store.systemsarchitect.io

And my website:

Recently I’m working on Instantiate.io, a value creation experiment tool to help startup planning with AI. I write a reference manual on cloud metrics.

Also, an enthusiast of blockchain, I’m active working on applications in the innovative Solana blockchain ecosystem.

Thanks for reading ! 🥰 If you like this topic, please give some claps (it’s free), bookmark, add to your list and share with colleagues. I follow back. I’m here to meet people, learn, and contribute. 🥰