Working with the Database

WeProof uses PostgreSQL as its primary database technology, with knex and Bookshelf providing useful Javascript interfaces and ORM respectively.

As discussed in the Quick Start page, the database must first be set up by ensuring PostgreSQL is running, and making the database migrations for both the development and test databases.

$ postgres -D /usr/local/var/postgres 
$ createdb weproof
$ createdb weproof-test

Once this is done, you can read/write to and from the database through the application without any problems.

Modifying the schema

knex provides excellent tools for making and rolling back migrations in order to modify the database schema. If you wish to modify a current table, or even create a new one, you must first create a new migration. Do this by ensuring you are in the /server directory and running the following command:

$ knex migrate:make <migration_name> --env <development | test> --knexfile src/db/knexfile.js

This will create an empty migration with the name <migration_name>. Inside, it will look something like this:

exports.up = function(knex, Promise) {
  
};

exports.down = function(knex, Promise) {
  
};

This project uses ES6 syntax almost exclusively, so change the syntax to match the rest of the code

export function up(knex, Promise) {
  
};

export function down(knex, Promise) {
  
};

The up function describes changes to the schema that will occur when the migrations are made. The down function describes changes to the schema when this migration is rolled back or "undone". Therefore any changes you make in the up function should be completely reversed in the down function. Take the following as an example. Here, we create a table called user with the attributes username and password. In the up function, we specify the table name and the attributes, and in the down function, we simply indicate that we want to drop the table.

export function up(knex, Promise) {
  return knex.schema.createTable('users', (table) => {
    table.increments();
    table.string('username').notNullable().unique();
    table.string('password').notNullable();
  });
};

export function down(knex, Promise) {
  return knex.schema.dropTable('users');
};

Once we're happy with the migration, we can make these changes to the schema by running a custom script:

$ npm run migrate:development
// or
$ npm run migrate:test

Interacting Through the Shell

Browsing the database directly through the shell is often useful for double checking its contents. This is fairly straight forward, but here are some useful commands to help you get started.

Open the shell by running:

$ psql weproof

From here, there are a handful of useful commands to know right off the bat.

weproof=# \q            // quit
weproof=# \x auto       // set display mode to auto-expand
weproof=# \dt           // list all tables
weproof=# \d+ <table>   // view column information for a given table