High level overview - make sure to plan out your function!

• login user function should take in username/email password
  • if username & email is not passed in, user not found
  • if username character is too short, user not found.
  • if no username or no password, user not found.
• bcrypt encrypt it
• Find user by username / email
• Compare password in user matches encrypted password.
  • If password does not match, throw error

Make sure to follow rest conventions

Signup:
POST /api/users

Delete Users:
DELET /api/users/:id

Goal

As a developer, I can safely rely on the User Module to create and edit user accounts.

Requirements

• createUser -> This creates a user account in the database
• getUser -> returns information about the user
• deleteUser -> deletes user account and all relevant information
• isConfirmed -> return true / false if user has confirmed their account
• hasDBPassword -> return true/false if user has database password.

Goal

As a developer, I want to be able to run a sendEmail function with an email and a type. The email function should take care of sending the email.

Email should also generate email Template Strings.

Requirements

const {generateTemplate, sendEmail } = require('./email')
sendEmail( '[email protected]', 'subject string', generateTemplate('confirmation', data) )

TODO

1. Get Mailgun account, use their API to send an email successfully.
2. sendEmail Function
3. GenerateTemplate Function

helper functions.

const signup = (userObj) => {

signup should take in email, username, password.

• Check validation rules and return an object with errors.
  • username contains invalid characters (must be alpha numeric)
  • email is invalid
  • username / password is too short / long
• Call sequelize's database function for signup
  • If error happens, throw error.
• Call mailgun function to send email
• Respond with user object.

Code like this one unknowingly exposes user password to the internet: https://github.com/garageScript/databases/blob/master/src/routes/userRoutes.js#L89

Maybe add comment to users.js file saying... Make sure to always delete password before returning user object

POST /api/notification

https://github.com/garageScript/databases/wiki/Routes-and-APIs#post-apinotification

Documentation
https://docs.timescale.com/latest/getting-started/installation/ubuntu/installation-apt-ubuntu

npm
https://www.npmjs.com/package/timescale#usage

POST /api/passwordReset

Sound send 2 things in the body

1. token
2. password

User should be able to create a database.

Call PG Module with username and dbPassword

logger.error should accept error object, objects, and/or string.

All the other logger functions should allow for objects, strings, array, or other data types.

If it is error, you can call the .toString() method.

https://stackoverflow.com/questions/30469261/checking-for-typeof-error-in-js

JSON.stringify( [1,2,3], null, 2)

1. create a route that goes to / and just renders welcome
2. create an integration test to start stop server and sends a request to / to match snapshot.
3. Setup view engine

Create .env.example and with sample information and comments about what each env variable is used for.

1. Update readme to move .env.example to .env and what accounts users have to create / setup to get the variables needed.
2. Update .gitignore to ignore .env files so nobody will accidentally commit that file.

/databases

-postgres text
-create postgres button

Reproduction:

Navigate to one of these endpoints after signing in.

https://c0d3.com/teacher/1
https://c0d3.com/teacher/2
https://c0d3.com/teacher/3
https://c0d3.com/teacher/4
https://c0d3.com/teacher/5
https://c0d3.com/teacher/24
https://c0d3.com/teacher/25
https://c0d3.com/teacher/27
https://c0d3.com/teacher/28
https://c0d3.com/teacher/29

Example: DataDog

Databases

src/server.js

• startServer function that takes in a port number and runs app.listen

• stopServer function that stops the server. Lookup express documentation.

• integration/server.js with a beforeAll function that runs startServer

End point: /databases/postgres
-port number
-database name
etc.

This PR partially made the pg module unusable, need to fix: https://github.com/garageScript/databases/pull/42/files

the other issue that PG module face is the IF NOT query does not seem to work. May have to remove it.

• Postgres

merged in pr 66 prematurely. Please address comments: https://github.com/garageScript/databases/pull/66/files

Update user account lib/user.js

• setDBPassword that takes in an argument, and updates the Account sequelize table.

To document these changes: https://github.com/garageScript/databases/pull/21/files

0 Credentials

hostname: 165.227.55.105
username: account username
password: account setDbPassword value

or

Create PG Credentials - Button

1 Intro

Postgres is an application. More specifically, a database application for you to organize your data. It can store data for you and you can have multiple apps connect to your Postgres:

2 Basics

To quickly help you get started, we will go over a few basic commands that you can follow along. Explanations and best practices will be covered in the section below.

2.1 Connect

To connect to your app to Postgres, you need a module called pg. You need to specify where the postgres application is located (host and port), the name of your database, and your username / password to login to the database.

const { Client } = require('pg')

const startApp = async ()=>{
  client = new Client({
    host: '<host name>',
    port: 5432,
    user: '<username>',
    password: '<password>',
    database: '<database_name>'
  })
  await client.connect()
  console.log('Connected')
}

startApp()

2.2 Simple Commands

After connecting to Postgres, you can now send commands that Postgres can understand. These commands are called Sequel Query Language (SQL). Here are a few:

• CREATE TABLE lesson( id serial PRIMARY KEY, title VARCHAR (256) );
  • This creates a table called lesson that has 2 columns: id and title
  • id column is a primary key and the serial property means that it will automatically increase as you add rows into the table.
  • VARCHAR (256) means that title is a string of up to 256 characters.
  • Make sure you don't run this over and over again. You only need to run this command once
• INSERT INTO lesson (title) VALUES ('postgres tutorial')
  • This creates a new row in the lesson table with a title of 'postgres tutorial'.
• select * from lesson where title = 'postgres tutorial'
  • This retrieves all the rows from the lesson table that has the title "postgres tutorial'.
• UPDATE lesson SET title = 'postgres demo' WHERE id=1;
  • Retrieves the row where id =1, and then changes the title to 'postgres demo'
• DELETE FROM lesson WHERE id=1;
  • Deletes the row where id = 1

To run a Query, you run client.query(' -- YOUR SQL HERE --')

Make sure you await to resolve! All SQL commands involves sending a the SQL to your postgres application and then waiting for it to finish executing, so the commands will be asynchronous. Therefore any client actions will be asynchronous and returns a promise. To wait for a command to finish, you need an await or .then to wait for the query to resolve before continuing.

const lessons = await client.query('SELECT * from lesson;'); // gets all the lessons
console.log( lessons.rows )

2.3 Usage in your website

How would you execute these commands in your website? Here are a few sample code to build an API:

app.post('/api/lessons', async (req, res) => {
  await client.query(`INSERT INTO lesson (title) VALUES ($1)`, [req.body.title])
  res.status(201).send('lesson created')
})

app.get('/api/lessons', async (req, res) => {
  const lessons = await client.query(`select * from lesson;`)
  res.json(lessons.rows)
})

app.get('/api/lessons/:id', async (req, res) => {
  const lessons = await client.query(`select * from lesson WHERE id=$1;`, [req.params.id])
  res.json(lessons.rows[0])
})

app.put('/api/lessons/:id', async (req, res) => {
  const lessons = await client.query(`UPDATE lesson SET title = $1 WHERE id=$2;`, [req.body.title, req.params.id])
  res.send('resource updated')
})

app.delete('/api/lessons/:id', async (req, res) => {
  await client.query(`DELETE lesson WHERE id=$1;`, [req.params.id])
  res.send('resource deleted')
})

2.3.1 Security

Notice how we don't create a string from user input and directly execute that query like this:

client.query(`DELETE lesson WHERE id=${req.params.id};`)

If you create an SQL query directly from user input, your database will have a security issue called (Injection vulnerability). A user could simply pass in the following as id: 1; UPDATE lesson SET title="my awesome title" WHERE id=1. The user could essentially do anything they want to your database, including deleting all your data.

To prevent this awful attack, always make sure to pass user input as the second argument into client.query. The client.query function will help you clean the user input to prevent sql injection attacks.

3 Database Design

When it comes to Postgres and other SQL databases, you must be very intentional about your data and make sure it is clear and organized. If you are building a website like c0d3 and you have a user table to store user information and a lesson table to store lesson information (like title and description), how do you store the user's progress for each lesson?

Option1 Could you add a column to the user table?

• No. Imagine someone suggested this to you. Knowing what you know so far, explain why it does not make sense to add a column to the user table.

Option2 Could you add a column to the lesson table?

• No. Imagine someone suggested this to you. Knowing what you know so far, explain why it does not make sense to add a column to the lesson table.

Creating a new table. The best solution is to create a new table called userlesson that has 3 columns:

• userId to retrieve user's information from the corresponding user table when needed.
• lessonId to retrieve lesson's information from the corresponding lesson table when needed.
• status that is a string, to store the user's status for the each lesson.

In the above example, since userId and lessonId are used to retrieve the row from the user and lesson tables respectively, they are called foreign keys. userId is a foreign key to the user table.

The id column in the user and lesson table that other tables reference to, is called the primary key. id column is a primary key for the lesson (or user) table.

Let's say you want to see what the status is for lesson with id 5 for every user in your database. How would you do that?

Option 1 : Get all the users from your database and then get their lesson status for each user:

const users =  await client.query(`select * from user;`)
const allStatuses = await Promise.all( users.rows.map( (user) => {
  return client.query(`select * from userlesson WHERE userid=$1 AND lessonid=$2;`, [user.id, lessonId])
}))

Option 2: Run 1 query using join:

const result = client.query(`

SELECT
	userlesson.status,
	user.email,
FROM
	userlesson
WHERE 
        userlesson.lessonid = $1
INNER JOIN user ON user.id = userlesson.userid

`, [lessonId])

3.1 Joins

Join helps you combine these tables together and get data from all the tables in 1 query. If you forget how to use Join, remember that you can always use option 1 to get the data you need by running multiple queries. To understand why you need join, let's analyze the two options above. Let's say:

• You have 1000 users
• Each user took 7 lessons. So your userlesson table has 7000 rows.

Option1: 1000*7000 lookups. 7 Million lookups!

• Getting all the users, 1000 rows.
• For each user
  • Look through 7000 rows in the userlesson table to find the correct userid and lessonid

Option2: 7000 lookup + magic time

• Get all the userlesson where lessonid matches. 7000 lookups
• Combine the result table with user table (magic time).

Postgres's magic time is really fast. Option 2 will be significantly faster than option 1 and with a big database it could mean the difference between waiting months and waiting minutes. If you are a data analyst, part of your job is to figure out how to join tables efficiently to get the data you need quickly.

Sometimes, you could unknowingly join tables in a way to make your query ALOT slower than option 1. The most painful part about this mistake is that when data is small, you don't notice the query taking a long time. However, as the company grows over the years and more data is accumulated, the code is forgotten and the application becomes really, really slow. This problem is very difficult to fix and to avoid writing joins directly, most companies use an Object Relational Mapping (ORM) library to generate the SQL queries for you. This not only avoids the problem of engineers joining the tables incorrectly but also allows developers to use the database without knowing SQL.

4 ORM

In the previous section, you learned how to directly connect to your postgres database, execute simple queries, and execute more complex queries using join. These SQL queries are hard to remember, manage, and could be written incorrectly to make the queries really slow. To solve these problems, most production systems use an Object Relational Mapping (ORM) library. We will use the most common one called sequelize in this section. Sequelize converts your JavaScript functions into SQL commands for you.

4.1 Connection and models

To use sequelize, you must first connect to your database and then create the necessary tables.

const { Sequelize, DataTypes } = require('sequelize')

// Defining your database connection
const sequelize = new Sequelize('database', 'username', 'password', {
  host: '<host for database>',
  dialect: 'postgres'
});

// Creating a table called lesson
const Lesson = await sequelize.define('lesson', {
  title: {
    type: DataTypes.STRING,
    allowNull: false
  }
})
await sequelize.sync({ alter: !!process.env.ALTER_DB })

await sequelize.authenticate()

// Update title
await Lessons.update( {
  title: "new value"
}, {
  where: { id: 1 }
})

await Lessons.destroy( {
  where: { id: 1 }
})

await Lessons.findAll()

await Lessons.findAll( {
  where: { id: 1 }
})

await Lessons.create({
  title: 'new title'
})

5 Scaling

in /lib/user.js there should be a resetUserPassword function.

• Takes in 2 arguments (token) and password.
• no token, throw error
• base64 unencrypt token
• JSON.parse
• get userId from object
• find user in the database with the userId
• throw an error if user is null
• check if token in the database and token in the object matches
• throw an error if doesn't match
• throw an error if expiration < Date.now()
• bcrypt user password
• store encrypted password into database.

Added github actions: https://github.com/garageScript/databases/pull/91/files

Builds are failing. Figure out why.

AC

• Create account model
• Create functions: createUser, updateUserDBPassword, confirmUserAccount, createSession

confirmUserAccount(token)

• Looks up user db by token
• if token has expired, throw error
• mark account as confirmed
• tokenExpire timestamp

Affects:

https://github.com/garageScript/databases/pull/108/files

Tutorial: https://scotch.io/tutorials/use-ejs-to-template-your-node-application

1. make sure partials are put into /views/partials folder.
2. Sample code: <% include ../partials/head %>

Goal

As a engineer, I want to be able to look at my monitoring systems to make sure nothing is broken and set up alerts. For example, if database is down for some reason, I should get alerted right away.

If a user reports an issue, I should be able to look at the logs and figure out what the issue is.

Basic Requirements

Logging module must support these features:

logger.error(....)
logger.log(...)

For now our logging module can just use console.log or console.error

Research

• Figure out which monitoring systems to use.
• Figure out if there is an existing logger (like winston) that already does this so we don't have to build our own logging system.
• Suggestion Multer

Acceptance criteria

• Must log each file name automatically using closure:
  • const log = require('./log')(__filename)
• Support log, warn, error
  • error is used for alerts.
  • warn is used for analysis.
  • log is informational and debugging
• Send a PR to change pg module to use logger module: https://github.com/garageScript/databases/pull/2/files

/lib/user.js should have a function called sendPwReset

• Takes in a user object model
• Creates a new token. Maybe crypto.randomBytes
• JSON stringify this: {userId: id, userToken: token}, then base64encode it.
• Sends Confirmation Email with the encoded data above
• Updates account token and expiration date

1. Takes in a userAccount object (like sendUserPasswordReset) function as parameter.
2. Modify db.js to have one to many relationships. User has many databases through UserDatabases.
3. Database needs to have a text field.

Updating db password

PATCH /api/users/:id

Acceptance:

• startMongoDB function must start the database
• closeMongoDB function must close the database
• createMongoDB function must create an account.
• deleteMongoDB function must delete an account.
  https://www.npmjs.com/package/mongodb

Create username and password and a submit button, add bootstrap to make it look nice.

• confirm user function should take in a token
• Lookup user model by token
  • throw error if no user is found (invalid token)
• Look at token expiration date
  • throw error if token's expiration date < now.
• Update user model by setting user state to confirmed

Current progress: https://github.com/garageScript/databases/pull/2/files

Acceptance:

• startPGDB function must start the PG database
• closePGDB function must close the PG database
• createPGAccount function must create an account.
• deletePGAccount function must delete an account.