Sqlite | Notion

Teachers

For this course, we are using SQLite. It is the most popular database in the world (it is in every phone, browser, and most TVs).

How is it different?

1. Serverless

Most databases (like PostgreSQL) act as a Server. You must install software, start a background process, create users, and connect to it over a network.

SQLite is Embedded. The database engine is just a library inside your program. There is no separate "process" running.

2. Single File

The entire database (tables, data, indexes) is stored in one single file on your computer (e.g., my_database.db).

To backup: Just copy the file.
To delete: Just delete the file.

3. Flexible Types

Most SQL databases are strict. If you say a column is an INTEGER, you CANNOT put "Hello" in it. SQLite is more forgiving. It prefers the correct type, but it might let you put text in a number column if you force it (but you shouldn't!).

Installing on Windows

Installing on Mac

Using the CLI (`sqlite3`)

We will use the command-line interface.

Opening a database

# Opens (or creates) a file called school.db
sqlite3 school.db

"Dot" Commands

SQLite has special commands that start with a dot .. These are NOT SQL; they are commands for the tool itself.

.help: Show help.
.tables: List all table names.
.schema: Show the CREATE TABLE statements used to build the database.
.quit or .exit: Leave the tool.
.read filename.sql: Run SQL commands from a file.

Formatting Output

By default, SQLite's output can be hard to read. Run these commands to make it look like a nice table:

.headers on
.mode column

Tip: You can put these in a file named .sqliterc in your home folder to load them automatically.

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0

CC BY-NC-SA 4.0 Icons

*https://hackyourfuture.net/*

Found a mistake or have a suggestion? Let us know in the feedback form.