Getting started with the Knex migration CLI
Authentication only works if we have some way to say “yes” or “no” when determining if a user exists or not. To do that, we need to store data somewhere – some sort of base location to put data… Since we’re using Knex, we need a knexfile – not something I’m familiar with. Apparently the documentation on the knexfile itself isn’t great though… To use the migration CLI (and I want to use the migration CLI), you need a knexfile. I think the migration CLI is a big value add for handling databases. Using the equivalent in Rails, it makes application upgrades easy and developing with schema changes something that’s just a run of the mill activity instead of a headache. To that end, I’m kind of just blazing ahead here. We still don’t want passwords in version control, so I’m continuing to use dotenv here.
It’s important that we use the database administrator account we made before. In fact, this is basically the whole reason we made the administrator account.
// knexfile.js
require('dotenv').config();
module.exports = {
development: {
client: 'postgres',
connection: {
host: process.env.DB_HOST,
database: process.env.DB_NAME + "_development",
user: process.env.DB_ADMIN,
password: process.env.DB_ADMIN_PASSWORD
}
},
test: {
client: 'postgres',
connection: {
host: process.env.DB_HOST,
database: process.env.DB_NAME + "_test",
user: process.env.DB_ADMIN,
password: process.env.DB_ADMIN_PASSWORD
},
pool: {
min: 2,
max: 10
}
},
production: {
client: 'postgres',
connection: {
host: process.env.DB_HOST,
database: process.env.DB_NAME + "_production",
user: process.env.DB_ADMIN,
password: process.env.DB_ADMIN_PASSWORD
},
pool: {
min: 2,
max: 10
}
}
};As a sanity check, ensure everything is all set up properly:
...\min-auth-server λ knex migrate:currentVersion Using environment: development Current Version: none
This could yield all sorts of different issues – permissions are wrong, password is wrong, schema is wrong. Regardless, its best to sort this out while we have the fewest possible things to worry about. You can also go check out what this has done to the database itself (spoiler alert, it has added 2 tables).
min_auth_development=> \dt min_auth.*
List of relations
Schema | Name | Type | Owner
----------+----------------------+-------+----------------
min_auth | knex_migrations | table | min_auth_admin
min_auth | knex_migrations_lock | table | min_auth_admin
(2 rows)Schema considerations
This is perhaps one of the harder decisions so far. All the other decisions about technologies, frameworks, libraries, etc. guide development more than they do the end application. This is where things start to narrow to what the actual purpose of the application is. We want to stay relatively general so that we have plenty of room to grow, but we also don’t want to over complicate things. With that taken into consideration, I think it’s appropriate to create both a users table and a roles table. This will allow us to govern permissions separately from users by bundling up particular permissions with roles. To enable a many-to-many relationship, we’ll use a join table as well.
Note that we’ll be using bcrypt, and because of that we don’t need to store the password salt separately. With bcrypt, a provided known-length salt is appended to the hashed password as opposed to before the hashing, as you might expect. Read about it if you don’t believe me.
Generated with ERDPlus
I haven’t done a lot of particularly rigorous schema design, so if I’m asserting that usernames must be unique, I’m not sure why a its valuable to add a user_id as well. If you have some idea, please let me know. Seems like there’s at least some rationale in the StackOverflow answer here.
Let’s create 3 separate migration files, one for each of the tables. We can use the knex defaults
// in min-auth-server
λ knex migrate:make users
λ knex migrate:make roles
λ knex migrate:make users_rolesOpen them up and edit them. This part is just reading the Knex documentation and creating the schema above. Note that we’re using citext for the user name and email – this is a case insensitive string, which means its less suitable for display back to the user but more useful for uniqueness constraints
// {datetime_stamp}_users.js
exports.up = (knex, Promise) => {
return knex.schema.createTable('users', (table) => {
table.increments('user_id');
table.specificType('user_name', 'citext').unique().notNullable();
table.specificType('user_email', 'citext').notNullable();
table.string('password').notNullable();
table.timestamps();
});
};
exports.down = (knex, Promise) => {
return knex.schema.dropTable('users');
};// {datetime_stamp}_roles.js
exports.up = (knex, Promise) => {
return knex.schema.withSchema(process.env.DB_SCHEMA_NAME).createTable('roles', (table) => {
table.increments('role_id');
table.string('role_name').unique().notNullable();
table.timestamps();
});
};
exports.down = (knex, Promise) => {
return knex.schema.withSchema(process.env.DB_SCHEMA_NAME).dropTable('roles');
};// {datetime_stamp}_users_roles.js
exports.up = (knex, Promise) => {
return knex.schema.withSchema(process.env.DB_SCHEMA_NAME).createTable('users_roles', (table) => {
table.integer('user_id').unsigned();
table.foreign('user_id').references('user_id').inTable('users');
table.integer('role_id').unsigned();
table.foreign('role_id').references('role_id').inTable('roles');
table.primary(['user_id', 'role_id']);
});
};
exports.down = function (knex, Promise) {
return knex.schema.withSchema(process.env.DB_SCHEMA_NAME).dropTable('users_roles');
};Then a quick knex migrate:latest gets us our schema.
λ knex migrate:latest
Using environment: development
Batch 1 run: 3 migrations
...\min-auth-server\migrations\20170202213909_users.js
...\min-auth-server\migrations\20170202230126_roles.js
...\min-auth-server\migrations\20170202230148_users_roles.jsAnd taking another peek in the database to check things out:
min_auth_development=> \dt min_auth.*
List of relations
Schema | Name | Type | Owner
----------+----------------------+-------+----------------
min_auth | knex_migrations | table | min_auth_admin
min_auth | knex_migrations_lock | table | min_auth_admin
min_auth | roles | table | min_auth_admin
min_auth | users | table | min_auth_admin
min_auth | users_roles | table | min_auth_admin
(5 rows)You can see how Knex keeps track of the migrations as well:
min_auth_development=> SELECT * FROM knex_migrations;
id | name | batch | migration_time
----+-------------------------------+-------+----------------------------
13 | 20170202213909_users.js | 1 | 2017-02-05 01:43:20.148+00
14 | 20170202230126_roles.js | 1 | 2017-02-05 01:43:20.209+00
15 | 20170202230148_users_roles.js | 1 | 2017-02-05 01:43:20.263+00
(3 rows)Excellent. Check out the users table, just to make sure everything lines up:
min_auth_development=> \d+ users
Table "min_auth.users"
Column | Type | Modifiers | Storage
------------+--------------------------+---------------------------------------------------------+----------
user_id | integer | not null default nextval('users_user_id_seq'::regclass) | plain
user_name | citext | not null | extended
user_email | citext | not null | extended
password | character varying(255) | not null | extended
created_at | timestamp with time zone | | plain
updated_at | timestamp with time zone | | plain
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
"users_user_name_unique" UNIQUE CONSTRAINT, btree (user_name)
Referenced by:
TABLE "users_roles" CONSTRAINT "users_roles_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(user_id)Looks great! Contains everything we wanted to see. Migrations applied successfully, and we’re in business. If something went wrong you can use knex migrate:rollback to undo it (yay CLI!).
Adding an authentication route
Before we get into the meat of signing in users and managing their sessions, we need a place for the work to happen. Time to add our first route to the server! As an unopinionated technology, Express leaves it wide open for how developers should structure their apps. I’m going to go ahead with a something that looks (so far as I can tell) as though it’s conventional. I’ll create a routes folder, then within that folder I’ll create a users folder to hold user related routes.
$ mkdir routes\users $ touch routes\users\index.js
We’ll just add a couple placeholder routes to get this moving – post requests are ever so slightly harder to see, so for right this instant we’ll add a get request – just so we can see it working.
// routes\users\index.js
const routes = require('express').Router();
routes.post('/api/users/sign-in', (req, res) => {
res.status(200).json({ message: "To-do: implement this..." });
});
routes.post('/api/users/sign-up', (req, res) => {
res.status(200).json({ message: "To-do: implement this..." });
});
routes.post('/api/users/sign-out', (req, res) => {
res.status(200).json({ message: "To-do: implement this..." });
});
// This route is only for demonstration, don't include it
routes.get('/api/users/sign-in', (req, res) => {
res.status(200).json({ message: "To-do: implement this..." });
});
module.exports = routes;Now if we start it up with yarn start-server and visit (i.e. send a GET request to) http://localhost:3000/api/users/sign-in then we can render a route that’s not part of the Angular application:
Connect the client
Now that we have a route on the server (other than our default route which serves the Angular application), we may as well hook in the client side. It won’t be very exciting right now, but at least it gives us a pretty good idea what’s going on. Back in the ol’ user.service we’ll rejigger things a bit and start talking to the server.
// user.service.ts
-import { Http } from '@angular/http';
+import { Http, Headers, RequestOptions } from '@angular/http';
import { Injectable } from '@angular/core';
+import { Observable } from 'rxjs/Rx';
+import 'rxjs/add/operator/map';
+import 'rxjs/add/operator/catch';
...
export class UserService {
+ private HEADERS = new Headers({ 'Content-Type': 'application/json' });
...
signIn(email: string, password: string) {
- // This would be where we call out to the server to authenticate
- // We'll use 'token' as a placeholder for now
- localStorage.setItem(this.authToken, 'token');
- this.signedIn = true;
+ this.http.post('/api/users/sign-in', { username: email, password: password }, { headers: this.HEADERS })
+ .map(response => response.json())
+ .subscribe(
+ next => this._signUserIn(next),
+ error => console.error(error),
+ );
}
create(username: string, email: string, password: string) {
- // Obviously this is not what this function will ultimately do
- this.signIn(email, password);
+ this.http.post('/api/users/sign-up', { username: username, email: email, password: password }, { headers: this.HEADERS })
+ .map(response => response.json())
+ .subscribe(
+ next => this.signIn(email, password),
+ error => console.error(error),
+ );
}
signOut() {
- localStorage.removeItem(this.authToken);
- this.signedIn = false;
+ this.http.post('/api/users/sign-out', {}, { headers: this.HEADERS })
+ .map(response => response.json())
+ .subscribe(
+ next => this._signUserOut(next),
+ error => console.error(error),
+ );
}
isSignedIn() {
return this.signedIn;
}
+
+ _signUserIn(response) {
+ localStorage.setItem(this.authToken, 'token');
+ this.signedIn = true;
+ }
+
+ _signUserOut(resposne) {
+ localStorage.removeItem(this.authToken);
+ this.signedIn = false;
+ }
}Now if you build the application, run the server, and try signing in and out, you should still be able to do everything. The only difference is now the client is talking to the server before signing you in. Should be obvious, but this isn’t doing *any* authentication at all. Everyone who submits a form gets “signed in”. The client side UI should look the same, but now if you watch your server logs, you should be able to see requests coming in. Remember to build and deploy the client to the server before running, and to also make sure that you start at the root of the site (http://localhost:3000/) so you’re not reusing an old Angular client side.
λ yarn start-server
yarn start-server v0.21.0-20170203.1747
$ cd min-auth-server && yarn && nodemon
yarn install v0.21.0-20170203.1747
[1/4] Resolving packages...
success Already up-to-date.
Done in 0.34s.
[nodemon] 1.11.0
[nodemon] to restart at any time, enter `rs`
[nodemon] watching: *.*
[nodemon] starting `node ./bin/www`
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Alpine 6.2.1) 6.2.1 20160822, 64-bit
// Server has started by this point
// Visit the root page
GET / 200 18.613 ms - 721
GET /styles.d41d8cd98f00b204e980.bundle.css 200 7.858 ms - -
GET /inline.1f47894ebb65f78c7be0.bundle.js 200 6.067 ms - 1460
GET /polyfills.807187ab19f977ed98f4.bundle.js 200 6.560 ms - 83835
GET /main.f6dff3649a5430d68d05.bundle.js 200 7.930 ms - 77077
GET /vendor.fa7f6c980c531ee6e4ce.bundle.js 200 7.399 ms - 465628
// Angular app has been delivered
// Try signing in
POST /api/users/sign-in 200 35.469 ms - 38
// ...and signing out
POST /api/users/sign-out 200 12.310 ms - 38
// ...and signing up
POST /api/users/sign-up 200 5.199 ms - 38
POST /api/users/sign-in 200 0.911 ms - 38
// ...and signing out again
POST /api/users/sign-out 200 1.972 ms - 38Now we’ve got the first pieces of the client talking to the server. Best of all, we didn’t break the client!
Technically Rural 