nvoitovych/cityview_api

###Getting started #####Database (MySQL)

1. Enter to mysql
   mysql -u root -p
2. CREATE DATABASE db_name;
3. Create seperate users to make migrations and data manipulation
   CREATE USER 'db_migrations_user'@'localhost' IDENTIFIED BY 'pass';
CREATE USER 'db_crud_user'@'localhost' IDENTIFIED BY 'pass2';
4. Grant privileges for migrations user to only create, alter, drop tables, NOT db.
   Absolute minimum of permissions for migration is:
   Alter table permission is for adding CONSTRAINTS.
   GRANT DROP, CREATE, ALTER ON TABLE db_name.* TO 'db_migrations_user'@'localhost';
GRANT INDEX ON TABLE cityview_db.* TO 'cityview_dbstruct_user'@'localhost';
   References permission is for adding FK.
   GRANT REFERENCES ON cityview_db.* TO 'cityview_dbstruct_user'@'localhost';
5. GRANT DELETE, SELECT, UPDATE, INSERT ON db_name.* TO 'db_migrations_user'@'localhost';
6. GRANT DELETE, SELECT, UPDATE, INSERT ON db_name.* TO 'db_crud_user'@'localhost';

DB settings
Set
DB_HOST, DB_USER, DB_PASS, DB_NAME
in .env.* files(Reference: dotenv-flow)

Migrations
Take a look on package.json script section:
"migrate:dev": "NODE_ENV=migrationsDev knex migrate:latest",
"migrate:prod": "NODE_ENV=migrationsProd knex migrate:latest",
"rollback:dev": "NODE_ENV=migrationsDev knex migrate:rollback"
Note:
Table with FK have to be created before related table.
Running any knex migrate command, it will use NODE_ENV implicitly.(Reference: knex migrate)

Step-by-Step

1. Make previous actions(Database, DB settings)
2. It'll create knex system tables(knex_migrations_lock and knex_migrations)
   npm run migrate:dev
3. knex_migrations_lock and knex_migrations tables will be created, and fail with error like this:
   Error: ER_TABLEACCESS_DENIED_ERROR: SELECT command denied to user 'db_migrations_user'@'localhost' for table 'knex_migrations_lock'
4. Grant CRUD privileges to migrations user on db_name.knex_migrations_lock and db_name.knex_migrations.
   Migrations user CRUD data about migrations(created tables, to rollback, etc) in that tables.
   We cannot grant privileges on non existing table, that's why we have to create that tables before.
   GRANT SELECT, UPDATE, DELETE, INSERT ON db_name.knex_migrations TO 'db_migrations_user'@'localhost';
GRANT SELECT, UPDATE, DELETE, INSERT ON db_name.knex_migrations_lock TO 'db_migrations_user'@'localhost';

Seeds
Аfter the tables are created fill the tables with data. Use only for development and testing
Run script(package.json scripts):
"seeds:dev": "NODE_ENV=development knex seed:run"