Skip to content

Latest commit

 

History

History
130 lines (86 loc) · 3.8 KB

database.md

File metadata and controls

130 lines (86 loc) · 3.8 KB

Spark database

The Spark project uses

Using MySql

By default we use MySql. Other database are currently not supported.

MySql installation

Windows

Download MySQL Community Server from MyQSL website. On the installation wizard, it is recommended to select custom install and include MySQL Server only. Choosing a full install will install tons of development tools that are not mandatory.

Mac

On Mac after installing, please add the following lines to your ~/.bash_profile

alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin
Overcoming root password problems

Also, On first install, you might get mysql password expired or other root password related issues.

To change mysql default password - run the following commands

mysql -u root -p

Enter the default root password you got during mysql setup Then run the following to set your password:

SET PASSWORD = PASSWORD('xxxxxxxx');

Database configuration in Spark project

Knex uses the database configuration from a .env file that is stored in the root folder.

By default (local dev machine) it is configured to use MySQL. This default configuration should get you started without additional configurations.

If you are not using a simple local server configuration, edit your .env file and set the relevant settings to match your database (If you don't have an .env file, duplicate .env-example).

.env file:

SPARK_DB_CLIENT=mysql
SPARK_DB_HOSTNAME=localhost
SPARK_DB_DBNAME=spark
SPARK_DB_USER=spark
SPARK_DB_PASSWORD=spark
SPARK_DB_DEBUG=false

Creating the database

To create the Spark database and the user, run:

npm run-script createdb

or if you need to use a different configuration or credentials, use a command similar to:

sudo mysql -u root < migrations/create_db.sql

Running migrations

Make sure you have Knex installed:

npm install -g knex

Migrate forward to latest migration

knex migrate:latest

See Knex.js documentation for more options and details

Creating a new migration

If you need to make changes to the DB schema, you should create a migrations file and commit it with the rest of your code.

Run migrate:make with a relevant migration name:

knex migrate:make <migration_name>

Edit the created file and write your migration. You can use the exising migration files as examples or refer to the Knex.js documentation

Populating the database

It is usually helpful for developers to work with some data in the database. Use knex seed mechanism to populate the database for the first time after migration.

IMPORTANT: Seeding will drop your current data.

npm run seed

You can then login into the system with the user [email protected] and password admin.

Running a local DB from production/staging DB data

This procedure requires you to have permissions to midbarrn project on google cloud. You will also need to install the Google Cloud SDK

Delete the existing environment and start a new fresh DB

docker-compose down -v; docker-compose up -d db

Get a url to existing DB backup, the URL should look like this -

gs://midburn-k8s-backups/sparkdb-staging-dump-2018-03-02-00-00.sql

Import the URL directly to the DB

! gsutil cat gs://midburn-k8s-backups/sparkdb-staging-dump-2018-03-02-00-00.sql | \
    mysql --host=localhost --port=3306 --protocol=tcp --user=root --password=123456 \
    && echo failed to import data