Skip to content

Database guide for development

Jump to bottom
Laila Melkas edited this page Dec 13, 2018 · 6 revisions

Setting up database

  1. Install postgresql MacOS: https://postgresapp.com/ Ubuntu: https://www.postgresql.org/download/linux/ubuntu/

  2. If port isn’t 5432 by default, set that as the port

Linux

  1. After downloading postgres create an user with sudo -u postgres createuser rekrysofta
  2. You need to set password for created user by logging into psql using sudo -u postgres psql
  3. Then set the password for rekrysofta to rekrysofta by using \password rekrysofta
  4. Create the database by using sudo -u postgres createdb ohturekry
  5. Log back into postgres and connect to the db using \connect ohturekry
  6. In the server AND the emailer folders create .env file and set these:
DB_USERNAME=rekrysofta
DB_PASSWORD=rekrysofta
DB_NAME=ohturekry
DB_HOST=localhost
DB_PORT=5432
  1. In the root folder (same as where Dockerfile is) create .env file and set these
DB_USERNAME=rekrysofta
DB_PASSWORD=rekrysofta
DB_NAME=ohturekry
DB_HOST=database
DB_PORT=5432

Mac

  1. After installing the Postgres app just simply create database called ohturekry and double-check that the port it is using is 5432, go to the server folder and create .env file and set these. Also create the .env file with same values in the emailer folder
DB_USERNAME= <name-of-your-computer>
DB_PASSWORD= 
DB_NAME=ohturekry
DB_HOST=localhost
DB_PORT=5432

4.Go to the project’s root folder and create .env and set these

DB_USERNAME=rekrysofta
DB_PASSWORD=rekrysofta
DB_NAME=ohturekry
DB_HOST=database
DB_PORT=5432

Inspect database locally

To see what's in the database in your local environment, you need to start an interactive session in the postgres:10 container. See here for instructions how to do that.

Then type in

psql -U rekrysofta -d ohturekry

to connect to the database

Remember that to execute postgreSQL statements, all table names must be surrounded by double quotes:

SELECT * FROM "JobApplications";

Running migrations

As we are using Sequelize for database management, all changes to the schema should be done using Sequelize’s migrations. Essentially each change becomes its own migration file which can be run and undone. The migration files themselves should never be modified to introduce DB changes.

Running the migrations is done automatically in our project, so if you start the project using the script in {PROJECT_ROOT}/scripts/init.sh, the migrations are run as well as test data created. If you, however, want to run the migrations manually, the command is node_modules/.bin/sequelize db:migrate. All sequelize commands need to be run in the server root,{PROJECT_ROOT}/server

Undoing the last migration is done by running node_modules/.bin/sequelize db:migrate:undo, and undoing all migrations node_modules/.bin/sequelize db:migrate:undo:all.

Making changes to existing models

Each new change to the DB needs to be defined in its own migration file. A new file can be added using the command node_modules/.bin/sequelize migration:generate. This creates a skeleton migration file with up and down methods defined.

In the up method using queryInterface the changes are introduced. Changes may include adding a new column etc. If wanting to create a new table, see Introducing a new model. Correspondingly, the introduced changes must be reverted in the down method, which is run when undoing migrations.

If you want to, say, add a column ‘age’ to table ‘House’, you should first navigate to server root in the console and then run node_modules/.bin/sequelize migration:generate --name {name_of_your_migration_file}, for instance node_modules/.bin/sequelize migration:generate --name ‘add_age_to_house’.

These migration files are always prefixed automatically with a timestamp. This is important, because the timestamps define the order in which the migrations are run, oldest first. Also undo works similarly, just starting from the newest migration. If the order is not correct, the associations can get tricky, as undoing migrations might try to remove a table or a column that is referenced from another model.

After the changes are made in the migration file, corresponding changes must be made in the model, which can be found under {PROJECT_ROOT}/server/db/models

Introducing a new model

To introduce a new model, run command node_modules/.bin/sequelize model:generate --name {MODEL_NAME} --attributes {ATTRIBUTE1}:{DATATYPE1},{ATTRBUTE2}:{DATATYPE2}. For instance, to create a model ‘House’ with attributes ‘age:number’ and ‘address:string’, the command would be node_modules/.bin/sequelize model:generate --name House --attributes age:number,address:string. This command will generate both the model file as well as the appropriate migration file.

Clone this wiki locally