foundersandcoders / pg-workshop

pg-workshop

Author: @shiryz

In this workshop we'll be building on what we learnt in the pg walkthrough. This app currently contains static data on users' name and location. We'll be setting up our own database connection so that the data can be retrieved from a table of "users" instead. We'll also want to write code that enables us to add new users via a form.

Getting started

1. Clone this repo
2. Install the node_modules by typing npm i in your terminal.
3. Run npm run devin your terminal and checkout the result at http://localhost:5000. This is the starting template for what you'll be building.
4. In src/handlers.js you'll find a function that deals with calls to the /users endpoint. The data is currently coming from the static.js file. Add your own name and location within static.js. Refresh the page & check the results.

Task 1: Setting up the database

We are currently hard-coding the data in to the application (static.js) because we don't have a database. Now we want to replace static.js with an actual database. Let's start by setting up the database we will connect to.

1. Connect to postgres, by typing psql or pgcli in the terminal.
2. Create the database by typing CREATE DATABASE [the name of the database];.
3. Create a superuser with a password by typing CREATE USER [the new username] WITH SUPERUSER PASSWORD '[the password of the database]'; (the password needs to be in quotes, otherwise you get an error).
4. Change ownership of the database to the new user by typing ALTER DATABASE [name of the database] OWNER TO [the new username];
5. Add a config.env file and add the database's url in this format: DB_URL = postgres://[username]:[password]@localhost:5432/[database]. The database name needs to be in lower case.

Task 2: Getting data from the database

Your job now is to add to database/db_connection.js and queries/getData.js and refactor handlers.js so that the response data comes from the users table in your database instead of from static.js.

1. In the terminal, connect to your database using psql postgres://[username]:[password]@localhost:5432/[database].
2. Create a table called 'users' with three columns: 'id', 'name' and 'location' and add a couple of rows of dummy data. Hint: don't hard code the ids
3. Inside queries/getData.js, write a SQL query that pulls the necessary data from your database.
4. Change the /users handler in handlers.js to call the getData query.

Task 3: Adding data to the database

So far, we've only been dealing with GETting data from a server. But what if we want users to be able to add their details to our database?

As we still don't have a visible form in the front end, only a developer can add to the database, either through psql in the command line, or by adding .sql files to this repo. Let's change that.

In the commented out form in index.html we have this HTML attribute: action="create-user". When a user clicks 'submit' on this form, the details they have entered will be sent in the payload to a 'create-user' endpoint.

1. Open up index.html and uncomment the form
2. Check that you can see an empty input box when you refresh your browser
3. Add a new endpoint to router.js (/create-user)
4. Create a new handler function for the endpoint create-user in handlers.js that uses the data from your form to INSERT a new row into your users table. What kind of method do you think you'll need (check the HTML!)? How will you access the data from your form?

Task 4: CSS Challenge

Style it up!

1. Try and get the design looking as close as possible to the below:

Bonus

Customise your app!

You could:

1. Add some new columns to your users table
2. Try and handle different types on input on the client side (e.g. boolean values or longer text input)
3. Validate input to check that the user does not already exist

pg-workshop's Issues

Add CSS component to workshop

We'd like to modify this workshop to include a CSS section on styling input fields.

We'll include a screenshot of a styled version of the repo and challenge students to replicate it. #16

solutions branch does not work

