thinkful-ed / node-sql-sequelize-lessons

Extension work on SQL and Sequelize for Thinkful's Node course

SQL and Sequelize for students of Node

Extension lessons for Thinkful's Node course covering:

• SQL basics
• Postgres
• ElephantSQL
• Sequelize

Assumes prior knowledge of Express, Mongo, Mongoose, and integration testing.

node-sql-sequelize-lessons's Issues

User-submitted feedback for 1.1.2

"SELECT * from restaurants;" should be "SELECT * FROM restaurants;" to keep with "the convention of using uppercase for SQL commands like INSERT INTO and lowercase for table names and properties."

Curriculum url: https://courses.thinkful.com/node-sql-001v1/assignment/1.1.2
UUID: 81c2b090-e895-11e6-a3eb-03934004d1da

1.1.1 - Wrong commands for Windows

Nicole Gilbert ([email protected]) reported an issue.

Name: "Setting up Postgres locally" assignment
Live URL: https://courses.thinkful.com/node-sql-001v1/assignment/1.1.1

Content:

Step 9 creates errors in Command Prompt, when creating the dev user on Windows / PC. Instead of using -Pw, I was able to troubleshoot by using the command -P instead.

Front conversations

User-submitted feedback for 1.1.4

There was no assignment to submit in this lesson but it asks for a link to a project at the bottom of the page.

Curriculum url: https://courses.thinkful.com/node-sql-001v1/project/1.1.4
UUID: 81cbd850-e895-11e6-a3eb-03934004d1da

User-submitted feedback for 1.1.4

'Here's that behavior was achieved when originally setting up this database:'

'Here's how' maybe...?

Curriculum url: https://courses.thinkful.com/node-sql-001v1/assignment/1.1.4
UUID: 81c2d7a0-e895-11e6-a3eb-03934004d1da

User-submitted feedback for 1.1.4

What project are we supposed to submit?

Curriculum url: https://courses.thinkful.com/node-sql-001v1/project/1.1.4
UUID: 81cbd850-e895-11e6-a3eb-03934004d1da

User-submitted feedback for 1.1.5

This says: "From the command line, run the following command:
psql -f ./backup.sql postgres://username:[email protected]:5432/dbName", but this is missing a critical step that it took us a while to figure out. Exit the database BEFORE running this command in your terminal (or maybe it would work in a separate terminal window). At any rate, this command does NOT appear to work if you are actively in a database in the same terminal window. I recommend adding that to the lesson.

Curriculum url: https://courses.thinkful.com/node-sql-001v1/assignment/1.1.5
UUID: 81c2feb0-e895-11e6-a3eb-03934004d1da

BASH commands vs SQL equivalents

We've experienced several issues with postgres commands on Windows in Git Bash. For example:

$ psql dev -U dev
psql: warning: extra command-line argument "dev" ignored
Password for user -U:

That should say Password for user dev:

Other errors are "too many arguments", "warning: extra command-line argument "postgres" ignored" and sometimes just ignored without error or warning.

Possible Solutions:

1. One solution is to be sure that all switch arguments go before non-switched arguments

2. Use built-in command prompt

Also like to investigate used SQL equivalents to BASH commands.

So compare Bash Commands like
$ createdb -U dev dev-restaurants-app
versus.
=> CREATE DATABASE dev-restaurants-app OWNER dev

Note from Postgres Documentation:
"createdb is a wrapper around the SQL command CREATE DATABASE. There is no effective difference between creating databases via this utility and via other methods for accessing the server."

User-submitted feedback for 1.1.4

"Let's say we wanted to restaurant data alongside each grade". Should this say..."Let's say we wanted to DISPLAY restaurant data alongside each grade"?

Curriculum url: https://courses.thinkful.com/node-sql-001v1/project/1.1.4
UUID: 81cbd850-e895-11e6-a3eb-03934004d1da

User-submitted feedback for 1.2.2

Hello! Happy 4th of July.

It looks like Sequelize is now on v4. instanceMethod and classMethod are no longer valid.
http://docs.sequelizejs.com/manual/tutorial/upgrade-to-v4.html

I was able to get my app to work with just a couple of minor changes. Basically, I removed classMethod and instanceMethod from the options object and made them methods for the model, if that makes sense. Here's an example of one of my Sequelize models on v4 (4.2.1, specifically).
https://github.com/Ljyockey/hospitalert-api/blob/master/models/user.js

Students may run into issues when they work on the final assignment as NPM will automatically install v4 and as of right now there don't seem to be a lot of examples on the v4 changes aside from the official Sequelize docs. Just thought I'd point it out..Have a great day! :)

