SQL & Databases
Topic 2 · Getting started

Set Up SQL on Your Computer

Run SQL on your own machine: SQLite and DB Browser for SQLite (the easiest start), the sqlite3 command line, DBeaver as an all-in-one tool, PostgreSQL for job-ready skills, and loading a .sql or .csv file.

Show:
In this topic
Beginner

Do you need this yet?

Not to learn: every lesson runs in your browser, and the SQL playground has bigger databases. But real work happens on real database systems, so set one up when you are ready. There are two good paths:

PathInstallGood for
SQLite + DB Browser5 minutes, nothing to configurelearning, small projects, files you can email
PostgreSQL + DBeaver15–20 minuteswhat most companies use: practise for jobs and interviews

A database system has two parts: the database engine (which stores data and runs queries) and a client (the program where you type queries and see results). SQLite is an engine in a single file; PostgreSQL is a server that runs in the background; DB Browser and DBeaver are clients.

Beginner

Path 1: SQLite and DB Browser for SQLite

SQLite keeps a whole database in one ordinary file (like shop.db). There is no server to start. DB Browser for SQLite is a free program with buttons to create tables, browse data and run SQL.

  1. Download the installer from sqlitebrowser.org/dl ↗ (the "Standard installer for 64-bit Windows") and run it.
  2. Or, in a terminal: winget install DBBrowserForSQLite.DBBrowserForSQLite
  1. Download the .dmg from sqlitebrowser.org/dl ↗ and drag it to Applications.
  2. Or with Homebrew: brew install --cask db-browser-for-sqlite

On Ubuntu or Debian:

$ sudo apt install sqlitebrowser

Then: New Database (choose a file name), or Open Database for an existing .db file. Write queries in the Execute SQL tab and press ▶ (or Ctrl + Enter).

Get the practice data

In the SQL playground, choose a database and use More → Download to save the SQL, or in DB Browser use File → Import → Database from SQL file with any .sql file. File → Import → Table from CSV file turns a spreadsheet export into a table.

Beginner

The sqlite3 command line

SQLite also has a command-line tool. macOS and most Linux systems include it; on Windows, run winget install SQLite.SQLite (or download "sqlite-tools" from sqlite.org). Commands that start with a dot are for the tool itself; everything else is SQL and must end with a semicolon.

$ sqlite3 shop.db sqlite> .tables sqlite> .headers on sqlite> .mode column sqlite> SELECT name, price FROM products LIMIT 3; sqlite> .read setup.sql -- run a file of SQL sqlite> .import --csv sales.csv sales -- load a CSV as a table sqlite> .quit

Python can also use SQLite with no installation at all (the sqlite3 module is built in): see SQL from Python.

Beginner

Path 2: PostgreSQL

PostgreSQL ("Postgres") is the most popular open-source database server, and the one job adverts mention most. It runs in the background and you connect to it with a user name and password.

  1. Download the installer from postgresql.org/download/windows ↗ (the EDB installer) and run it.
  2. Choose a password for the postgres user and write it down. Keep the default port, 5432.
  3. It installs pgAdmin (a client) and psql (the command line, in the Start menu as "SQL Shell (psql)").

The easiest way is Postgres.app from postgresapp.com ↗: drag it to Applications, open it and press Initialize. Or with Homebrew:

$ brew install postgresql@17 $ brew services start postgresql@17
$ sudo apt install postgresql $ sudo -u postgres psql

The service starts automatically. Inside psql, set a password: ALTER USER postgres PASSWORD 'choose-one';

Then create a database and connect to it with psql:

$ psql -U postgres postgres=# CREATE DATABASE shop; postgres=# \c shop shop=# \i setup.sql -- run a file of SQL shop=# \dt -- list tables shop=# \q
Beginner

DBeaver: one client for every database

DBeaver Community (free, dbeaver.io ↗) connects to SQLite, PostgreSQL, MySQL and most others, with an editor that completes table and column names, shows table diagrams and exports results to CSV or Excel. Press New Database Connection, choose the type (SQLite: pick the file; PostgreSQL: host localhost, port 5432, user postgres and your password). VS Code users can also use the SQLTools extension.

Beginner

Fixing the usual problems

You seeFix
psql: password authentication failedUse the password you chose during installation, for the user postgres.
connection refused / "is the server running?"Start the server: Postgres.app → Start, brew services start postgresql@17, or on Windows the "postgresql" service in Services.
'psql' is not recognizedUse "SQL Shell (psql)" from the Start menu, or add PostgreSQL's bin folder to PATH.
A query does nothing in the command lineIt is waiting for the semicolon. Type ; and press Enter.
A function works here but not in PostgreSQLDialects differ in date and text functions (for example SQLite strftime vs PostgreSQL to_char). The SQL reference notes the differences.
Beginner

Summary

Key words

database engine
the software that stores data and runs queries (SQLite, PostgreSQL)
client
a program to write queries and see results (DB Browser, DBeaver, psql)
server
a database that runs in the background and accepts connections (PostgreSQL)
connection
host, port, database, user and password
.sql file
a text file of SQL statements you can run
dialect
one database system's version of SQL

Common mistakes

  • Forgetting the PostgreSQL password you chose: write it down during installation.
  • Forgetting the semicolon at the end of a statement in a command-line tool.
  • Running dot-commands (.tables) in DB Browser or backslash commands (\dt) outside psql: they belong to their own tools.
  • Editing data in a client and forgetting to press Write Changes (DB Browser) or commit.
Check yourself

Quiz

1. Which database keeps everything in one file and needs no server?

2. What is DBeaver?

3. Your query in psql does nothing when you press Enter. Why?

Ready