Basics

Access the database

• create a user (sudo adduser username) o use your existing one (if unsure, run whoami)
• add a "role" for the desired user: sudo -u postgres createuser --interactive and insert the username
• if needed, create a database: sudo -u username createdb dbname
• run psql: sudo -u username psql -d dbname. Note that, if the -d flag is omitted, psql will look for a db named as the user
• once in psql, run: grant ALL privileges on database dbname to username;
• once in psql, run: alter user username with encrypted password 'passw'l

Migration

Knex.js is the tool chosen for migration.

Setup

To install Knex, you can use npm: npm install knex -g In order to perform setup, run knex init This will create a file named knexfile.js, containing a module.exports object which is meant to contain different cases of use (developement, staging, production), but for the moment there will be no differentiation and the file should look like this:

module.exports = {
  client: 'postgresql',     // use PostgreSQL
  connection: process.env.DATABASE_URL || {     // try to retrieve credential from .env file 
    user: 'greentracker',     // if not possible, these are the default credentials 
    database: 'greentrackerdb', 
    password: 'greentrackerpw'
  }
};

Create migration

Once your greentrackerdb is ready (look above to see how), you can create a migration that will "copy" its structure: knex migrate:make migration_name

Update

To update database to the migrated state, run knex migrate:latest

Automate the process

In package.json, insert the following:

"scripts": {
  "migrate": "knex migrate:latest",
  "start": "npm run migrate && node index.js"
}

[http://vincit.github.io/objection.js/#introduction for seed examples]

Tables

create table users(
  user_id integer primary key,
  username varchar(50)
);
create table habits(
  user_id integer,
  foreign key (user_id) references users (user_id),
  time timestamp without time zone, 
  choice varchar(10)
);