Setup SQL and Hookup User Model

[Deon]

A few things:

• DB Design for Users
• Some SQL server needs to be setup
• Add some library to interface with it
• Hook that up to the User model.
• Add Migrations Library
• Add Migrations
• Add a DB Mock Library
• Update tests for the User model

This can be broken out into multiple tasks

[Deon]

User Table draft, based off of the current Mongoose Schema:

Also added a friends and friends request table. Please comment with suggested changes. I'll update this comment directly with changes.

CREATE TABLE users IF NOT EXISTS (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL,
  password CHAR(60) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  username VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  password_reset_token BINARY(32),
  password_reset_token_expiry TIMESTAMP,
  PRIMARY KEY(id)
);

CREATE TABLE friends IF NOT EXISTS (
  requestor INT UNSIGNED NOT NULL,
  requestee INT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  accepted BOOLEAN NOT NULL,
  PRIMARY KEY(requestor, requestee),
  FOREIGN KEY(requestor) REFERENCES users(id),
  FOREIGN KEY(requestee) REFERENCES users(id)
);

CREATE INDEX friend_accepted ON friends(accepted);

[Deon]

Alternatively, we can merge friends/friend_requests into one and add a state enum (I think this is cleaner?).

Also, open to changes on friends key names.

[yucombinator]

Yeah we should merge them. Also do we need a requestor / requestee distinction? Or user1/user2 would be fine.

[Deon]

IMO yes b/c then we can distinguish between friend requests you've made, and requests others have made (at least it's more descriptive)

[yucombinator]

We can keep it that way (but I think in terms of UI we won't show anything differently once the request is accepted)

[Deon]

Agreed with the UI post-acceptance.

[Deon]

Updated DB design to reflect table merge, added index on acceptance.

[yucombinator]

👍