Tables and types

In the last lesson, we got comfortable with the sqlite3 shell. We know how to open a database, run commands, and read the output. But so far the database is empty. There is nowhere to put data, and nothing to query. Before any of that can happen, you need a table. In this lesson, we will create our first one, learn how SQLite thinks about types, and figure out how to decide what deserves a table in the first place.

Choosing what gets a table

Before you write any SQL, stop and think about what you are modeling. A table represents a noun, a thing you want to store and query independently. The question to ask yourself is: “Would I ever want to list, search, or count these things?” If the answer is yes, it should be a table.

Users. You list users, search by email, count active users. That is a table.

Email addresses. Usually not. An email belongs to a user and is not queried on its own. It is a column on the users table.

Orders. You list orders, filter by status, calculate totals. That is a table.

Order status. Maybe. If you have 3 statuses (pending, shipped, delivered), a TEXT column is fine. If you have 20 statuses with descriptions, colors, and sorting rules, a separate table might make more sense.

The line is not always clear, but the principle is simple. If you need to query it independently, give it a table.

Creating your first table

Let’s create a users table. Open the SQLite shell and type the following:

Code along

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

That is it. You just created a table with four columns. Let’s break down what each piece means, because even this small statement packs in a lot.

id INTEGER PRIMARY KEY creates a column called id that holds whole numbers. The PRIMARY KEY part means this column uniquely identifies each row. SQLite will automatically assign an incrementing number to id if you do not provide one when inserting. We will cover primary keys in much more detail in a later lesson.

email TEXT NOT NULL creates a text column that cannot be empty. The NOT NULL part means SQLite will reject any insert that does not include an email.

name TEXT NOT NULL works the same way. Every user must have a name.

created_at TEXT NOT NULL DEFAULT (datetime('now')) is a text column with a default value. If you insert a user without specifying created_at, SQLite evaluates datetime('now') at insert time and fills in the current timestamp automatically.

You might notice keywords like NOT NULL and DEFAULT in that statement. Those are called constraints. They are rules that protect your data from bad inserts. We will not go deep on them here because the next lesson covers every type of constraint in detail. For now, just know that NOT NULL means “this column must always have a value” and DEFAULT means “use this value if one is not provided.”

Type affinities

If you have used other databases like PostgreSQL or MySQL, you are used to strict types. A column declared as INTEGER only accepts integers, and a column declared as VARCHAR(255) only accepts strings up to 255 characters.

SQLite works differently. It uses something called type affinity. Instead of enforcing a strict type, SQLite recommends a type for each column but lets you store any type of value in any column. You could put the text "hello" into an INTEGER column and SQLite would not complain.

This is unusual, and it trips up a lot of people. In practice though, you should still declare types on your columns and respect them. The type declaration serves as documentation, and well-behaved application code should always insert the correct type.

SQLite has five storage classes:

NULL is the absence of a value. Not zero, not an empty string. It means “no value at all.”

INTEGER is a whole number. It can be 1, 2, 4, 6, or 8 bytes depending on the size of the value. SQLite handles this automatically.

REAL is a floating-point number. An 8-byte IEEE floating point. Use it for things like coordinates, percentages, or prices (though for money you might prefer storing values in cents as INTEGER to avoid floating-point rounding issues).

TEXT is a string. It can be any length. SQLite stores it using the database encoding (UTF-8, UTF-16BE, or UTF-16LE, but almost always UTF-8).

BLOB is raw binary data. Stored exactly as it was input. You would use this for things like file contents or images, though in practice most people store files on disk and keep only a file path in the database.

In day-to-day work, you will use INTEGER, TEXT, and REAL constantly. BLOB is rare. And NULL is not a type you declare on a column. It is a special value that any column can hold, unless that column has the NOT NULL constraint.

Dates and times

SQLite does not have a native DATE or DATETIME type. This surprises a lot of people, but it is not a problem in practice. You store dates as TEXT in ISO 8601 format.

2024-01-15
2024-01-15T10:30:00Z
2024-01-15 10:30:00

