My First Time with PostgreSQL
How to do PostgreSQL development
I am always interested in learning new technologies, and the more I try new things, the easier it feels to pick up a new framework/technology. I have recently come across a GitHub repo which is an open source version of Venmo. Big shoutout to Aaron for sharing the code on GitHub.
The app that is hosted in that repo is not a live app, it is a demo app. You can’t use it for sending real money. However, the open sourced repo is a great way for you to learn about Postgresql, encryption, deployment and overall it is a nice full-stack app where you can study the code and new learn things.
First, I downloaded the PostgreSQL(pronounced post-gress-Q-L) from here. It comes with nice database client called pgAdmin4 which is a Windows Client that I used for local development.
I created a database called paymoo and used the script that was in the repo to create tables. I also added a command in the package.json called
"dev": "concurrently \"npm run client\" \"npm run server\"",
This allows to run the client and the server same time.
I wanted to study the code and add some features. When I looked at the code, I immediately saw there was a room for improvement and I could do the following five things.
1. Use UUIds
I wanted to change primary keys in the users database from integers to universal unique identifiers(uuid). uuids are keys that look like this:c93f779d-a1b3-41ef-be33-df0994778a62. More info could be found here
This is important because if you see that your id is 200. You can try to fetch user 199’s data. So, by using the uuids, it is really hard to guess.
So before the SQL statements that I used were like these:
CREATE TABLE USERS (
id SERIAL PRIMARY KEY,
username varchar(20) UNIQUE NOT NULL,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL,
updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
created_at timestamp DEFAULT CURRENT_TIMESTAMP,
phone varchar(11) UNIQUE NOT NULL,
password varchar(64) NOT NULL,
email varchar(64) UNIQUE NOT NULL,
avatar_url varchar(500)
);
CREATE TABLE USERS_TRANSACTIONS (
txn_id SERIAL PRIMARY KEY,
payer_id INT REFERENCES USERS(id),
payee_id INT REFERENCES USERS(id)
);
CREATE TABLE TRANSACTIONS (
txn_id int PRIMARY KEY REFERENCES USERS_TRANSACTIONS(txn_id),
amount NUMERIC(10,2),
note VARCHAR(1000),
created_at timestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE BALANCE (
user_id INT PRIMARY KEY REFERENCES USERS(id),
amount NUMERIC(10,2)
);
I used to right right click on the database on the pgAdmin4 client DB client and click CREATE SCRIPT option, and then paste the file content there. To add the uuids, I had to modify the script content like this:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE USERS (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
username varchar(20) UNIQUE DEFERRABLE INITIALLY DEFERRED,
first_name varchar(20) NOT NULL,
last_name varchar(20) NOT NULL,
updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
created_at timestamp DEFAULT CURRENT_TIMESTAMP,
phone varchar(11) NOT NULL,
password varchar(64) NOT NULL,
email varchar(64) NOT NULL,
avatar_url varchar(500)
);
CREATE TABLE USERS_TRANSACTIONS (
txn_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
payer_id UUID DEFAULT uuid_generate_v4() REFERENCES USERS(id),
payee_id UUID DEFAULT uuid_generate_v4() REFERENCES USERS(id)
);
CREATE TABLE TRANSACTIONS (
txn_id UUID PRIMARY KEY DEFAULT uuid_generate_v4() REFERENCES USERS_TRANSACTIONS(txn_id),
amount NUMERIC(10,2),
note VARCHAR(1000),
created_at timestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE BALANCE (
user_id UUID PRIMARY KEY DEFAULT uuid_generate_v4() REFERENCES USERS(id),
amount NUMERIC(10,2)
);
PostgreSQL syntax is slightly different than MySQL, but overall they are close. First CREATE EXTENSION line imports the function that would generate the uuids. More info on the uuid can be found at the (docs)[https://www.postgresql.org/docs/9.4/uuid-ossp.html]
2. Hash passwords before inserting them to the database.
This is very important since the databse shouldn’t have the plan text passwords.
Before the code was storing the passwords in plain text. I chose to do the encryption on the front end with bcrypt npm package using the BlowFish Algorithm. This article told me not to use sha family or md5 type hashing as it explained that the they are not secure and could be used for data integrity(you download something from the web and see if the hash is what is reported to see if you are downloading what you are supposed to), but not for encrption.
Below is Sign up before hashing
const pg = require('./index.js').pg;
const newUserSignup = function(signupData, startingAmount) {
let userId = undefined;
return pg.transaction(userInsert => {
return pg.table('users')
.transacting(userInsert)
.returning('id')
.insert({
username: signupData.username,
first_name: signupData.firstName,
last_name: signupData.lastName,
// TODO: hash password upon insertion
password: signupData.password,
phone: signupData.phone,
email: signupData.email,
avatar_url: signupData.avatarUrl ? signupData.avatarUrl : null
})
.then(id => {
userId = id[0];
return userId;
})
.then(id => {
return pg.table('balance')
.transacting(userInsert)
.insert({
user_id: id,
amount: startingAmount
})
})
.then(userInsert.commit)
})
.then(() => {
return userId;
})
}
module.exports = {
newUserSignup: newUserSignup
}
Below is Sign up after hashing
const pg = require('./index.js').pg;
const bcrypt = require('bcrypt');
// Hash password before inserting into db
const saltRounds = 10;
console.log("store");
const hashUserPassword = async (password) => {
bcrypt.hash(password, saltRounds, function(err, hash) {
// Store hash in your password DB.
if (err) {
throw err;
}
}).then(async hash => {
return await hash;
}).catch(err => {
console.log(err);
})
}
const newUserSignup = async (signupData, startingAmount) => {
let userId = undefined;
const hashed_password = await hashUserPassword(signupData.password);
return pg.transaction(userInsert => {
return pg.table('users')
.transacting(userInsert)
.returning('id')
.insert({
username: signupData.username,
first_name: signupData.firstName,
last_name: signupData.lastName,
password: hashed_password,
phone: signupData.phone,
email: signupData.email,
avatar_url: signupData.avatarUrl ? signupData.avatarUrl : null
})
.then(id => {
userId = id[0];
return userId;
})
.then(id => {
return pg.table('balance')
.transacting(userInsert)
.insert({
user_id: id,
amount: startingAmount
})
})
.then(userInsert.commit)
})
.then(() => {
return userId;
}).catch(err => {
return res;
})
}
module.exports = {
newUserSignup: newUserSignup
}
Once we hash the password, the login query also needs to be changed. We do the password authentication when user provides an input, we try to see if we can get the hash stored in the database when the user provides a password. From the hash, it is really go backwards and try to predict the password.
Below is Log in before hashing
app.post('/login', (req, res) => {
var {username, password} = req.body;
db.getPasswordAtUsername(_.escape(username.replace(/"/g,"'")), (err, row) => {
if (err) {
console.error("Error retrieving from database: ", err);
res.status(500).json(err);
} else {
if (row.length) {
if (row[0].password === password) {
res.status(200).json({ userId: row[0].id });
} else {
res.status(401).json({ error : "Incorrect password"});
}
} else{
res.status(401).json({ error : "Invalid username"});
}
}
});
});
Below is Log in after hashing
app.post('/login', (req, res) => {
var {username, password} = req.body;
db.getPasswordAtUsername(_.escape(username.replace(/"/g,"'")), (err, row) => {
if (err) {
console.error("Error retrieving from database: ", err);
res.status(500).json(err);
} else {
if (row.length) {
// Compare if password can generate the hash stored in the db
bcrypt.compare(password, row[0].password, (err, hashing_res) => {
if (err) {
res.status(401).json({ error : "Incorrect password"});
console.log(err);
return
}
if (hashing_res===true){
res.status(200).json({ userId: row[0].id })
}
})
} else {
res.status(401).json({ error : "Invalid username"});
}
}
});
});
row[0].password is the hash that we store and we see if the user input hash can yield the same result. If so, we authenticate the user.
3. Deploy to Heroku.
I wanted to share the app, so I deployed the app with Git using two commands:
heroku create
git push heroku master
The app can be found here: https://paymoo.herokuapp.com
4. Assign random Avatar URLs.
Before the users were prompted to input a Avatar URL during signin up, but I wanted to generate one myself for users at run-time instead of asking the user. I used a library called faker. I will use the same library in step 5 to flood the database with random users.
Below is Sign Up Component before Avatar URL
import React from 'react';
import axios from 'axios';
import { Link } from 'react-router-dom';
import NavBar from './Navbar.jsx';
import FlatButton from 'material-ui/FlatButton';
import { ValidatorForm } from 'react-form-validator-core';
import { TextValidator} from 'react-material-ui-form-validator';
class SignUp extends React.Component {
constructor (props) {
super(props);
this.state = {
formData: {
username: '',
email: '',
firstName: '',
lastName: '',
phone: '',
password: '',
avatarUrl: '',
},
submitted: false,
didSignupFail: false,
errorCode: null
}
}
handleInputChanges (event) {
const { formData } = this.state;
formData[event.target.name] = event.target.value;
this.setState({ formData });
}
signUserUp() {
this.setState({ submitted: true }, () => {
setTimeout(() => this.setState({ submitted: false }), 5000);
});
let user = this.state.formData;
axios.post('/signup', user)
.then((response) => {
let userId = response.data.userId;
this.props.logUserIn(userId);
this.props.history.push('/');
})
.catch((error) => {
if (error.response && error.response.status === 422) {
console.log('error authenticating user errors', error.response)
this.setState({
didSignupFail: true,
errorCode: 422
})
} else {
console.log('Error in component', error.response)
this.setState({
didSignupFail: true,
errorCode: 500
})
}
});
}
...
...
render() {
const { formData, submitted } = this.state;
return (
<div>
<NavBar isLoggedIn={false} />
<div className='body-container'>
<div className='form'>
<ValidatorForm
ref="form"
onSubmit={this.signUserUp.bind(this)}
onError={errors => console.log(errors)}
>
<TextValidator
floatingLabelText="Avatar URL (Optional)"
onChange={this.handleInputChanges.bind(this)}
value={formData.avatarUrl}
name="avatarUrl"
/><br/>
<div>
<button className='btn' onClick={this.signUserUp.bind(this)}>Create Account</button>
</div>
</ValidatorForm>
</div>
</div>
</div>
);
}
Since we are not generating a URL, we ask the user with a TextValidator in the form. But we can remove that input and assing a random avatar url on form submit.
Below is Sign Up Component after Avatar URL is added
import React from 'react';
import axios from 'axios';
import { Link } from 'react-router-dom';
import NavBar from './Navbar.jsx';
import FlatButton from 'material-ui/FlatButton';
import { ValidatorForm } from 'react-form-validator-core';
import { TextValidator} from 'react-material-ui-form-validator';
const faker = require('faker');
class SignUp extends React.Component {
constructor (props) {
super(props);
this.state = {
formData: {
username: '',
email: '',
firstName: '',
lastName: '',
phone: '',
password: '',
avatarUrl: '',
},
submitted: false,
didSignupFail: false,
errorCode: null
}
this.assignAvatarUrl = this.assignAvatarUrl.bind(this);
this.signUserUp = this.signUserUp.bind(this);
}
assignAvatarUrl () {
const url = faker.internet.avatar();
this.setState({ avatarUrl: url});
}
handleInputChanges (event) {
const { formData } = this.state;
formData[event.target.name] = event.target.value;
this.setState({ formData });
}
signUserUp() {
this.assignAvatarUrl.bind(this);
this.setState({ submitted: true }, () => {
setTimeout(() => this.setState({ submitted: false }), 5000);
});
let user = this.state.formData;
axios.post('/signup', user)
.then((response) => {
let userId = response.data.userId;
this.props.logUserIn(userId);
this.props.history.push('/');
})
.catch((error) => {
if (error.response && error.response.status === 422) {
console.log('error authenticating user errors', error.response)
this.setState({
didSignupFail: true,
errorCode: 422
})
} else {
console.log('Error in component', error.response)
this.setState({
didSignupFail: true,
errorCode: 500
})
}
});
}
5. Flood the database with random fake users.
I wanted to create a script that would insert about thousands of fake users with one command. This step was my favorite overall. I tried a million on my 2.5 GHz 8 GB RAM Windows Computer and I got a hep-out-of-memory error. I later checked that Chrome had about 1.5 GB memory at most. Also, the password hashing is actually a very slow process, so I kept the number of users around 30k.
const axios= require('axios');
const faker = require('faker');
const USER_NUMBER = 10_000;
const signUserUp = (user) => {
axios.post('http://127.0.0.1:3000/signup', user)
.then((response) => {
console.log(response.data);
})
.catch((error) => {
if (error.response && error.response.status === 422) {
console.log('error authenticating user errors', error.response)
} else {
console.log('Error in component', error.response);
//console.log('Attempted user', user);
}
});
}
for (var i=0;i<USER_NUMBER;i++) {
let user = {
username: faker.internet.userName(),
email: faker.internet.email(),
firstName: faker.name.firstName(),
lastName: faker.name.lastName(),
phone: faker.phone.phoneNumberFormat().replace(/-/g, ""), //111-222-3333 is trimmed down to be 1112223333
password: faker.internet.password(),
avatarUrl: faker.internet.avatar()
}
//console.log(user);
signUserUp(user);
};
I hope this post gives some insights regarding password encryption, postgreSQL development and full-stack deployment.