Create your first database

Let’s build a tiny Instagram: a database with a table for users and a table for their posts. We will use SQLite, because the whole database is a single file and there is no server to set up. The SQL you write here works almost the same in PostgreSQL and MySQL.

Plan your schema

Before writing any SQL, plan your schema: decide what you need to store. Think in things, not screens: each kind of thing becomes a table, and each detail about it becomes a column.

• A user has a username and a name.
• A post has a caption, and it belongs to one user.

Every table also gets an id column, so each row can be found by a number that never repeats.

Tools to draw your schema

Sketching your tables as boxes and lines makes mistakes easy to spot before you build anything. Pen and paper works, and so do these tools:

Start with ChartDB or drawDB: both are free, open source and run in your browser.

Let AI draft your schema

An AI assistant can turn a plain description of your app into a first schema in seconds. All three of these have a free plan:

Copy this prompt, then change the first line to describe your own app:

I am building a simplified Instagram where users write posts and like posts.
Design a database schema for SQLite. I am a beginner, so keep it simple.

For each table, list every column with its data type and its rules
(primary key, NOT NULL, UNIQUE, foreign key).
Explain each relationship between tables in one sentence.
Then give me the CREATE TABLE statements.

AI makes mistakes, so treat its answer as a draft: check every table against your plan, and ask it “why?” about anything you do not understand. Once the schema makes sense to you, paste the SQL into a diagram tool above to see it drawn.

Design the schema

The schema is the plan for your tables: the columns, the type of data each one holds, and the rules it must follow. Here is ours:

The three data types you need to start:

Create the database

Download SQLite if you do not have it; it comes pre-installed on macOS and most Linux systems. Then open a terminal and run:

sqlite3 instagram.db

This opens the SQLite shell for a database called instagram.db. The file is created on disk as soon as you add your first table. Everything you type at the sqlite> prompt runs against this database.

In PostgreSQL or MySQL, a server holds many databases, so you create one by name instead: CREATE DATABASE instagram;

Create the tables

Now turn the schema into SQL. CREATE TABLE takes the table name, then each column with its type and rules:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users (id),
  caption TEXT,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

Each rule maps straight to the schema table:

• PRIMARY KEY makes id the unique number for each row. In SQLite it fills itself in when you add a row.
• NOT NULL means the column is required.
• UNIQUE stops two users from having the same username.
• DEFAULT CURRENT_TIMESTAMP fills in the date and time automatically.
• REFERENCES users (id) makes user_id a foreign key: every post must point to a user that exists.

SQLite only enforces foreign keys when you switch them on, once per connection:

PRAGMA foreign_keys = ON;

Check your tables

SQLite has shortcut commands that start with a dot. .tables lists your tables, and .schema shows how one was created:

sqlite> .tables
posts  users
sqlite> .schema posts
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users (id),
  caption TEXT,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

If both tables are listed, your database is ready. Type .quit to leave the shell; the data stays in instagram.db.

Words to know