Generator Mode: Tell AI What You Want

Introduction
Generator modes
Closing remarks

Introduction

Generator modes tell AI what to generate, e.g. generate SQL, fix SQL, optimize SQL or something else. You can easily switch between generator modes to generate exactly what you want.

Below you will see the different generator modes. Some generate quite different output while others are closely related (e.g. generate query and generate optimized query) and enable you to fine tune output. Let's go through the different generator modes and how to use them effectively.

SQL and NoSQL

This is a collection of generator modes focused on generating SQL and NoSQL. Most of them work better by adding your database schema as data source (same goes for choosing GPT-4).

Generate query

Generate SQL or NoSQL queries using natural language. AI will interpret your instructions, e.g. get customers who signed up last month but didn't make a purchase and generate the corresponding SQL using best practices.

Generated SQL query

            
              
            
            SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email,
  c.create_date
FROM
  customer c
  LEFT JOIN payment p ON c.customer_id = p.customer_id
  AND p.payment_date >= c.create_date
WHERE
  c.create_date >= date_trunc ('month', CURRENT_DATE - INTERVAL '1 month')
  AND c.create_date < date_trunc ('month', CURRENT_DATE)
  AND p.payment_id IS NULL
GROUP BY
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email,
  c.create_date;

Note: Generations come explanations which are omitted here for brevity.

Generate optimized query

