INCOMPLETE: Half thoughts: init (Postgres & Knex)

WB todoMVC MASTER (a work in progress)

I’ve chosen Postgresql for my database and want to drive management of the database schema with knex-migrate & knex-seed.

Knex-migrate allows you to generate middleware boiler plate to manage psql scripts that will create/modify the database schema. Once this is done, you’ll be able to track/modify/automate which version of your database you are using.

Knex-seed (a tool that comes with the npm package knex-migrate) allows you automate the population of data to your database.

Gut assumptions on best practices & psuedo strat:

  1. Your application should not be able to access/modify all databases on your postgres server.
  2. You will need to create a psql user that only has access to the database/databases that belong the the application you are creating.
  3. This will require that you manually create the empty database with an admin user.
  4. Next create an app specific user. Example: todosystem
  5. Grant “todosystem” all privileges for app specific database(s). Example: “Todo”
  6. Init knex-migrate
  7. Configure database connection with app specific username/password
  8. WORK IN PROGRESS… MANY EDITS/ADDITIONS NEEDED… CRITIQUES/SUGGESTIONS WELCOME…

Postgres gotchas

  • Semicolon Semicolon Semicolon:
    If you run into odd issues, chances are you forgot to end your execution statement with a semicolon.
  • To double quotes or not?
    I don’t fully understand this, but sometimes you will want to wrap dbnames and other psql types in “” double quotes when creating them. If you do this, and you try to modify a db without wrapping the reference in “” double quotes, you will get an error. To fix, try referencing the database with the name wrapped in “” double quotes.
    ** it appears using double quotes will force case sensitivity

Init database and user

Open terminal on mac and start psql as Superuser

$ psql -U postgres

Verify that you are logged in as Superuser postgres

# select current_user;

Create an empty database

# CREATE DATABASE “Todo”;

Create app specific user “todosystem”

# CREATE USER “todosystem” WITH PASSWORD 'todosystempassword';

Grant todosystem all permissions for empty DB

# GRANT ALL ON DATABASE “Todo” TO “todosystem”;

Logout/quit psql

# \q

Verify todosystem can only log in to approved DB
TRY:

$ psql -U todosystem

Should result in:

psql: FATAL: database “todosystem” does not exist

TRY

$ psql -U todosystem -d Todo

Should result in: Successful login

Create a test table

> CREATE TABLE testtest (id serial PRIMARY KEY);

Drop test table

> DROP TABLE testtest;

Init knex

Open a new terminal window

Navigate to where ever your project lives

$ cd ~/Sites/swag/todo-api

Create a .knex folder to manage DB migrations

$ mkdir .knex

Change to knex directory

$ cd .knex

Install knex globally

$ npm install knex -g

Install knex for project

$ npm install knex --save

Initialize knex

$ knex init

TO BE CONTINUED…

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.