AWS Database Blog

Accelerate database development in Amazon RDS and Amazon Aurora with Amazon CodeWhisperer

As organizations continue to scale applications, the need for database developers to quickly pick up new skills becomes increasingly important. Amazon CodeWhisperer is an AI-powered productivity tool for the integrated development environment (IDE) and command line that helps improve developer productivity by generating code recommendations based on your comments in natural language and code in the IDE. This allows you to accelerate database development by using the SQL code generation capabilities of Amazon CodeWhisperer. The SQL code generated can be applied to various database engines such as PostgreSQL, SQL Server, Babelfish for Aurora PostgreSQL, and MySQL.

In this post, we show you how to use Amazon CodeWhisperer to accelerate database development for an Amazon Relational Database Service (Amazon RDS) for MySQL database. You can expect a similar experience using other Amazon RDS and Amazon Aurora database engines. We use a sample course registration database to cover the basics of using Amazon CodeWhisperer for tasks such as performing DDL operations, creating simple or complex queries, indexing, and aggregation.

Solution overview

Amazon CodeWhisperer integrates directly with a supported IDE that the database developer chooses. Amazon CodeWhisperer can generate the SQL code with a developer prompt, which can then be applied to the RDS for MySQL database cluster directly or through a tool like MySQL Workbench.

The following diagram outlines the proposed solution architecture.

Prerequisites

Make sure that you have the following prerequisites:

Additionally, review the Amazon CodeWhisperer user commands.

Use Visual Studio Code for SQL generation

This section introduces the basics of interacting with Amazon CodeWhisperer in VS Code for SQL development.

Amazon CodeWhisperer uses artificial intelligence to provide code recommendations and is non-deterministic. This code shown in this post may differ from what you get from Amazon CodeWhisperer locally in your IDE.

Complete the following steps:

  1. Create a new file in VS Code and make sure the language mode for the file is selected as SQL (example.sql).
  2. In the empty file, add a natural language comment for the database schema and table that you are building within a /* */ block, as shown in the following screenshot.
  3. Press Enter after the block ends, and it will populate the SQL command per the instructions you provided.
  4. You can edit the SQL command if required, or press Tab to accept.

You can write more instructions to create other DDL and DML statements in the same file. You can also copy the SQL commands generated and run them in the RDS for MySQL database by connecting using any MySQL client.

Database development using Amazon CodeWhisperer

We use sample queries to depict how SQL is generated. We recommend that you test them or create your own dataset per your requirements for production workloads.

In this post, you learn how to use Amazon CodeWhisperer to do the following tasks on a sample course registration database for students in a college. For each of the tasks, we provide a prompt for a solution as a simple language input and look at the provided SQL response. We recommend doing all parts in the same SQL file as shown in the following sections.

Create a database and schema tables

First, let’s build a college student database to where you’ll store student information and course information and provide details about the courses that have been registered by students. Before constructing the database, you need to establish the connection to the RDS database instance from VS Code. You can use VS Code extensions like MySQL to connect to the database instance.

Let’s start by prompting Amazon CodeWhisperer in VS Code as follows:

/* Create a database for a college that contains three tables
The students tables contain student id, first name, last name, age and major,
courses table contains course id, course name, description and credits,
and finally the registration table contains the student id, course id and date of registration. The registration table contains foreign keys on student id and course id
*/

We get the following response.

Amazon CodeWhisperer successfully generated all three tables we defined in natural language along with the applicable attributes.

Next, let’s run the SQL commands generated from the prompt in VS Code to create the database and tables.

As shown in the code, we were able to successfully run the SQL commands generated by Amazon CodeWhisperer to create all three tables.

The following diagram describes the tables created within the database. The students table has a one-to-many relationship with the registration table, because one student can register multiple times for multiple courses. Similarly, the courses table has a one-to-many relationship with the registration table, because each course can have multiple registrations by multiple students.

Insert data

Let’s insert five sample records to each of the tables you created. You can edit the queries to insert your own specific information. Use the following prompt in VS Code to insert five records into each of the tables:

/*Insert 5 records in each table*/

We get the following response.