Curriculum url: https://courses.thinkful.com/node-sql-001v1/assignment/1.2.2
UUID: 81c373e0-e895-11e6-a3eb-03934004d1da

1.1.1 - instructions don't work for Windows

Robert Frazier ([email protected]) reported an issue.

Name: "Setting up Postgres locally" assignment
Live URL: https://courses.thinkful.com/node-sql-001v1/assignment/1.1.1

Content:

In steps 4 and 5, it needs to say that the "System Properties" and "Environment Variables" are found in Windows Setup, not in the program we just installed.

Steps 10 and 11 do not work as written in Windows 10. I am either getting no feedback indicating anything has happened, or else error messages.

Front conversations

Windows Postgress Issues

Summary:

1. The new installer appears to automatically setup and run postgres as service

2. Stopping service and running from command line prompts firewall settings

3. Running createuser -U postgres -Pw --interactive does not respond

4. Running createdb dev -U dev or createdb -U dev dev does not respond

5. Running pg_ctl start responds with this:

IEUser@MSEDGEWIN10 MINGW64 ~
$ pg_ctl start
pg_ctl: another server might be running; trying to start server anyway
server starting

IEUser@MSEDGEWIN10 MINGW64 ~
$ 2017-05-30 04:27:21 PDT LOG:  could not bind IPv6 socket: Only one usage of each socket address (protocol/network address/port) is normally permitted.

2017-05-30 04:27:21 PDT HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2017-05-30 04:27:21 PDT LOG:  could not bind IPv4 socket: Only one usage of each socket address (protocol/network address/port) is normally permitted.

2017-05-30 04:27:21 PDT HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2017-05-30 04:27:21 PDT WARNING:  could not create listen socket for "*"
2017-05-30 04:27:21 PDT FATAL:  could not create any TCP/IP sockets
2017-05-30 04:27:21 PDT LOG:  database system is shut down

2. After stopping the service above, and running pg_ctl start
   

3. Bash command createuser -U postgres -Pw --interactive does not respond. But logging in with psql -U postgres and entering the password at the prompt does work. So create the user using the CREATE USER command

CREATE ROLE dev WITH LOGIN PASSWORD 'Password1' SUPERUSER;

4. Running createdb dev -U dev or createdb -U dev dev does not respond so use CREATE DATABASE command instead:

CREATE DATABASE dev WITH OWNER dev;

User-submitted feedback for 1.1.2

In the Updating Records section with the cuisine:

UPDATE restaurants
SET cuisine = 'Francais'
WHERE cuisine = 'French';

That is improper French, at best it'd be la cuisine francais sans cedilla to avoid ASCII issues but to just say francais makes no sense. It's in reference to the language by saying fran. La gastronomie francaise would also be appropriate here. tl;dr food is feminine in French, must be feminine and include la cuisine or la gastronomie.

Curriculum url: https://courses.thinkful.com/node-sql-001v1/assignment/1.1.2
UUID: 81cbb140-e895-11e6-a3eb-03934004d1da

1.1.4 - code should say 9 instead of 10

Robert Frazier ([email protected]) reported an issue.

Name: "Joins, related data, and constraints" assignment
Live URL: https://courses.thinkful.com/node-sql-001v1/assignment/1.1.4

Content:

"Now run the following commands:

SELECT * FROM grades WHERE id = 15; -- it has restaurant_id of 10
DELETE FROM restaurants WHERE id = 10;
SELECT * FROM grades WHERE id = 15; -- restaurant_id is null"

Should be:

"Now run the following commands:

SELECT * FROM grades WHERE id = 15; -- it has restaurant_id of 9
DELETE FROM restaurants WHERE id = 9;
SELECT * FROM grades WHERE id = 15; -- restaurant_id is null"

Front conversations

1.1.1 - Incorrect PATH variable in video

Nicole Gilbert ([email protected]) reported an issue.

Name: "Setting up Postgres locally" assignment
Live URL: https://courses.thinkful.com/node-sql-001v1/assignment/1.1.1

Content:

In the video/visual instruction for step 5 for Windows, there is a typo in the Variable Value. If you use %POSTGRES_DIR%/data instead of %POSTGRES_DIR%\data, Windows gives you an error message that "/" is not an allowed symbol. The video tripped me up because it has the wrong convention.

Front conversations

Describe using a file to create and run SQL commands

There has been some confusion about how to execute the code for the drills and the challenge. Several students have tried to execute all the code for the drills in the psql shell. And they are confused about the challenge where is says "Create a single SQL (.sql) file that creates tables and inserts some records into a blog database..."

My suggestion is to add section on running queries using a .sql file to the drills

psql -U dev -f ~/query.sql dev-restaurants-app