Why ISO 8601? Because it sorts correctly as plain text. The string 2024-01-15 comes before 2024-12-25 in alphabetical order, which means ORDER BY created_at gives you the right chronological order without any extra work.

SQLite also has built-in date and time functions:

SELECT datetime('now');               -- current date and time in UTC
SELECT date('now');                    -- current date only
SELECT datetime('now', '+7 days');    -- one week from now
SELECT datetime('now', '-1 hour');    -- one hour ago

These functions understand ISO 8601 strings and can manipulate them. So even without a native date type, SQLite handles dates and times well.

You can also store dates as INTEGER using Unix timestamps (seconds since 1970-01-01). Both approaches work. TEXT in ISO 8601 is more human-readable. INTEGER timestamps are easier to do arithmetic on. Either is fine, just pick one and be consistent across your schema.

Primary keys

Every table should have a primary key. That is a column (or combination of columns) that uniquely identifies each row. In our users table, that is the id column.

id INTEGER PRIMARY KEY

When you declare a column as INTEGER PRIMARY KEY, SQLite does something special. It makes that column an alias for the internal row ID. This means three things:

The value is always a unique integer.
If you do not provide a value on insert, SQLite automatically assigns the next available number.
Lookups by this column are extremely fast because they go directly to the row without scanning the table.

INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice');
INSERT INTO users (email, name) VALUES ('[email protected]', 'Bob');

The first user gets id 1, the second gets id 2. You did not have to specify the id at all. SQLite handled it.

We will dedicate an entire lesson to primary keys later, where we will compare integer IDs vs. UUIDs and discuss the tradeoffs. For now, INTEGER PRIMARY KEY is the default choice for most tables.

Checking your table

After creating a table, you will want to verify that it exists and looks correct. The SQLite shell gives you two dot-commands for this.

.tables lists all tables in the database:

sqlite> .tables
users

.schema shows the full CREATE TABLE statement:

sqlite> .schema users
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

This is useful when you forget what columns a table has, or you want to check the constraints. You will use .schema constantly.

Dropping a table

If you want to start over and delete a table entirely, use DROP TABLE:

DROP TABLE users;

This deletes the table and all of its data. There is no undo. In the shell, you can just recreate it, so it is fine for experimentation. But be careful with DROP TABLE on real databases.

A safer version that does not error if the table does not exist:

DROP TABLE IF EXISTS users;

The IF EXISTS part means SQLite silently does nothing when the table is already gone, instead of throwing an error. This is especially useful in scripts where you want to reset a table by dropping it and recreating it.

Naming conventions

Before we move on, a quick note on naming. Consistency matters. Pick a convention and stick with it across your entire schema.

Tables: plural nouns, snake_case. users, book_tags, order_items.

Columns: singular, snake_case. name, created_at, author_id.

Foreign keys: use the pattern referenced_table_id. A column in a books table that references authors would be called author_id.

Booleans: use an is_ or has_ prefix. is_active, has_verified_email, is_public. This makes the meaning obvious when you read a query like WHERE is_active = 1.

These are not enforced by SQLite. You could name your table TBL_USR_001 and your columns COL_A and COL_B. SQLite would not care. But your future self and your teammates will. Clear, consistent names make your schema readable.

Exercises

Exercise 1: Create a products table with columns for id, name, price, and in_stock. Choose appropriate types for each column. Use .schema products to verify the result.

Exercise 2: Drop the products table using DROP TABLE IF EXISTS and recreate it. Verify with .tables that it exists again.

Exercise 3: Create a reviews table with id, book_id, rating, body, and created_at. Use datetime('now') as the default for created_at. Insert a row without specifying created_at and then SELECT it to confirm the timestamp was filled in automatically.

What type should you use to store a date in SQLite?

You now have a table with columns and types, and you already saw two constraint keywords (NOT NULL and DEFAULT) sneak into the CREATE TABLE statement. Those little rules are doing a lot of quiet work to keep your data clean. In the next lesson, we will slow down and look at every type of constraint SQLite offers, one by one.

Access Required