(If you get stuck and just want to try out some SQL, consider running SQLite in
your browser with sqlime.org. Not recommended for real
work.)
SQLite ships with macOS. Open a terminal and type sqlite3 to run the SQLite shell.
Install SQLite from your package manager or download a pre-built binary.
If you already have a terminal/command-line situation that you are happy with, we highly recommend sticking with what you know. It is very likely that you can install SQLite no matter what your preferred command-line situation is.
If you do not already have a preferred terminal/command-line for Windows, here are a few options. (If you have a suggestion for how to improve these instructions, please contact course staff, as very few of us use Windows.)
Option 1: Download the pre-built binary
for Windows. (Under "Precompiled Binaries for Windows", select the "bundle of
command-line tools" option.) Uncompress the file and double click
sqlite3.exe to open the SQLite shell.
Option 2: (maybe a bit more complicated): Install cygwin to get a unix-like environment.
Open cygwin.
The first time you run cygwin, install SQLite by clicking: Setup → Database → sqlite3
Type sqlite3 to open the SQLite shell.
Option 3 (suggested by some students): The
chocolatey package manager and the
associated SQLite
package. When install
chocolatey, be sure to update your PATH environment variable and restart
your machine afterward.
After installing, run the SQLite shell based on the OS-specific instructions above.
You should then see a welcome message that is something like:
SQLite version 3.46.1 2024-08-13 09:16:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
The exact version number and date may differ, but this shouldn't matter for our purposes.
The last line sqlite> is the "prompt": it is waiting for you to type something.
Type this:
select 1 + 2;
It should respond:
3
sqlite>
It's working!
Follow the instructions on the Postgres downloads page by clicking on your operating system.
Open the terminal appropriate to your operating system and type psql -U
postgres and press Enter. You should get a message like this:
$ psql -U postgres
psql (17.2 (Postgres.app))
Type "help" for help.
postgres=#
The exact version number might differ depending on which version you installed and what operating system you are running on.
The last line postgres=# is the "prompt": it is waiting for you to type
something. Type this:
select 1 + 2;
It should respond:
select 1 + 2;
?column?
----------
3
(1 row)
postgres=#
It's working!
-U postgresIn case you're curious, the -U postgres option passed to psql means "connect
with the username postgres" which is the default username for the database
administrator account on a new Postgres installation. If you omit this option,
it will try to connect using your OS-level username to a database with the same
name as your OS-level username. Since such a database is not created by default,
if you run psql with no options, you will get an error like:
$ psql
FATAL: database "jrw" does not exist
(my username is jrw on my laptop).
To fix this, connect as the postgres admin user and create a database with your username:
$ psql -U postgres
psql (17.2 (Postgres.app))
Type "help" for help.
postgres=# CREATE DATABASE jrw;
CREATE DATABASE
Then disconnect and reconnect with no command line options and it should work:
$ psql
psql (17.2 (Postgres.app))
Type "help" for help.
jrw=# select 1 + 2;
?column?
----------
3
(1 row)
As you develop more sophisticated queries, it's useful to use an editor that understands queries. A SQL IDE will allow you to connect to SQLite (and other kinds of) databases and run queries from a SQL file with syntax highlighting.
Here are a few suggestions for SQL IDEs:
The SQLTools extension to VS Code. There are drivers for different database systems that must be installed separately, including SQLTools SQLite and SQLTools PostgreSQL.
DBeaver is a standalone SQL IDE with more features and a better UI than VS Code. It began as a fork of the Eclipse IDE.