# Migrations
## What are database migrations used for?
The database migrations are used for migrating data from a source database to a target database, and migrating can also help when needing to upgrade to a later database version. Both the source and target database use the PostgreSQL database system, but it is possible to use a different system between the two databases.

## db-migrate node module
The db-migrate module is a migration framework for implementing database migrations. It offers commands and access to multiple APIs for achieving a full migration implementation.

npm i db-migrate

The PostgreSQL driver must be installed along with db-migrate in order to make the database connection.

npm i db-migrate-pg

## Commands

### up
Executes all the "up" migrations that are located in the migrations directory.

Example of an "up" migration:

exports.up = function(db) {
  return null;
};

### down
Executes the "down" migrations that are located in the migrations directory. By default it only executes the last executed migration. To execute a specific "down" migration the "down" command followed by the migration name can be used.

Example of a "down" migration:

exports.down = function(db) {
  return null;
};

### reset
Executes *all* the "down" migrations inside the first scope.

### create
The create command creates a migration template where a correctly formatted migration file and some boilerplate code are provided.

### db:create
The db:create command creates a database inside PostgreSQL or whichever database system is being used.

### db:drop
The db:drop command drops a database inside PostgreSQL or whichever database system is being used.

## Migration API for SQL

### db.createTable
Creates a new database table with the specified columns.

### db.dropTable
Drops a database table.

## Migration Files

### add-settings.js
**up:** Executes the db.createTable to create a "settings" table.

Below is a list of settings attributes:
* key
* value

**down:** Executes the db.dropTable to drop the "settings" table.

### add-connections.js
**up:** Executes the db.createTable to create a "connections" table.

Below is a list of connections attributes:
* connection_id
* state
* my_did
* alias
* request_id
* invitation_key
* invitation_mode
* invitation_url
* invitation
* accept
* initiator
* their_role
* their_did
* their_label
* routing_state
* inbound_connection_id
* error_msg
* created_at
* updated_at

**down:** Executes the db.dropTable to drop the "connections" table.

### add-contacts.js
**up:** Executes the db.createTable to create a "contacts" table.

Below is a list of contacts attributes:
* contact_id
* label
* created_at
* updated_at
* meta_data

**down:** Executes the db.dropTable to drop the "contacts" table.

### add-connections-to-contacts.js
**up:** Executes the db.createTable to create a "connections_to_contacts" table.

Below is a list of connections_to_contacts attributes.
* connection_id
* contact_id

**down:** Executes the db.dropTable to drop the "connections_to_contacts" table.

### add-schemas.js
**up:** Executes the db.creatTable to create a "schemas" table.

Below is a list of schemas attributes:
* schema_id
* name
* version
* protocol
* sequence_number
* origination
* attribute_names

**down:** Executes the db.dropTable to drop the "schemas" table.

### add-credential-definitions.js
**up:** Executes the db.createTable to create a "credential_definitions" table.

Below is a list of credential_definitions attributes:
* credential_definition_id
* tag
* schema_id
* type
* protocol
* value

**down:** Executes the db.dropTable to drop the "credential_definitions" table.

### add-held-credentials.js
**up:** Executes the db.createTable to create a "held_credentials" table.

Below is a list of held_credentials attributes:
- credential_id
- schema_id
- credential_definition_id
- req_req
- revoc_reg_id
- witness
- signature
- signature_correctness_proof
- values

**down:** Executes the db.dropTable to drop the "held_credentials" table.

### add-issue-credentials.js
**up:** Executes the db.createTable to create a "issue_credentials" table.

Below is a list of issue_credentials attributes:
* credential_exchange_id
* credential_id
* credential
* raw_credential
* revocation_id
* connection_id
* state
* role
* initiator
* thread_id
* parent_thread_id
* schema_id
* credential_definition_id
* revoc_reg_id
* credential_proposal_dict
* credential_offer
* credential_offer_dict
* credential_request
* credential_request_metadata
* auto_issue
* auto_offer
* auto_remove
* error_msg
* trace
* created_at
* update_at

