Turning a Markdown Spec Into a Database - With Test Data!

I created 10 or so workshop outlines during my time at Microsoft. Most started out at hour-long presentations that I built on top of and others got into the weeds quickly. Some were completely customized for the team I was working with too.

This workshop is for people getting started and has super simple steps for seeing what AI can do for you. It's a shorter one and usually lasts for an hour two if we stretch it, which is easy to do because it's damned fun.

I'm once again using Copilot as that's what I'm used to working with, but I might do a variation of this with Python and Claude Code, so look for that in the future.

Focusing on Iterative Exploration

That's the simple idea behind this workshop: using AI to think through a problem domain. I'm using my old go-to here, an eCommerce application, but you can apply this process to anything your trying to build out, including:

• A new application you're starting from scratch.
• A new feature you want to add to an existing application.
• A rewrite/refactor

The idea is straightforward: you write out what you want to do in a spec, just like you normally would, but this spec is for your AI tool of choice. Keep things terse and simple with lots of bullet points, like this:

# The Red:4 Store

This is an ecommerce store that will deliver both digital and physical goods. To do this we need to track:

 - `products` with a sku, name, price, description, and type (digital, hardware, kitchenware, clothing, etc).
 - `inventory` that tracks products and their stock levels as well as location. For digital products, this should indicate download URL.
 - `orders` with a unique, random `number`, `total` in pennies, `date`, `status`, `transaction_id` if it's checked out.
 - `customers` with unique email, name, lifetime value

This is a decent place to start, and the most important thing is that we can iterate on this as we go along. Copilot (the tool I'll be using) will do the "heavy lifting" on this, creating the boilerplate code that slows us down.

If we miss something, we can stop and go backwards and redo. It's OK! Copilot is extremely fast compared to me writing out all this code, so redoing something is not a complete loss. In fact, I think it's encouraged.

Clearer Instructions Mean Smaller Prompts

The more context you add to your instruction set, the smaller your prompts can be. If you're using Copilot, these instructions go into the .github/copilot-instructions.md file. For Cursor, you use Cursor Rules. If you're using Claude Code, you pop the instructions into CLAUDE.md in the root of your app.

Just about every AI coding tool out there gives you the ability to prepend text to your prompts, and it makes all the difference.

Here's the prompt I used to get started in the video below:

Generate the initial SQL file and put into db/schema.sql

That's it! When working with file creation you do need to get specific with the LLM, ensuring you give it a path and extension otherwise it'll make up whatever it thinks you want.

Once the schema is generated, go over it (as you should) and, if you find any issues, update your instructions.

For instance: I like integer primary keys called id that are auto-incremented. I like using text instead of varchar in every case (it's a PostgreSQL thing), and a few other preferences.

This type of thing is perfect for custom instructions, so we can add it in, right below our spec:

## Database

 - Database tables will be lower cased using underscores.
 - Every table will have an auto increment integer primary key called `id`.
 - `users` will not store passwords, only social login information as well as magic email links.
 - `char`, `varchar` and `nvarchar` are never to be used for string fields, only `text`.
 - Every table should have `created_at` and `updated_at` timestamps.
 - Many to Many relationships will have compound primary keys, never a single ID with compound unique.

You can then add coding standards as well as test preferences. I'll devote an entire post to that one as testing is one area where AI tools can really help.

Here's a gist with the markdown snippets I use, which vary from project to project of course. These get me started, which is nice:

Code style Snippets

Code style Snippets. GitHub Gist: instantly share code, notes, and snippets.

Gist262588213843476

Generating Test Data

A lot of people don't know you can do this - but generating test data with a tool like Copilot and Claude Sonnet 4 (my LLM of choice) is ridiculously simple:

Create some test data for the database and put it in db/test.sql. Make sure the data is obviously fake and have some fun, but not too much fun. Add 10 products and 10 orders with necessary accompanying data.

After a few seconds, this is what I got back (a small sample of the whole thing):

INSERT INTO products (sku, name, price, description, type, download_file, created_at, updated_at) VALUES
('DIG-001', 'Quantum Meditation App', 999, 'Achieve inner peace through quantum entanglement guided meditation sessions', 'digital', 'quantum-meditation-v2.3.zip', '2024-01-10 09:00:00', '2024-01-10 09:00:00'),
('HW-002', 'Self-Stirring Coffee Mug', 2495, 'Never worry about stirring your coffee again with this battery-powered stirring mug', 'hardware', NULL, '2024-01-12 10:15:00', '2024-01-12 10:15:00'),
('KIT-003', 'Levitating Spice Rack', 8999, 'Magnetic levitation technology keeps your spices floating in perfect organization', 'kitchenware', NULL, '2024-01-15 11:30:00', '2024-01-15 11:30:00'),
('CLO-004', 'Invisible Socks', 1299, 'So comfortable you will forget you are wearing them (Warning: Actually invisible)', 'clothing', NULL, '2024-01-18 14:45:00', '2024-01-18 14:45:00')

At that point you can step into the terminal and, once again, ask Copilot to generate the command for you to create a SQLite database with the schema and test data (use CMD or CTRL-I):

If you're interested, I recorded doing all of this below. Total time IRL is about 16 minutes, but I chopped some elapsed time out for watchability.

Hope this helps!

Rob