Like the "Generate query" generator this generator take natural language instructions and returns SQL or NoSQL. The difference is that it will produce performance optimized queries. It can use more complex query patterns. Recommended to add your database schema as data source. Asking the same as above (get customers who signed up last month but didn't make a purchase) this generator mode returns:

Generated SQL query v.1

            
              
            
            WITH
  LastMonthCustomers AS (
    SELECT
      customer_id
    FROM
      customer
    WHERE
      create_date >= date_trunc ('month', CURRENT_DATE - INTERVAL '1 month')
      AND create_date < date_trunc ('month', CURRENT_DATE)
  ),
  CustomersWithPurchases AS (
    SELECT DISTINCT
      customer_id
    FROM
      payment
    WHERE
      payment_date >= date_trunc ('month', CURRENT_DATE - INTERVAL '1 month')
      AND payment_date < date_trunc ('month', CURRENT_DATE)
  )
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email
FROM
  LastMonthCustomers c
  LEFT JOIN CustomersWithPurchases p ON c.customer_id = p.customer_id
WHERE
  p.customer_id IS NULL;

Generated SQL query v.2

            
              
            
            SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email
FROM
  customer c
  LEFT JOIN payment p ON c.customer_id = p.customer_id
  AND p.payment_date >= date_trunc ('month', CURRENT_DATE - INTERVAL '1 month')
  AND p.payment_date < date_trunc ('month', CURRENT_DATE)
WHERE
  c.create_date >= date_trunc ('month', CURRENT_DATE - INTERVAL '1 month')
  AND c.create_date < date_trunc ('month', CURRENT_DATE)
  AND p.payment_id IS NULL;

Generate query and types

Similar to "Generate query" mode it takes natural language instructions but additionally it generates the Typescript return types for the query. So when asking get customers who signed up last month but didn't make a purchase it will add types as well.

Generated SQL query and types

It generates the SQL:

            
              
            
            SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email
FROM
  customer c
  LEFT JOIN payment p ON c.customer_id = p.customer_id
WHERE
  c.create_date >= date_trunc ('month', CURRENT_DATE - INTERVAL '1 month')
  AND c.create_date < date_trunc ('month', CURRENT_DATE)
  AND p.payment_id IS NULL;

And then the type:

            
              
            
            type CustomerWithoutPurchase = {
  customer_id: number;
  first_name: string;
  last_name: string;
  email: string;
};

Explain query

Add the SQL or NoSQL query in the input prompt that you wish explain and AI will generate a detailed explanation. This is great for understanding complex queries and legacy code. If we input the generate SQL query from above it will tell us what is happening.

Explanation example

Add to the input prompt:

            
              
            
            SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email
FROM
  customer c
  LEFT JOIN payment p ON c.customer_id = p.customer_id
WHERE
  c.create_date >= date_trunc ('month', CURRENT_DATE - INTERVAL '1 month')
  AND c.create_date < date_trunc ('month', CURRENT_DATE)
  AND p.payment_id IS NULL;

And AI responds with:

The provided SQL statement is a query that selects a list of customers from a database who were created in the previous month and have not made any payments. Here's a breakdown of the statement:

            
              
            
            SELECT
  c.customer_id, -- Selects the customer ID
  c.first_name, -- Selects the customer's first name
  c.last_name, -- Selects the customer's last name
  c.email -- Selects the customer's email address
FROM
  customer c -- From the 'customer' table, aliased as 'c'
  LEFT JOIN payment p ON c.customer_id = p.customer_id -- Performs a left join with the 'payment' table, aliased as 'p', on the condition that the customer IDs match
WHERE
  c.create_date >= date_trunc ('month', CURRENT_DATE - INTERVAL '1 month') -- Filters customers created on or after the first day of the previous month
  AND c.create_date < date_trunc ('month', CURRENT_DATE) -- Filters customers created before the first day of the current month
  AND p.payment_id IS NULL;

-- Filters out any customers who have made a payment (i.e., only includes customers with no matching record in the 'payment' table)

The LEFT JOIN ensures that all customers from the previous month are included in the result set, even if they have no corresponding entries in the payment table. The WHERE clause filters the customers based on their creation date to include only those who were added in the previous month. Additionally, it checks for customers who have not made any payments by looking for NULL values in the payment_id column from the payment table after the join. If a customer has made a payment, there would be a non-null payment_id, and thus, they would not be included in the results.

Optimize query

Add the SQL or NoSQL query in the input prompt and AI will generate an optimized query.

SQL optimization example

Add to the input prompt:

            
              
            
            SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email
FROM
  customer c
  LEFT JOIN payment p ON c.customer_id = p.customer_id
WHERE
  c.create_date >= date_trunc ('month', CURRENT_DATE - INTERVAL '1 month')
  AND c.create_date < date_trunc ('month', CURRENT_DATE)
  AND p.payment_id IS NULL;

AI responds with:

The provided SQL statement is designed to select customers who were created in the previous month and have not made any payments. Here's a breakdown of the statement:

c.customer_id, c.first_name, c.last_name, c.email: Selects the customer ID, first name, last name, and email from the customer table.
FROM customer c: Specifies the customer table and aliases it as c.
LEFT JOIN payment p ON c.customer_id = p.customer_id: Performs a left join on the payment table to find if there are any payments associated with each customer.
WHERE c.create_date >= date_trunc('month', CURRENT_DATE - INTERVAL '1 month'): Filters customers created on or after the first day of the previous month.
AND c.create_date < date_trunc('month', CURRENT_DATE): Ensures that the creation date is before the first day of the current month.
AND p.payment_id IS NULL: Filters out customers who have made at least one payment.

To optimize the provided SQL statement, consider the following suggestions:

Indexing: Ensure that there are indexes on the columns used in the JOIN condition (customer_id) and the WHERE clause (create_date and payment_id). This will speed up the query execution by allowing the database to quickly locate the relevant rows.
Column Selection: Only select the columns that are necessary. If additional columns are not required for the result set, do not include them in the SELECT clause.
Avoid Functions on Columns in WHERE Clause: Using functions like date_trunc on columns in the WHERE clause can prevent the use of indexes. If possible, pre-calculate the date range and use it in the query.

Here's an improved version of the SQL statement with the above considerations:

            
              
            
            SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email
FROM
  customer c
  LEFT JOIN payment p ON c.customer_id = p.customer_id
WHERE
  c.create_date >= (CURRENT_DATE - INTERVAL '1 month')::date - EXTRACT(DAY FROM (CURRENT_DATE - INTERVAL '1 month')::date) + 1
  AND c.create_date < CURRENT_DATE::date - EXTRACT(DAY FROM CURRENT_DATE::date) + 1
  AND p.payment_id IS NULL;

In this improved version, the date_trunc function is replaced with a calculation that should still use the index on create_date if it exists. The calculation finds the first day of the previous month and the first day of the current month by subtracting the day part of the date from the date itself and adding 1. Casting the CURRENT_DATE to date ensures that the time part is not considered in the comparison.

Tip: If you need suggestions for database indexes then add your database schema as data source, switch to "Generate query" and input Suggest database indexes and it will iterate through your database schema and make suggestions.

Database indexes

            
              
            
            -- Indexes for primary keys
CREATE INDEX idx_actor_id ON actor (actor_id);

CREATE INDEX idx_address_id ON address (address_id);

CREATE INDEX idx_category_id ON category (category_id);

CREATE INDEX idx_city_id ON city (city_id);

CREATE INDEX idx_country_id ON country (country_id);

CREATE INDEX idx_customer_id ON customer (customer_id);

CREATE INDEX idx_film_id ON film (film_id);

CREATE INDEX idx_inventory_id ON inventory (inventory_id);

CREATE INDEX idx_language_id ON LANGUAGE (language_id);

CREATE INDEX idx_payment_id ON payment (payment_id);

CREATE INDEX idx_rental_id ON rental (rental_id);

CREATE INDEX idx_staff_id ON staff (staff_id);

CREATE INDEX idx_store_id ON store (store_id);

-- Indexes for foreign keys
CREATE INDEX idx_actor_info_actor_id ON actor_info (actor_id);

CREATE INDEX idx_address_city_id ON address (city_id);

CREATE INDEX idx_customer_address_id ON customer (address_id);

CREATE INDEX idx_customer_store_id ON customer (store_id);

CREATE INDEX idx_film_actor_actor_id ON film_actor (actor_id);

CREATE INDEX idx_film_actor_film_id ON film_actor (film_id);

CREATE INDEX idx_film_category_category_id ON film_category (category_id);

CREATE INDEX idx_film_category_film_id ON film_category (film_id);

CREATE INDEX idx_film_text_film_id ON film_text (film_id);

CREATE INDEX idx_inventory_film_id ON inventory (film_id);

CREATE INDEX idx_inventory_store_id ON inventory (store_id);

CREATE INDEX idx_payment_customer_id ON payment (customer_id);

CREATE INDEX idx_payment_rental_id ON payment (rental_id);

CREATE INDEX idx_payment_staff_id ON payment (staff_id);

CREATE INDEX idx_rental_customer_id ON rental (customer_id);

CREATE INDEX idx_rental_inventory_id ON rental (inventory_id);

CREATE INDEX idx_rental_staff_id ON rental (staff_id);

CREATE INDEX idx_staff_address_id ON staff (address_id);

CREATE INDEX idx_staff_store_id ON staff (store_id);

CREATE INDEX idx_store_address_id ON store (address_id);

CREATE INDEX idx_store_manager_staff_id ON store (manager_staff_id);

-- Additional indexes that might be useful based on common query patterns
-- (These are hypothetical and would depend on actual query usage)
CREATE INDEX idx_actor_last_name ON actor (last_name);

CREATE INDEX idx_customer_last_name ON customer (last_name);

CREATE INDEX idx_film_title ON film (title);

CREATE INDEX idx_address_postal_code ON address (postal_code);

CREATE INDEX idx_payment_date ON payment (payment_date);

CREATE INDEX idx_rental_date ON rental (rental_date);

Format query

Add the SQL query to be formatted to the input prompt and the query is returned highlighted and formatted. This improves readability and makes it less error-prone.

Fix query

Add the faulty SQL or NoSQL query to the input prompt and AI generates a fixed version.

Fixed query

Add to input prompt:

            
              
            
            SELECT
  a.actor_ida,
  a.first_name1,
  a.last_name,
FROM
  actor a
  LEFT JOIN film_actora fa ON a.actor_id = fa.actor_id
  LEFT JOIN rental r ON fa.film_id = r.inventory_id
WHERE
  r.rental_date IS NUL
  OR r.rental_date < DATE_SUB (NOW (), INTERVAL 1 YEAR)
GROUP BY
  a.actor_id,
  a.first_name,
  a.last_name

And AI responds with (included explanations omitted for brevity):

            
              
            
            SELECT
  a.actor_id,
  a.first_name,
  a.last_name
FROM
  actor a
  LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
  LEFT JOIN inventory i ON fa.film_id = i.film_id
  LEFT JOIN rental r ON i.inventory_id = r.inventory_id
WHERE
  r.rental_date IS NULL
  OR r.rental_date < DATE_SUB (NOW (), INTERVAL 1 YEAR)
GROUP BY
  a.actor_id,
  a.first_name,
  a.last_name;

See full example.

Generate data

Generate data for SQL and NoSQL databases. Usually done to populate database for local development or testing purposes. If you have added your database schema as data source, you can simply instruct it to "Add 10 customers. Add 20 orders." and it automatically pick up the database schema. If not, you can specify columns names like Add 5 customers with name, url, imageUrl, city, country, twitterHandle, createdAt, isBanned.

Generate data example

Add to the input prompt:

            
              
            
            Add 5 customers with name, url, imageUrl, city, country, twitterHandle, createdAt, isBanned. Use actual city and country names from US.

And AI responds with:

            
              
            
            INSERT INTO
  customers (
    name,
    url,
    imageUrl,
    city,
    country,
    twitterHandle,
    createdAt,
    isBanned
  )
VALUES
  (
    'Customer 1',
    'https://www.example.com/customer1',
    'https://www.example.com/customer1/image.jpg',
    'New York City',
    'United States',
    '@customer1',
    NOW (),
    FALSE
  ),
  (
    'Customer 2',
    'https://www.example.com/customer2',
    'https://www.example.com/customer2/image.jpg',
    'Los Angeles',
    'United States',
    '@customer2',
    NOW (),
    FALSE
  ),
  (
    'Customer 3',
    'https://www.example.com/customer3',
    'https://www.example.com/customer3/image.jpg',
    'Chicago',
    'United States',
    '@customer3',
    NOW (),
    FALSE
  ),
  (
    'Customer 4',
    'https://www.example.com/customer4',
    'https://www.example.com/customer4/image.jpg',
    'Houston',
    'United States',
    '@customer4',
    NOW (),
    FALSE
  ),
  (
    'Customer 5',
    'https://www.example.com/customer5',
    'https://www.example.com/customer5/image.jpg',
    'Phoenix',
    'United States',
    '@customer5',
    NOW (),
    FALSE
  );

Data analyst

This is a collection of generator(s) helps you answer questions about your data (e.g. formatted as CSV or Excel or other). Add your data as data source and ask away. A dataset example is the Rolling Stone's 500 Greatest Albums of All Time on Kaggle.

Analyze data

Ask question using everyday language and AI will examine the data and respond with an answer. For the above dataset a questions could be Which artist has the most albums? (will answer "The Beatles") or List years without any albums?.

The answers aren't always 100% correct so please verify the correctness.

Generator Mode: Tell AI What You Want

Table of contents

Introduction

Generator modes

SQL and NoSQL

Generate query

Generate optimized query

Generate query and types

Explain query

Optimize query

Format query

Fix query

Generate data

Data analyst

Analyze data

Other

Freeform

Generate Regex

Generate Excel

Generate Google Sheet

Closing remarks