The solutions provided dont seem to allow you to create a user... :(

Minor copy edits for clarity throughout

Whilst user testing, comment below about things that need changing.

CSS task is unnecessary

copy css from solution

move solutions from branch to folder

Once we get @pbywater's PR #23 merged into the solutions branch, let's follow the pattern that we've started elsewhere and move these files from a branch into a solutions folder within the master branch of this repo, as we've done with the pg-walkthrough

postUserHandler solution

I've seen a student copy this post handler in their project- I don't think it's standard practice because it results in sending back the homepage (homeHandler), but keeping the /add in the url bar. Better would be a redirect.

pg-workshop/solutions/src/handlers.js

Lines 31 to 43 in 160ec55

	const postUserHandler = (request, response) => {
	let data = '';
	request.on('data', chunk => {
	data += chunk;
	});
	request.on('end', () => {
	const { name, location } = qs.parse(data);
	postData(name, location, err => {
	if (err) return serverError(err, response);
	homeHandler(response);
	});
	});
	};

handle errors

Once PR #23 is merged, we need to change the solution to show errors being handled. Particularly after the confusion from last week over the issues from the error-handling-workshop

• https://github.com/pbywater/pg-workshop/blob/solution/src/router.js#L15
• https://github.com/pbywater/pg-workshop/blob/solution/src/router.js#L24
• https://github.com/pbywater/pg-workshop/blob/solution/src/router.js#L36
• https://github.com/pbywater/pg-workshop/blob/solution/src/router.js#L41
• https://github.com/pbywater/pg-workshop/blob/solution/src/router.js#L52

CSS Challenge image

Create solutions branch

User test workshop and come up with potential solution.

@ConchobarMacNessa and @macintoshhelper - review my PR once I've finished this to suggest any changes

add author and maintainer to readme

author = @bradreeder
Option 1 (maintenance by someone other than the author)

Author

--insert name--
--insert github handle--
--insert photo of author--

Maintainer

--insert name--
--insert github handle--
--insert photo of maintainer--

I was going to use the photos that FAC has of us from day 1 of the course. But obviously people would be free to update it with any photo of themselves that they wanted

Css challenge

Refactor master with CSS in solution folder only

After reviewing the pg-workshop with @shiryz and @MynahMarie , we decided to add the CSS from the solution into the main workshop.

I have created a branch with this change as well as some refactoring to encourage best practises with relation to abstracting handlers out of the router, handling static files and errors, using qs and the power of destructuring.

Based on reviews by @eliasCodes and @m4v15 I've separated the code into two branches. Both with the refactoring but one with CSS in the solution to be merged into master and one with CSS in the main workshop that can be used by cohorts if they want.

Clarify setup

Some instructions were confusing and there was an issue with setting up as a Linux user. @macintoshhelper - I think you've worked on these?

Make request to the server and view data

Task 2 Step 4 note should refer to line 7 of `router.js`

Form button should have a type of 'submit'

For a button to automatically submit a form it needs to have type=submit rather than type=button. I think this is actually the default for a button within a form, so it could also be left off entirely.

I'm happy to do a PR if you like

Create an html file.

create an html file with button to make request to the server and retrieve data.

Create server

Set up server.

rename "handlers" folder to "queries"

router.js contains the handlers. This folder only contains functions which are called within the handlers and so should probably be renamed. I suggest queries instead.

• change the folder name itself
• change all the references in the readme

Might as well wait until the solutions branch is turned into a folder #26

clarity of instructions in readme

Getting started

Students have expressed confusion before whenever there is existing code in a workshop but they aren't walked through what that code is doing.

• Set the scene by explaining what our app does (before the instructions to clone and npm install & npm run dev)
• After they've had a chance to see it in the browser once, give students explicit instruct to change something with the current code, so they see exactly what already exists:

1. Add your own name and location within handlers/static.js
2. Refresh the page & check the results

Note: if you have asked students to npm run dev, nodemon should handle the update automatically, so students shouldn't have to manually restart the server before refreshing.

Task 1

• Give high-level explanation of the problem:
  We are currently hard-coding the data in to the application (static.js) because we don't have a database.
  Now we want students to replace static.js with an actual database.
• Change "Setting up the database" to "Task 1"
• Turn bullet points into numbered steps
• Add table creation to the end of these steps

  Create a table called 'users' with three columns: 'id', 'name' and 'location' and add a couple of rows of dummy data. Hint: don't hard code the ids

• Final step is to connect this database to "Write a db_connection.js file" to the end of these

Task 2

The original Task 1 is good, but

• could be broken down into more specific steps

In src/router.js you'll find a function that deals with calls to the /users endpoint. The data is currently coming from the static.js file.
Change the handler so that the response data comes from the users table in your database instead of from static.js.

1. Inside queries/getUsers.js, write a SQL query that pulls the necessary data from your database
2. Change the /users handler in router.js (aka handlers.js) to call the getUsers query

• should probably point students to the existence of this line and explain what it's doing, since they haven't seen this in prior workshops & are likely not to notice it since it's been pre-written

Task 3

• Change description of the task from developer-centric (what kind of query we're writing) to user-centric (why we would write that query)
• Point students to the inline HTML attribute action="create-user" as the reason for having to alter the create-user handler! (Not everyone is necessarily familiar with this.)
• Explain what happens when submit button is clicked & what the payload of a form looks like

Right now, the only way to add users to our database, is manually. Only a developer can add users to the database, either through psql in the command line, or by adding .sql files to this repo.

But we want to make this accessible via our website, so that people can add users to the database themselves.

1. Uncomment-out the existing form in index.html
2. Refresh your browser and make sure you see this

Notice the combination of type="submit" on the button element, and the action attribute on the form (action="create-user"). This means that every time the submit button is clicked, we are sending a request to the /create-user endpoint on our server, with the contents of the form as the payload:

{
  name: // value of input field goes here,
  location: // value of input field goes here
}

1. Add a new endpoint to router.js (/create-user)
2. Create a handler function for this new endpoint create-user, which uses the data from your form to INSERT a new row into your users table
3. Give the users of your website some helpful feedback, so they know that their data has been successfully dealt with.

Task 4

Style it up! Try and get the design looking as close as possible to the below before adding any new features.