Assignment 2: Exploring PostgreSQL & SQLite CLI

In this assignment you will use the administrator tools bundled with PostgreSQL and SQLite to inspect an existing database.
While all relational database systems share the same conceptual foundations, each product ships with slightly different command-line utilities and conventions. This assignment demonstrate some of those differences.

§1 1. Start the Database Instances #

1. Extract the project archive to a new directory.
2. Open a terminal window and navigate into that directory.
3. Confirm you are in the correct place by running ls:

$ ls
docker-compose.yaml    init    README.md

4. Launch the database containers:

docker-compose up

This command will start the PostgreSQL and SQLite instances in containers. Logs will stream to your terminal; leave this window open while you work.

$ docker-compose up
[+] Running 3/3
 ✔ Network assignment2_default  Creat...                               0.0s 
 ✔ Container sqlite             Created                                0.0s 
 ✔ Container postgres           Created                                0.0s 
Attaching to postgres, sqlite
postgres  | The files belonging to this database system will be owned by user "postgres".
postgres  | This user must also own the server process.
postgres  | 
postgres  | The database cluster will be initialized with locale "en_US.utf8".
postgres  | The default database encoding has accordingly been set to "UTF8".
postgres  | The default text search configuration will be set to "english".
postgres  | 
postgres  | Data page checksums are disabled.
postgres  | 
postgres  | fixing permissions on existing directory /var/lib/postgresql/data ... ok
postgres  | creating subdirectories ... ok
postgres  | .... more log output
postgres  | PostgreSQL init process complete; ready for start up.

To shut down the databases after completing the assignment, press Ctrl + C in this same terminal window.

If you accidentally close the window, simply reopen a terminal and navigate bask to the assignment directory, then run docker-compose up again to re-attach.

§2 2. Interact with the Instances #

§2.1 2.1 PostgreSQL: psql #

You are going to use the PostgreSQL administrator Command Line Interface (CLI) to interact with your PostgreSQL instance.

The Administrator CLI allows users to directly interact with and manipulate the database instance.

To conect to your PostgreSQL instance, open a NEW terminal window and run the following command:

docker exec -it postgres sh

You should now be at a shell prompt inside the PostgreSQL container, akin to opening a remote SSH session on a real server.

$ docker exec -it postgres sh
/workdir # 

From here, launch the PostgreSQL CLI:

psql

Your prompt changes to root=#, indicating you are inside the psql CLI.

/workdir # psql
psql (17.6)
Type "help" for help.

root=#

Essential psql commands:

\dt                     -- list all tables
\d <table>              -- describe table (columns, PK, FK, indexes), replace <table> with your actual table name
SELECT * FROM <table>;  -- inspect data (semicolon required), this statement prints all data in the table
\q                      -- exit psql

Full PostgreSQL CLI reference: https://www.postgresql.org/docs/current/app-psql.html

§2.2 2.2 SQLite: sqlite3 #

SQLite is fundamentally different from client-server databases like PostgreSQL. It’s an embedded, file-based database engine, not a traditional server instance. There is no separate database server process.

• Each SQLite “database” is simply a single file on your filesystem (/data/app.db in this assignment)
• The database engine runs within your application process (in this case, the sqlite3 CLI tool itself)
• There are no “instances” in the traditional sense, each database file is the database

I have provided an sqlite container with data pre-loaded.

To conect to the SQLite container, open a NEW terminal window and run the following command:

docker exec -it sqlite sh

You should now be at a shell prompt inside the SQLite container.

$ docker exec -it sqlite sh
/workdir # 

You can launch the SQLite prompt with sqlite3 /data/app.db, this will attach to the database file at /data/app.db:

sqlite3 /data/app.db

Essential sqlite3 commands:

.tables                   -- list tables
.schema <table>           -- show CREATE TABLE statement for a table, replace <table> with your actual table name
SELECT * FROM <table>;    -- inspect data (semicolon required), replace <table> with your actual table name
.quit                     -- exit sqlite3

Full sqlite3 CLI reference: https://sqlite.org/cli.html

§3 3. Stop the Database Instances #

Do not shut down the containers until you have finished exploring both databases.
If you accidentally stop them, repeat the commands from section 1.

When ready:

1. Return to the terminal running docker-compose up, you will see the output logs from the database instances.
2. Press Ctrl + C to gracefully stop both containers.
3. Verify nothing is running:

$ docker ps
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES
# empty output

§4 4. Assignment Instructions #

Use psql and sqlite3 to collect the following for each table in both databases:

• Table names
• Column names and data types for each table
• Primary key(s) for each table
• Count rows of data for each table (SELECT COUNT(*) FROM table_name;) (manually counting is viable too)

While I encourage you to consult outside sources for a deeper understanding of the class topics, everything you need to complete this assignment is provided in this tutorial.

§5 5. Submission #

Create a plain-text submission on canvas with eight numbered sections:

1. PostgreSQL tables
2. PostgreSQL column names and data types (per table)
3. PostgreSQL primary keys (per table)
4. PostgreSQL row counts (per table)
5. SQLite tables
6. SQLite column names and data types (per table)
7. SQLite primary keys (per table)
8. SQLite row counts (per table)

If you have any questions or issues, please message or email the professor.