Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 33 ยท Meet SQLite

Your First Database

Create your first SQLite table, insert rows, read them back with SELECT and meet the hidden rowid column. A hands-on start with a tiny contacts database.

Time to build something. In this lesson you will create a small contacts table, put people into it, and read them back. Along the way you will meet the three statements that do most of the work in any database (CREATE TABLE, INSERT and SELECT) and a SQLite specialty, the hidden rowid.

If SQL is brand new to you, the SQL tutorial goes slower and deeper. Here we focus on what SQLite does with these statements.

Creating a table

A table is a grid of rows and columns. You describe its shape once with CREATE TABLE: a name, and a list of columns with an optional type for each.

SQL Create a table and look at it

Press Run (or Ctrl + Enter) to see the result here.

The first query confirms the table now exists. The second returns nothing, but notice that the result still shows the four column names. An empty table has a shape and no rows.

INTEGER PRIMARY KEY marks id as the row's unique identifier. In SQLite this particular spelling is special, and gives you automatic numbering. More on that in a moment.

Three statements, one cycle

CREATE TABLE Describe the shape once.
INSERT Add rows.
SELECT Read them back.

UPDATE and DELETE come later in the tutorial.

1 / 2

Inserting rows

INSERT INTO adds a row. Name the columns you are filling, then give the values in the same order.

SQL Insert one row at a time

Press Run (or Ctrl + Enter) to see the result here.

You never mentioned id, yet both rows have one: 1 and 2. When a column is declared INTEGER PRIMARY KEY and you leave it out, SQLite picks the next free number for you.

You can insert many rows with one statement by listing several value groups separated by commas. This is shorter and faster than many single inserts.

SQL Insert several rows at once

Press Run (or Ctrl + Enter) to see the result here.

Katherine's email is NULL, SQL's marker for "no value". It is not an empty string and not zero; it means unknown or missing. The result table displays it differently from text.

Reading rows with SELECT

SELECT chooses which columns to show and FROM says which table. * means all columns. Add WHERE to filter, ORDER BY to sort and LIMIT to cap the number of rows.

SQL Filter, sort, limit

Press Run (or Ctrl + Enter) to see the result here.

The first query skips Katherine, because she has no email, and sorts the rest by name: Ada, Alan, Grace. The second returns the two most recently added people, Katherine and Alan.

Notice IS NOT NULL. A comparison like email != NULL never works, because anything compared with NULL gives NULL, not true. Always use IS NULL and IS NOT NULL.

Meet the rowid

Every ordinary SQLite table has a hidden 64-bit integer column called rowid. It identifies each row and is how SQLite finds rows fast. When you declare a column as INTEGER PRIMARY KEY, that column simply becomes another name for the rowid: they are the same number.

SQL rowid and id are the same thing

Press Run (or Ctrl + Enter) to see the result here.

In the first query rowid and id match on every row. The tags table has no declared key at all, yet SELECT rowid still works, because every row has one. Note that SELECT * does not include the hidden rowid column: you must ask for it by name.

Some practical rules to remember:

  • Use an explicit id INTEGER PRIMARY KEY in tables you care about. It makes the row number visible and stable.
  • The type must be exactly INTEGER. A column declared INT PRIMARY KEY or BIGINT PRIMARY KEY is not an alias for rowid.
  • A later lesson covers this in depth: Tables, rowid and INTEGER PRIMARY KEY.

Seeing the id you just created

After an insert, you often want to know the number SQLite assigned. The function last_insert_rowid() returns the rowid of the most recent insert on the connection.

SQL last_insert_rowid()

Press Run (or Ctrl + Enter) to see the result here.

The two results show 1 and then 2. Programming-language drivers expose the same value (for example cursor.lastrowid in Python), which is how application code learns the ID of a row it just created.

Changing and removing rows (a first look)

You will learn UPDATE and DELETE properly in INSERT, UPDATE and DELETE. Here is a preview so your first database can change:

SQL UPDATE and DELETE

Press Run (or Ctrl + Enter) to see the result here.

Grace moved to Arlington and Alan is gone, leaving two rows. Always include a WHERE clause: without it, UPDATE changes every row and DELETE empties the table.

Common beginner mistakes

  • Forgetting the semicolon between statements. In the editor this can merge two statements into a syntax error.
  • Using double quotes for text. 'London' (single quotes) is a string. A double-quoted word is an identifier, though SQLite will sometimes quietly treat an unknown one as a string, which hides typos.
  • Creating a table twice. A second CREATE TABLE contacts fails with "table contacts already exists". Use CREATE TABLE IF NOT EXISTS contacts (...) to make the statement safe to repeat.
  • Expecting an empty string and NULL to be equal. They are different values.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You declare id INTEGER PRIMARY KEY and insert a row without giving id. What happens?

    A column declared exactly INTEGER PRIMARY KEY is an alias for the rowid, and SQLite fills it in with the next unused number.
  2. 2. Why does WHERE email != NULL return no rows?

    NULL means unknown, so comparisons with it are never true. IS NULL and IS NOT NULL are the correct tests.
  3. 3. A table has no declared primary key. Which statement is true?

    Ordinary SQLite tables always have a hidden rowid. SELECT * leaves it out, but SELECT rowid, * includes it.
  4. 4. Which function returns the id of the row you just inserted?

    last_insert_rowid() returns the rowid of the most recent successful insert on that connection.

Summary

  • CREATE TABLE defines columns once; INSERT INTO ... VALUES adds one or many rows; SELECT reads them with WHERE, ORDER BY and LIMIT.
  • A column declared exactly INTEGER PRIMARY KEY is an alias for the hidden rowid and numbers itself automatically.
  • Every ordinary table has a rowid, even without a key, but SELECT * does not show it.
  • NULL means "no value" and must be tested with IS NULL / IS NOT NULL.
  • last_insert_rowid() returns the id of the last inserted row; UPDATE and DELETE need a WHERE to avoid touching every row.

Next: Dynamic Typing and Type Affinity explains how SQLite decides what kind of value each cell holds, and why it differs from most databases.