INCOMPLETE: Half thoughts: init (Postgres & Knex)
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:
- Your application should not be able to access/modify all databases on your postgres server.
- You will need to create a psql user that only has access to the database/databases that belong the the application you are creating.
- This will require that you manually create the empty database with an admin user.
- Next create an app specific user. Example: todosystem
- Grant “todosystem” all privileges for app specific database(s). Example: “Todo”
- Init knex-migrate
- Configure database connection with app specific username/password
- WORK IN PROGRESS… MANY EDITS/ADDITIONS NEEDED… CRITIQUES/SUGGESTIONS WELCOME…
- 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”;
Verify todosystem can only log in to approved DB
$ psql -U todosystem
Should result in:
psql: FATAL: database “todosystem” does not exist
$ 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;
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
$ knex init
TO BE CONTINUED…