Inserting data

Our migration 001_create_users.sql ran and created the users table. The table exists, the columns are in place, the constraints are ready to do their job. But it is empty. There is nothing in it yet.

From this point on in the course, every table we work with was born out of a migration file. We will not create a table with a shell command anymore. The migration is the source of truth for the schema, and what we do from here is fill that schema with data.

The INSERT statement adds rows to a table. Let’s start simple.

A single row

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

Let’s read this left to right.

INSERT INTO users means “add a row to the users table.” The next part, (email, name), lists the columns you are providing values for. Then VALUES ('[email protected]', 'Alice') provides the actual values, in the same order as the columns.

Notice that we only provided email and name. What about id and created_at?

We do not need to supply id because it is an INTEGER PRIMARY KEY. SQLite auto-assigns the next integer, starting from 1. And we do not need to supply created_at because the migration gave it a DEFAULT expression that fills in the current timestamp automatically.

After running this, you can verify the row exists:

SELECT * FROM users;

id  email              name   created_at
--  -----------------  -----  -------------------
1   [email protected]  Alice  2025-04-02 12:00:00

Both id and created_at were filled in for us. The constraints and defaults we set up in the migration are doing their job.

Multiple rows at once

You can insert several rows in a single statement by separating each set of values with a comma:

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

This is faster than three separate INSERT statements because SQLite only needs to process one statement and one transaction internally. When you are inserting a lot of data, this difference adds up.

What happens when an insert fails?

What do you think happens if you try to insert a duplicate email?

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

SQLite rejects it with: UNIQUE constraint failed: users.email. The row is not inserted. Your existing data is untouched. The UNIQUE constraint on email caught the duplicate before anything bad could happen.

What about inserting without a required field?

INSERT INTO users (email) VALUES ('[email protected]');

SQLite rejects it with: NOT NULL constraint failed: users.name. The name column requires a value, and we did not provide one.

This is exactly why constraints matter. They catch mistakes at the database level, before bad data can sneak in. Your migration file is not just paperwork. It is an active defense against bugs.

Getting the id back with last_insert_rowid()

After an insert, you often need the id of the row you just created. Maybe you want to use it to insert related data in another table. SQLite provides a built-in function for this:

INSERT INTO users (email, name) VALUES ('[email protected]', 'Eve');
SELECT last_insert_rowid();

last_insert_rowid() returns the id (the rowid) of the most recently inserted row on the current connection. You will see this used later when we insert related data across multiple tables.

INSERT OR REPLACE

Sometimes you want to insert a row, but if it conflicts with a UNIQUE constraint, replace the existing row instead of failing:

INSERT OR REPLACE INTO users (email, name)
VALUES ('[email protected]', 'Alice Updated');

If a row with that email already exists, SQLite deletes the old row and inserts the new one.

But there is a catch. Because it deletes and re-inserts, the id changes and created_at resets to the new default. That is often not what you want. You lose the original row’s identity, including any references other tables might make to that id.

INSERT … ON CONFLICT (upsert)

For more control, use the ON CONFLICT clause (available in SQLite 3.24+):

INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice Updated')
ON CONFLICT(email) DO UPDATE SET
  name = excluded.name;

This says: “Try to insert this row. If there is a conflict on the email column, do not fail. Instead, update the existing row’s name to the new value.”

The keyword excluded is special. It refers to the row that would have been inserted if there were no conflict. So excluded.name is 'Alice Updated', the name from the VALUES clause.

The key advantage over INSERT OR REPLACE is that the existing row is updated in place. The id does not change. The created_at does not reset. Only the columns you specify in the SET clause are modified. This is exactly how production applications handle “create or update” logic.

[!WARNING] Prefer ON CONFLICT over INSERT OR REPLACE when you need to preserve the existing row’s identity. INSERT OR REPLACE deletes and re-inserts, which resets auto-generated values and can trigger ON DELETE cascades on foreign keys (we will learn about those later).

Exercises

Exercise 1: Insert five users into the users table using a single multi-row INSERT statement. Verify with SELECT * FROM users;.

Exercise 2: Try inserting a user with a duplicate email. Read the error message. Then try inserting a user without a name. Read that error message too. Notice how each constraint produces a different, specific error.

Exercise 3: Insert a user, then use ON CONFLICT to update their name. Verify with SELECT that the id and created_at did not change, but the name did.

You can now put rows into a table, handle duplicates, and upsert. Every example in this lesson was a single INSERT, which SQLite already treats as atomic on its own. The moment you start doing multiple writes that have to hold together (a user plus their profile, a review plus a count update), you need the next tool in the kit: transactions. The next lesson pins down what BEGIN, COMMIT, and ROLLBACK actually do, and from there every multi-step operation in the course lives inside a transaction.