**down:** Executes the db.dropTable to drop the "issue_credentials" table.

### add-demographic-data.js

**up:** Executes the db.createTable to create a "demographic_data" table.

Below is a list of demographic_data attributes:
* contact_id
* email
* phone
* address
* created_at
* update_at

**down:** Executes the db.dropTable to drop the "demographic_data" table.

### add-users.js

**up:** Executes the db.createTable to create a "users" table.

Below is a list of users attributes:
* user_id
* username
* email
* password
* token
* created_at
* update_at

**down:** Executes the db.dropTable to drop the "users" table.

### add-roles.js

**up:** Executes the db.createTable to create a "roles" table.

Below is a list of roles attributes:
* role_id
* role_name

**down:** Executes the db.dropTable to drop the "roles" table.

### add-roles-to-users.js

**up:** Executes the db.createTable to create a "roles_to_users" table.

Below is a list of roles_to_users attributes:
* role_id
* user_id

**down:** Executes the db.dropTable to drop the "roles_to_users" table.

### roles-dafault-set.js

**up:** Executes three database commands to interact with the "roles" table. The first command executes the db.runSql for using a SQL query to delete from the table. The next two database commands execute db.insert for inserting values into the table columns.

Below is a list of values and their corresponding columns:
* First Database Insert
* Value "admin" to column "role_name"
* Second Database Insert
* Value "technician" to column "role_name"

**down:** Executes the db.runSql for using a SQL query to delete from the "roles" table.

### add-passports.js
**up:** Executes the db.createTable to create a "passports" table.

Below is a list of passports attributes:
* contact_id
* passport_number
* surname
* given_names
* sex
* date_of_birth
* place_of_birth
* nationality
* date_of_issue
* date_of_expiration
* type
* code
* authority
* photo
* created_at
* update_at

**down:**
Executes the db.dropTable to drop the "passports" table.

### users-default-set.js

**up:**
Executes two database commands to interact with the "users" table. The first command executes db.runSql for using a SQL query to delete from the table. The second command executes db.insert to insert values into the table columns.

Below is a list of values and their corresponding columns:
* Value "admin" to column "username"
* Value "[e-mail]" to column "email"
* Value `hashedPassword` to column "password"
* Value `castedTimeStamp` to column "created_at"
* Value `castedTimeStamp` to column "update_at"

**down:** Executes db.runSql for using a SQL query to delete from the "users" table.

### roles-to-users-set.js
**up:** Executes two database commands to interact with the "roles_to_users" table. The first command executes db.runSql for using a SQL query to delete from the table. The second command executes db.insert to insert values into the table columns.

Below is a list of values and their corresponding columns:
* Value "1" to column "role_id"
* Value "1" to column "user_id"

**down:** Executes db.runSql for using a SQL query to delete from the "roles_to_users" table.

### add-images.js
**up:** Executes db.createTable to create the "images" table.

Below is a list of images attributes:
* image_id
* name
* type
* image

**down:** Executes db.dropTable to drop the "images" table.

### settings-default-logo.js
**up:** Executes two database commands to interact with the "images" table. The first command executes db.runSql for using a SQL query to delete from the table. The second command executes db.insert to insert values into the table columns.

Below is a list of values and their corresponding columns:
* Value 'logo.png' to column "name"
* Value 'logo' to column "type"
* Value `base64` to column "image"

**down:** Executes db.runSql for using a SQL query to delete from the "images" table.

### settings-default-set.js
**up:** Executes four database commands to interact with the "settings" table. The first command executes db.runSql for using a SQL query to delete from the table. The next three database commands execute db.insert.

Below is a list of values and their corresponding columns:
* First Database Insert
* Value "theme" to column "key"
* Value `"{css properties}"` to column "value"
* Second Database Insert
* Value "smtp" to column "key"
* Value `"{smtp object}"` to column "value"
* Third Database Insert
* Value "organization" to column "key"
* Value `"{companyName}"` to column "value"