Amazon CodeWhisperer successfully generated an insert to place five records into each table.

Let’s run the SQL code to insert records into the tables.

As shown in the code, we successfully ran the SQL commands generated by Amazon CodeWhisperer to insert the five records into each table.

Retrieve data

The college advising department is trying to track details on student enrollment for course registration. Use the following prompt to retrieve students who are majoring in Computer Science:

/*Write a query to retrieve all the students who are majoring in Computer Science*/

We get the following response.

From the sample response, Amazon CodeWhisperer successfully generated the query for all students majoring in Computer Science. After running the code, we get the student currently enrolled in Computer Science.

Now, let’s generate another query to retrieve data to show all student names with course ID and registrations. Prompt Amazon CodeWhisperer as follows:

/*Write a query to display all student names with course ids and names they have registered*/

We get the following response.

After running the prompt, we get a list of all student names with course IDs and registered student names. In this case, Amazon CodeWhisperer generated table joins to successfully generate the query prompted.

Perform updates and deletes

Now we want to update the course name from Discrete Mathematics to Mathematics 101 in the courses table and update the course ID in the registration table. Prompt CodeWhisperer as follows:

/*Update the course name Discrete Mathematics to Mathematics 101 as well as the registration*/

We get the following response.

From the sample response, Amazon CodeWhisperer was able to successfully update the course name along with the registration.

Create an index

As more data gets added, the responses can become slow if they are doing entire table scans. Indexes are used to find rows with specific column values quickly. Without an index, the database must begin with the first row and then read through the entire table to find the relevant rows. Let’s create an index on a column from the students table.

To use Amazon CodeWhisperer to generate the SQL statement to create the index, use the following prompt:

/*Create an index on the major column*/

We get the following response.

From the sample response, the index on the students table by major was generated successfully.

Clean up

To avoid incurring future charges, delete the resources you created as part of this post. If you created an RDS database, delete the database.

Conclusion

In this post, we showed you how Amazon CodeWhisperer can help with a wide variety of SQL tasks, from creating tables and inserting data to running complex queries and managing indexes. Although there is still work for the developer to review and adjust the generated code as needed, Amazon CodeWhisperer eliminates much of the manual coding effort. This allows developers to focus their time on higher-value tasks like design and optimization rather than syntax.

For more information about Amazon CodeWhisperer for database development, refer to the CodeWhisperer for SQL workshop.

About the authors


Brendan Jenkins is a Solutions Architect at Amazon Web Services (AWS) working with Enterprise AWS customers providing them with technical guidance and helping achieve their business goals. He has an area of specialization in DevOps and Machine Learning technology.

Fahim Sajjad is a Solutions Architect at Amazon Web Services. He helps customers transform their business by helping design their cloud solutions and offering technical guidance. Fahim graduated from the University of Maryland, College Park, with a degree in Computer Science. He has a deep interest in AI and machine learning. Fahim enjoys reading about new advancements in technology and hiking.

Shivam Gulati is a Senior Data Architect at Amazon Web Services. He works with customers to design and build highly available and scalable database and data analytics solutions in the AWS Cloud. Outside of work, he enjoys traveling, photography, and exploring new places with friends and family.

Riya Dani is a Solutions Architect at Amazon Web Services (AWS), responsible for helping Enterprise customers on their journey in the cloud. She has a passion for learning and holds a Bachelor’s and Master’s degree from Virginia Tech in Computer Science with focus in Deep Learning. In her free time, she enjoys staying active and reading.

Abdullah Khan is a Solutions Architect at AWS. He attended the University of Maryland, Baltimore County, where he earned a degree in Information Systems. Abdullah currently helps customers design and implement solutions on the AWS Cloud. He has a strong interest in artificial intelligence and machine learning. In his spare time, Abdullah enjoys hiking and listening to podcasts.

Madhavi Evana is a Solutions Architect at AWS, primarily supporting the Cloud Sales Center (CSC) to assist customers in implementing solutions on the cloud. She graduated with a master’s degree in Data Analytics from George Washington University with a focus on Artificial Intelligence and Machine Learning. Her area of specialization is in AI and ML. She enjoys playing tennis and gardening.