Databases and persistence

The Relational Model

SQL Basics, DDL, and DML

Querying Data

Advanced Concepts

Sqlite

DBeaver

SQL Scripts

Connecting with Javascript

Appendix: Glossary of Database Terms

Resources

Practice

Assignment

• Teachers

Core program

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, which also ensures that sqlite is on your path.
Here is a books database save it somewhere you can find it: books_library.db

Opening a database

# change directory with your database
# Opens (or creates) a file called school.db
sqlite3 books_library.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 *https://hackyourfuture.net/*

Built with ❤️ by the HackYourFuture community · Thank you, contributors

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