mike-works / sql-fundamentals

Mike North's SQL Fundamentals and Professional SQL Courses
https://frontendmasters.com/courses/sql-fundamentals/
BSD 3-Clause "New" or "Revised" License
169 stars 108 forks source link
course database mysql nodejs postgresql rest-api sqlite

This is the example project used for the Mike.Works SQL Fundamentals and Professional SQL courses.

Course outline and slides

What are we building?

We'll be working with several flavors of the Northwind Database, which Microsoft uses to demonstrate a wide range of features across their MS Access and MS SQL Server product lines. You'll be writing some application code in a small Node.js web application (built with Express) to view and make changes to this data.

Here's what it looks like (and here is a live demo):

This app is not in a good state at the beginning of the workshop. Features are missing, there are major performances, and quite a few database-related bugs. We'll fix all these problems and learn as we go!

Setup Instructions

Clone this project

In your terminal, run

git clone https://github.com/mike-works/sql-fundamentals sql
cd sql

Database Software Setup

This project is used for two workshops. SQL Fundamentals may be completed using either SQLite, MySQL or PostgreSQL, and Professional SQL requires either MySQL or PostgreSQL.

To set up the database software, please check out these guides

Install node dependencies

If you only intend to complete the SQL Fundamentals workshop (exercises 1-10), and wish to ONLY use SQLite, you can run

npm install --no-optional

If you wish to use MySQL or PostgreSQL, or proceed beyond exercise 10 for the Professional SQL course, please include optional dependencies

npm install

Database Initialization

SQLite

The ./master.sqlite file already contains the data we'll be working with, but we'll want to create a copy called ./dev.sqlite in case we mess up and have to reset to a known good state. To create this working copy, please run

npm run db:setup:sqlite
What does this do? Ultimately, the command runs this script

Validate that your SQLite database works by running

sqlite3 dev.sqlite "SELECT count(id) FROM Employee"
#> 9

PostgreSQL

The ./sql/northwind.pg.sql script contains the necessary commands for setting up the PostgreSQL schema, and the ./sql/northwind_data.sql file will fill the database with data. The database setup that includes database creation, running these scripts, and setting appropriate permissions can be run by executing this command

npm run db:setup:pg
What does this do? Ultimately, the command runs this script

Validate that your PostgreSQL database works by running

psql northwind -c "SELECT count(id) FROM Employee"
#>  count
#> -------
#>      9
#> (1 row)

MySQL

The ./sql/northwind.mysql.sql script contains the necessary commands for setting up the MySQL schema, and the ./sql/northwind_data.sql file will fill the database with data. The database setup that includes database creation, running these scripts, and setting appropriate permissions can be run by executing this command

npm run db:setup:mysql
What does this do? Ultimately, the command runs this script

Validate that your MySQL database works by running

mysql -D northwind -e "SELECT count(id) FROM Employee"
#> +-----------+
#> | count(id) |
#> +-----------+
#> |         9 |
#> +-----------+

Run the tests

There's an initial set of tests that ensure the app is correctly setup for the beginning of the course. You should be able to run this command and see them all passing

# Test against SQLite
npm run test --- EX00
# Test against PostgreSQL
DB_TYPE=pg npm run test --- EX00

Commands & Scripts

Starting the app

The app can be built and started up by running

npm run watch

This will shutdown, rebuild and restart the app whenever source files are changed. If you want to start the app so that a debugger may be connected, run

npm run watch:debug

Running Tests

You may run a subset of test suites whotes names match a string by running

npm run test --- <string>

or if you wish for the tests to re-run on code changes

npm run test:watch --- <string>

and if you want to connect a debugger...

npm run test --- EX00 --inspect-brk

Additionally, you can run tests for a particular exercise, and all exercises before it. This is useful when trying to ensure that an exercise can be completed without breaking previous work.

npm run test:ex 4 # run tests up through exercise 4

or, if you want to re-run tests on code changes

npm run test:ex:watch 4

Choosing a database

This project is designed to work with three databases: SQLite (default), PostgreSQL and MySQL. The database that's used is determined by the DB_TYPE environment variable

DB_TYPE value Database
pg PostgreSQL
mysql MySQL
anything else SQLite

This environment variable can be used when running or testing the app. For example

DB_TYPE=mysql npm run watch # Run the app using MySQL, and rebuild whenever source code changes

DB_TYPE=pg npm run test:ex 9 # Run tests up to and including exercise 9 using PostgreSQL

Recommended Tools

The following tools are recommended for this course. Depending on which database(s) you choose to use for the course, please download the appropriate tools by following their respective installation instructions.

How To Deploy on Heroku

If for some reason, you cannot set up your own local database software, you can deploy this app onto heroku and use their $7/month hosted PostgreSQL service.

Step 1

Click this button to deploy the app to heroku. Because the database is large (about 700K rows) it cannot be run with their free database option. Deploy to Heroku

Step 2

Populate the database with data. This can be done one of two ways

If you have a local database already setup and running

Use the Heroku CLI posgtres push utility (recommended)

heroku pg:push northwind DATABASE_URL --app replace-this-with-your-heroku-app-name

If you don't have a local database to push

Use the psql command line utility to run the huge PostgreSQL setup script. This will take at least several minutes.

heroku run "psql \$DATABASE_URL?ssl=true < northwind.sql -q" --app sql456

Build Status

Solutions Branch Status
SQL Fundamentals Build Status
SQL Pro Build Status

License

While the general license for this project is the BSD 3-clause, the exercises themselves are proprietary and are licensed on a per-individual basis, usually as a result of purchasing a ticket to a public workshop, or being a participant in a private training.

Here are some guidelines for things that are OK and NOT OK, based on our understanding of how these licenses work:

OK

NOT OK (without express written consent)

Copyright

© 2018 Mike.Works, All Rights Reserved

This material may not be used for workshops, training, or any other form of instructing or teaching developers, without express written consent