This would also lay the groundwork for the challenge

User-submitted feedback for 1.1.1

Step 10 should say createdb -U dev dev instead of createdb dev -U dev

The user flag needs to come before the database name on windows, otherwise you'll get a "too many arguments" error.

Curriculum url: https://courses.thinkful.com/node-sql-001v1/assignment/1.1.1
UUID: 81cb8a31-e895-11e6-a3eb-03934004d1da

User-submitted feedback for 1.1.1

Just a suggestion for point no. 7 under the Mac installation. Change "Run the command createuser -Pw --interactive" to "While the server is running, enter the command createuser -Pw --interactive." I think this clarification is need since the video that this text is associated with shows a user stopping the server.

Curriculum url: https://courses.thinkful.com/node-sql-001v1/assignment/1.1.1
UUID: 81c26270-e895-11e6-a3eb-03934004d1da

User-submitted feedback for 1.1.2

This may seem obvious but somewhere at the beginning where students are learning commands, we should mention this little piece of advice which can be found at https://www.postgresql.org/docs/6.4/static/install12418.htm: "(Get in the habit of including those SQL semicolons. Psql won't execute anything until it sees the semicolon or a "\g" and the semicolon is required to delimit multiple statements.)" I mention this because when I inadvertently left out the semicolon, not only did the query not work but there was no error message and my prompt almost imperceptibly changed from "dev-restaurants-app=#" to "dev-restaurants-app-#". While I don't know the difference, a prompt with a dash instead of an equals sign appears to be useless and some students might not notice the difference and get frustrated when they correct their error by including the semicolon but still fail to make a successful query because their prompt now has a dash in it (which, again, seems to ignore all valid commands).

Curriculum url: https://courses.thinkful.com/node-sql-001v1/assignment/1.1.2
UUID: 81c2b090-e895-11e6-a3eb-03934004d1da

User-submitted feedback for 1.1.4

is:
Here's that behavior was achieved

should be:
Here's how that behavior was achieved

Curriculum url: https://courses.thinkful.com/node-sql-001v1/project/1.1.4
UUID: 81cbd850-e895-11e6-a3eb-03934004d1da

User-submitted feedback for 1.2.4

just submitted a 1-star by mistake, my b!

Curriculum url: https://courses.thinkful.com/node-sql-001v1/assignment/1.2.4
UUID: 81c39af0-e895-11e6-a3eb-03934004d1da

Add "DROP TABLE IF EXISTS..." to SQL dump/import file

The "Download this SQL dump." file needs DROP TABLE IF EXISTS statements at the top so the update can be re-executed easily

https://github.com/Thinkful-Ed/node-sql-sequelize-lessons/blob/master/content/1.1.2/content.md

User-submitted feedback for 1.1.6

Similar to the last issue I posted. Throughout the SQL curriculum, it says 'Ultimately, you need a script that can be run like this: psql -f ./bootstrap-blog-app.sql. When you've got this script, save it to Gist or Github.'

The -f $filepath$ flag should go before the database name. Otherwise you get an "extra argument ignored" error on Windows.

Curriculum url: https://courses.thinkful.com/node-sql-001v1/project/1.1.6
UUID: 81cbd852-e895-11e6-a3eb-03934004d1da

User-submitted feedback for 1.1.4

I believe this:
"CREATE TABLE grades (
id serial PRIMARY KEY,
"date" timestamptz NOT NULL,
grade text NOT NULL,
score integer NOT NULL,
restaurant_id integer REFERENCES restaurants ON DELETE CASCADE
);

should actually read:
CREATE TABLE grades (
id serial PRIMARY KEY,
"date" timestamptz NOT NULL,
grade text NOT NULL,
score integer NOT NULL,
restaurant_id integer REFERENCES restaurants(id) ON DELETE CASCADE
);

Curriculum url: https://courses.thinkful.com/node-sql-001v1/project/1.1.4
UUID: 81cbd850-e895-11e6-a3eb-03934004d1da

Upgrade to Sequelize v4

http://docs.sequelizejs.com/manual/tutorial/upgrade-to-v4.html

Slim Restaurants DB

ElephantSQL is the fallback Db for students who have trouble installing Postgres - mainly Windows students and some ubuntu. ElephantSQL has a 20MB limitation on free accounts so we need to slim down the database.

Attached is a DB dump with about 1000 restaurants and grades. Plus some additional commands to DROP TABLE/TYPE IF EXISTS in case the student attempts to reload the data. This comes in around 15MB in ElephantSQL. Honestly, I'm not sure why it is that large, but that's what they list it as.

nyc-restaurants-data-backup.sql.zip