pthom / northwind_psql

Northwind sample database for postgres

Northwind database for Postgres

A simple sql script that will populate a database with the famous northwind example, adapted for postgres.

Getting started:

Manually

Use the provided sql file nortwhind.sql in order to populate your database.

With Docker and docker compose

Pre-requirement: install docker and docker-compose

https://www.docker.com/get-started

https://docs.docker.com/compose/install/

1. Run docker-compose

> docker-compose up
...
... Lots of messages...
...
Creating network "northwind_psql_db" with driver "bridge"
Creating volume "northwind_psql_db" with default driver
Creating volume "northwind_psql_pgadmin" with default driver
Creating pgadmin ... done
Creating db      ... done

2. Run psql client:

Method 1: Via the docker-compose container

Open another terminal window, and type:

> docker-compose exec db psql -U postgres -d northwind

psql (13.2 (Debian 13.2-1.pgdg100+1))
Type "help" for help.

northwind=# select * from us_states;
 state_id |      state_name      | state_abbr | state_region
----------+----------------------+------------+--------------
        1 | Alabama              | AL         | south
        2 | Alaska               | AK         | north
        ...

Alternatively, you can launch bash, then psql:

docker-compose exec db /bin/bash

# You are now inside the "db" docker container
psql -U postgres northwind

Method 2: Direct access via the port 55432

The "db" docker exposes postgres on the port 55432. If you have psql on your path, you may connect to it via:

# Run this directly from your computer (this will connect to the docker db)
psql -U postgres northwind -p 55432

3. Connect PgAdmin

Access to PgAdmin at the url: http://localhost:5050

Add a new server in PgAdmin:

• General Tab:
  • Name = db
• Connection Tab:
  • Host name: db
  • Username: postgres
  • Password: postgres

Then, select database "northwind".

4. Stop docker-compose

Stop the server that was launched by docker compose up via Ctrl-C, then remove the containers via:

docker-compose down

5. Files & persistence

Your modifications to the postgres database(s)will be persisted in the postgresql_data docker volume, and can be retrieved once you restart docker compose up.

If you need to delete the database data, run docker-compose down -v (the database will then be repopulated from scratch when running docker-compose up).

If you need to upload any files into your db container, just copy and paste them to the files local folder. They will be available at the /files path inside the db container.

northwind_psql's Issues

Running standalone on Windows?

Hi - to run this on in a standalone container that I could access from other applications (like VS Code, or an API I was writing), I needed to modify the docker-compose file to look like this:

services:
  db:
    image: postgres:12
    environment:
      POSTGRES_DB: northwind
      POSTGRES_USER: northwind_user
      POSTGRES_PASSWORD: thewindisblowing
    volumes:
      - northwind-pg-data:/var/lib/postgresql/data
      - ./northwind.sql:/docker-entrypoint-initdb.d/northwind.sql
    ports:
      - "5432:5432"

volumes:
  northwind-pg-data:
    external: true

I also needed to run the following command prior to docker-compose up:

docker volume create --name northwind-pg-data -d local

Then I could access the database on localhost:5432 and everything worked great. (thanks for this repo btw). I'm wondering if a PR for either the readme spelling this out and/or a second compose file that could be ref'd with the -f param if someone wanted to do this.

If you think it's a good idea, I'll submit a PR. If you're not interested, that's fine - and thanks again for the repo! :)
-Erik

License

I think I placed this in the incorrect spot earlier: I know that the northwind dataset is available under an open source license, as is the database for SQL Server.

I am curious if I am able to use your specific code for the creation of the postgresql DB for a course I am running in the near future? If not, no worries - I just figured you have done a great job of translating into postgreSQL and I should inquire!

no FKs

postgres 9.5, no FKs

use -X, --no-psqlrc to ensure basic use of psql

Many user use a .psqlrc file, for instance to always turn auto commit off.
This is for good measure and is what I cal my safety belt against.

To make you script (create_db.sh) work in the presence of such settings, you might want to add
-X of --no-psqlrc in every call to psql.

See the text below.
Thx for your effort.

`
#!/bin/bash

dropdb northwind
dropuser northwind_user

createdb northwind
psql -X northwind < northwind.sql

psql -X template1 -c "create user northwind_user;"
psql -X template1 -c "alter user northwind_user password 'thewindisblowing';"
psql -X template1 -c "grant all on DATABASE northwind to northwind_user;"
psql -X northwind -c "GRANT ALL on ALL tables IN SCHEMA public to northwind_user"
`