Tag Archives: database

Creating database migrations and seeds in Node.js

This can be all done with knex library, a SQL query builder for JavaScript. In this post I’ll use MySQL (MariaDB) database, but knex also supports Postgres, MSSQL, SQLite3, and Oracle. I will also use faker for generating fake data for seeding the database.

Install knex, faker and mysql (or any other driver for database which knex supports).

npm install knex faker mysql

Create a new database in your database client (I use adminer for viewing and managing database content).

Initialize knex (which creates configuration file) and update development object accordingly to database driver you use.

$ node_modules/.bin/knex init
Created ./knexfile.js
// Update with your config settings.

module.exports = {
  development: {
    client: 'mysql',
    connection: {
      database: 'knexseed',
      user:     'root',
      password: ''
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },
...
}

Create a new migration, open newly created file and replace its content with the following (I create table called users with id (with auto increment property), username, email, created_at, updated_at columns – last two are created with timestamps method).

$ node_modules/.bin/knex migrate:make users
Using environment: development
Created Migration: /home/jernej/Code/knexseed/migrations/20160904154141_users.js
exports.up = function(knex, Promise) {
    return knex.schema.createTable('users', table => {
        table.increments('id')
        table.string('username')
        table.string('email')
        table.timestamps()
    });
};

exports.down = function(knex, Promise) {
    return knex.schema.dropTable('users')
};

Run migrations.

$ node_modules/.bin/knex migrate:latest
Using environment: development
Batch 1 run: 1 migrations 
/home/jernej/Code/knexseed/migrations/20160904154141_users.js

Create new seed file and replace its content with the following (here I create a loop which generates record with random data for users table)

$ node_modules/.bin/knex seed:make users
Using environment: development
Created seed file: /home/jernej/Code/knexseed/seeds/users.js
var faker = require('faker');

let createRecord = (knex, id) => {
  return knex('users').insert({
    id,
    username: faker.internet.userName(),
    email: faker.internet.exampleEmail(),
    created_at: new Date(),
    updated_at: new Date()
  })
}

exports.seed = (knex, Promise) => {
  return knex('users').del()
    .then(() => {
      let records = [];

      for (let i = 1; i < 10; i++) {
        records.push(createRecord(knex, i))
      }

      return Promise.all(records);
    });
};

Run seeds.

$ node_modules/.bin/knex seed:run
Using environment: development
Ran 1 seed files 
/home/jernej/Code/knexseed/seeds/users.js

Pro tip: If you prefer using ORM for quering the database, use Bookshelf.js.

Bookshelf is a JavaScript ORM for Node.js, built on the Knex SQL query builder. Featuring both promise based and traditional callback interfaces, providing transaction support, eager/nested-eager relation loading, polymorphic associations, and support for one-to-one, one-to-many, and many-to-many relations.

InfluxDB: a time-series database to go

InfluxDB is exciting new technology I’m learning at the moment. It’s a specialized database for storing time series. In short time-series is defined by its name and metric name(s). It consists of sequence of {timestamp, metric values} ((Baron Schwartz’s Blog: Time-Series Requirements)) where each record is uniquely defined by timestamp. The most obvious use case right now I see in using them is in custom system monitoring and Internet of Things – storing measurements for future analysis ((List of use cases for it can also be found in InfluxDB documentation here.)). It’s open-sourced and written in Go, my language of choice right now.

Because this is my first encounter with such databases I was eager to learn some background of them first before starting coding my own demo application with InfluxDB. I quickly stumbled upon blog posts from Jim Moiron and Baron Schwartz. Particular from the latter were instantly interesting to me since I’ve already read High Performance MySQL where he’s listed as co-author. ((I’ve used the book while writing my thesis about NewSQL databases.))

I highly recommend reading those blog posts. Reading them helped me understand general concepts I still didn’t master after reading some documentation pages on InfluxDB website.

InfluxDB

Blog posts above convinced me that InfluxDB is a TSDB to go ((I know you should pick the best tool for job but everyone has thing called “favorite thing of choice”)). I’ve installed it with official Docker image. I then followed Getting started from its documentation and was impressed with its query language which feels like SQL. Database can be accessed via CLI or HTTP API.

Key concept from InfluxDB:

  • Measurement: conceptually like table in RDBMS.
  • Fields: Columns in measurement. It’s values are not limited to just numbers.
  • Tags: Columns in measurement which compared to fields are indexed. Querying by them is faster.
  • Line protocol: format which represent a data point and consists of measurement name, optional tags, at least one field and optional timestamp (if no timestamp is present, current is applied)
  • Field/tag set: combination of all fields/tags values.
  • Time-series: consists of measurement and field set.

Difference between tags amd fields: As already mentioned, tags are indexed. When querying by them not all measurement rows are read so queries are more performant. Tags are usually data known before measurement is taken (server name, animal species for which measurement is taken,…), fields are measurement values taken at given time.