SQLite Test Client

Creating a quick fix for my team's problem

The Problem

In my Database System's course at the University of Washington Bothell, my team was tasked with designing, implementing, populating, and querying a database in SQLite3. As our team started establishing norms and expectations, I was nominated unanimously as the team's Project Manager. There was a team member that was not a CS-major. SQLite was a steap learning curve for them. As the PM, I agreed to complete a code review of every git branch before approving a merge to main.

Our professor had taught us only to use the command line interface for SQLite3. We were instructed to write all our database code in a text file and then copy-paste the statements line-by-line into the command line. At that time, we could see line-specific error messages and correct our statements in the text file.

I very quickly realized that the system we were taught would not allow for quick and efficient code reviews when merging branches with several files and hundreds of lines of code. So, I developed a solution.

The Solution

Structuring My Approach

I knew that I would need to solve a few very specific problems to be able to sustainably carry out my duties as PM. I would need to automate much of my responsibility and be able to quickly run unit tests, integration tests, and regression tests.

I determined my primary goals:
  1. Find a tool that supports syntax highlighting in SQLite3
  2. Get line/statement specific error reporting on a SQLite script without needing to copy/paste each statement
  3. Test the result of each supported query against the expected result
  4. Drop and rebuild the database as errors are discovered and corrected

Choosing My Tools

For my first goal, I learned that I could see syntax highlighting in VSCode when I wrote SQLite3 scripts in a file with a ".sql" extension. I also learned how to use exisiting extensions for SQLite in VSCode that allowed for database visualization and running entire scripts with the click of a button. I created a tutorial on how to use VSCode for SQLite development and sent the video to my classmates and professor.

Though using VSCode was a huge step up from the command line alone, it did not solve goals 2 - 4. After doing some research, I discovered that Python, the language I was studying at the time, supported a simple interface with SQLite3. I decided to use python to write code that would run tests on my team's database.

Implementing My Solution

Using Python, I wrote code that supported:
  • Dropping all tables from a database
  • Creating tables in a database using a .sql file with CREATE statements
  • Populating tables using .sql files with INSERT statements
  • Querying tables using .sql files with SELECT statements
  • Testing query results against the expected results
  • Running line/statement specific error-reporting on any .sql file
This code became essential for my team's workflow, so I abstracted it to work on any database, wrote detailed documentation on how to use it, and offered it to my professor to use as a grading tool or student resource.

Github Repo