Build an MVP for $20 with GitHub Copilot: A Journey – Database Development

Part 2 – Building Database with GitHub Copilot

In Part 2 of our journey, we’ll delve into Database development with GitHub Copilot. From prerequisites to establishing context, we’ll explore how to utilize GitHub Copilot for constructing and updating SQL scripts based on the provided database design.

If you haven’t already, make sure to read Part 1, where I discuss GitHub Copilot products and how you can prepare yourself to join me on this journey to build an MVP using GitHub Copilot.

Pre-Requisites

In our day-to-day tasks as developers or team leads, it’s essential to establish clarity on the design, architecture, or even a basic block diagram before diving into coding for any feature, API, or database. This approach ensures that we’re building precisely what’s required, minimizing the likelihood of future re-work.

When utilizing GitHub Copilot, this prerequisite remains unchanged. Before delving into learning how to generate SQL code with prompts, it’s crucial to take a step back and meticulously outline a list of tables you intend to create along with their schema details, including primary key and foreign key fields, data types, etc.

Watch full video covering topics in this blog

GitHub Copilot Shortcuts

When employing GitHub Copilot (CLI or Chat), you have access to a range of predefined actions that Copilot can execute to provide relevant responses. These actions might include code explanations, code documentation generation, test case generation, or referencing existing code.

You can provide two main shortcuts at the beginning of a prompt to furnish this context to GitHub Copilot.

Shortcut	Purpose
/	doc, explain, fix, help, optimize, tests. above self-explanatory word based on your needs.
#	Tagging file(s) from the current project or solution enables GitHub Copilot to generate code based on the tagged code.

Step-by-Step Guide

Assuming you have a basic understanding of the database schema and GitHub Copilot is enabled, let’s now dive into a step-by-step guide on how I created complete end-to-end database tables for my MVP.

Keep in mind that this is my approach to optimize results with GitHub Copilot, but it’s not the only approach available.

Defining the Standards

Whenever we write code, whether manually or with automation, it’s crucial to establish coding standards. With Copilot, you can define these standards in plain English, including specific naming conventions, conditions based on fields, or any other guidelines you deem necessary. Here are a few of the standards I’ve set for Copilot to generate SQL scripts.

All table names should begin with tbl
use camelCase for table names and field names
all field names should begin with keywords as per datatype below

int - i
datetime - dt
varchar - str
bit - b
float - f
image - img
text - txt

by default and 'id' column will be a primary key with identity set
avoid adding field prefix to primary key column name

Table Scripts as per Standards

With the established standards above, we can now reference the document in our prompts to generate actual table scripts, as follows:

Referring to SQLStandards.txt can you generate table for 'technology' with fields id, name, description,createdon, updatedon

this should generate a code something like below

CREATE TABLE tblTechnology
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    strName VARCHAR(255), 
    txtDescription TEXT, 
    dtCreatedOn DATETIME, 
    dtUpdatedOn DATETIME, 
    bIsDeleted BIT
)

Note how GitHub Copilot generated a script aligned with the standards defined in the SQLStandards.txt file referenced.

Generating documentation of script

Now, to regenerate the comments and documentation for the script, try the following:

/doc script in #scriptfilename

Watch the video below for a detailed walkthrough of how I generated scripts for all tables using GitHub Copilot.

Update Table scripts!

It’s not always feasible to define all standards upfront when it comes to coding and scripting. We’ll frequently need to refine and introduce new standards as the team evolves. Below are the updated standards.

Now, how do we update a previously generated script?

We can update existing tables by referencing the new standards file.

Can you update #tblcertifications.sql with updated standards as defined  by #SQLStandards.txt 

Generate Documentation

You can leverage GitHub Copilot to generate comprehensive documentation for tables, views, or any other database objects. Simply use the /doc command to instruct GitHub Copilot to generate documentation for the referenced file. Below is an example prompt.

/doc can you generate documentation for #tblCertification.sql

Generating a view!

In many cases, creating tables is straightforward. However, the complexity of a database design often arises when creating views that involve multiple joins and conditions.

Can you create a view with #tblMainContent.sql and join #tblRoles, #tblCertifications #tblTechnology.sql 

Conclusion

In this week’s edition, we caught a glimpse of how GitHub Copilot can be utilized to generate database scripts based on offline designs. Leveraging GitHub Copilot for script and code generation not only accelerates the process but also guarantees quality and adherence to standards.

Join me next week to discover how you can utilize GitHub Copilot to build database and business logic services in C#.

Don’t forget to subscribe to my newsletter to stay updated and never miss a blog post!