Databases for beginners: tables, indexes, and migrations

What a database actually is, in plain English

Picture a giant filing cabinet from an old library. Inside are drawers (those are tables). Each drawer holds cards (those are rows). And every card has the same fields filled in: name, date, phone (those are columns). That’s it. No magic.

That smart cabinet is your database A database is where an app stores everything that matters: users, orders, posts. It survives a restart — unlike whatever is living in the program's memory. . Everything your app has to remember forever — who signed up, what they ordered, which post they liked — lives right here. Close the site, kill the server, trip over a cable and black out half the block — the data stays put.

And the app doesn’t talk to the cabinet in plain human language — it uses a special one: SQL. Think of it as a note to the librarian: “bring me every card where city = Chicago.” As a vibecoder, you don’t need to memorize SQL — the agent or a smart layer writes it for you. But understanding what exactly the agent is asking the cabinet for is wildly useful. Otherwise it’ll one day ask to “take out absolutely everything,” and you won’t even blink.

Why a vibecoder needs to understand the database

Sooner or later your site stops being a pretty picture and starts wanting to remember things: a list of orders, accounts, comments. That’s when the database shows up. And with it comes a whole zoo of bugs that either slow the site down or quietly lose data. Quietly is the worst kind.

Understand how the cabinet works and you win four things at once:

• you don’t panic when the agent drops words like “migration” and “index”;
• you don’t let the agent wipe an entire table with one command out of sheer ignorance;
• you get a fast site, not one where a list takes 10 seconds to load and the user runs off to a competitor;
• you frame the task so the agent gets it right the first time.

The whole difference between “my site sometimes loses orders and lags” and “everything flies and nothing disappears” comes down to three words: column type, index, migration. Let’s go.

How the agent works with the database: the four pillars

1. Tables and column types: the shape of the card

When you create a drawer (a table), tell it right away for each field what goes in it: text, a number, a date, a yes/no. That’s the column type. Pick the wrong type and fixing it later is slow and painful.

The pros have ready-made rules for what to store in what (straight out of the postgres-patterns skill):

2. What an index in a database is

Picture a phone book where the names are all jumbled. To find “Petrov,” you read the whole thing from page one. A thousand pages — a thousand pages of reading for a single name. That’s exactly how a database works without an index. Slow and maddening.

An index An index is a helper table of contents for a column. The database builds it ahead of time and then finds the row you need instantly, without re-reading the whole table from the start. is that alphabetical lookup. You tell the cabinet, “here, keep a separate table of contents for the email field” — and searching by email becomes instant. No more “read a million cards and break a sweat.”

3. What a database migration is

The site grows, and you need to add a new field to the card — say, “phone.” Reaching into the live cabinet by hand is risky: you’re bound to bump something. So structure changes are done with migrations A migration is a step-by-step change to the database structure, following a saved blueprint: add a column, create a table, set an index. It lives in files so the same renovation can be repeated on any server. — “renovation blueprints” written out step by step.

And right here lives the MOST dangerous trap for a vibecoder. The command agents usually use to move those blueprints around is called migrate dev. Under certain conditions it asks permission to wipe the whole database: if the data “doesn’t match the blueprint,” it zeroes out entire tables (straight out of the prisma-patterns skill). One click of “yes” — and so long.

4. Dangerous commands: deleteMany and updateMany

Some commands change or delete many cards at once. Powerful — and therefore treacherous. The two main traps (both from prisma-patterns):

• deleteMany with no condition erases EVERYTHING. You meant to delete one client’s orders, forgot to add “whose, exactly” — and the cabinet silently hauled out the whole drawer. No questions. No “are you sure?” No recycle bin.
• updateMany returns a number, not the records. You bulk-change something and assume you got the updated cards back. What you got was a dry figure: “changed: 5.” Who those five are, you find out with a separate query.

A real-world example: how an online store breaks

You’re building a little online store with the agent. At first everything’s lovely: five products, three orders, fast as a rocket. A month later you’ve got a thousand orders — and here’s the reckoning:

1. The “my orders” page takes 8 seconds to load — because there’s no index on the “buyer” field, and the database re-reads the entire table every single time.
2. In the report, an order total reads “1499.9999998” — because the price was saved as a float instead of numeric.
3. You asked the agent to “delete the test orders,” it ran deleteMany with no condition — and took out all the orders, the real ones included.

None of these disasters is an “AI glitch.” They’re three clear traps, and all three are removed by one well-framed request:

Design a database for an online store. Follow these rules:

Store the price as an exact number for money (numeric), NOT a fractional
float. Store date and time with a time zone.
Put an index on every field we’ll search or sort by:
buyer, creation date, order status.
Any bulk delete or update command must be done ONLY with a condition
for exactly which records we’re working on. No condition — stop.
Use the migrate dev command locally only. For the live server,
prepare migrate deploy. Before deleting anything, show me the plan first.
Explain in plain words what tables you created and what each one is for.

A speed trick: cursor pagination

When a list is long (a feed of posts, orders, products), beginners ask for “give me page 50.” To serve it, the database is forced to count off and flip through the first 49 pages for nothing. The further the page, the harder it wheezes.

The pros use cursor pagination Cursor pagination is paging on the principle of “give me the next 20 after this record,” instead of “give me page number 50.” The database doesn't flip through everything from the start and runs equally fast at any depth. : “give me 20 after the last one shown.” Same speed whether it’s the first page or the thousandth. The agent didn’t suggest it on its own? Just ask: “make the feed page with a cursor, not by page numbers.”

Common beginner mistakes with databases

• Storing money as a float. You’ll get fractional cents and wonky totals. Only numeric.
• Forgetting indexes. Invisible on small data, on big data the site grinds to a halt. Index your search and sort fields.
• Running migrate dev on a live site. It can wipe the whole database. On prod — only migrate deploy.
• Bulk-deleting or changing with no condition. deleteMany with no condition wipes the entire table. Always specify “on whom.”
• Assuming updateMany returns the records. It only returns a number. Pull the updated cards themselves with a separate query.
• Not backing up before a dangerous operation. A backup is your time machine. It’ll save the whole project at least once.