DEV Community: James Ingold

Restoring MySQL Database from .frm and .ibd Files on MacOS

James Ingold — Wed, 23 Feb 2022 16:37:39 +0000

Ending 2020 by losing all your MySQL data seems about right doesn't it? I spent a full day recovering my data after updating to the latest Docker version on a NAS. It nuked my MySQL container in the process. Luckily, I had some database files but they were in .frm and .ibd format which are not easy to import, especially once the server is gone. In this article I'll show you how to restore your MySQL data on MacOS if you only have .frm and .ibd files available. Hopefully, this will save you some time if you are in the same position I found myself in.

A brief overview of the files we have and what we'll be doing to salvage the data:

.frm: Contains a single table definition, essentially create table statements
.ibd: This file holds the data and indexes for a single table on the file system.

Restore Process High-Level:

Generate create statements from .frm files to get our table structures back
Reset the tablespace: when an InnoDB table is truncated, or dropped and recreated, it gets a new table ID. Any ID mismatch between the table in the database and the backed-up table can prevent it from being restored. Since we're restoring this on a different MySQL Server instance, we need to drop the tables ids.
Put our .ibd files into the data directory
Sync the tablespace: Import new tables ids
Backup our data so we have full backup which can easily be restored

Let's Get Started

Install MySQL shell client:

brew install mysql-client

Add it to your path:

export PATH="/usr/local/opt/mysql-client/bin:$PATH"

Reload your shell

source ~/.zshrc

Let's begin by starting up a fresh docker instance that we'll use to restore our database.

Create config and data volumes. The config volume will hold additional MySQL configuration and the data volume will be where our data goes on disk.

mkdir ~/docker/mysql/conf.d
mkdir ~/docker/mysql/data

Add the following to a ~/docker/mysql/conf.d/config.cnf

[mysqld]
innodb_force_recovery = 0

Run a MySQL docker instance

docker run \
--detach \
--name=mysql2 \
--env="MYSQL_ROOT_PASSWORD=mypassword" \
--publish 32780:3306 \
--volume=/pathToMysqlConfig/conf.d:/etc/mysql/conf.d \
--volume=/pathToDataDirectory/mysql2:/var/lib/mysql:rw mysql:5.7

Step 1: Create Files to Reconstruct Table Structure

There are two ways to reconstruct the tables. The first option is to use MySQL command line utilities and the mysqlfrm tool. The other option is to create our tables with a dummy structure using some command line magic and then replace the dummy .frm's in our data directory with the actual .frms. I've used both but prefer the former, I'll show you how to do both.

Step 1.1: Use MySQL's command line utilities

The Python MySQL connector is required. Download it from here.
Download MySQL Utilities source code by selecting "Source Code" from "Select Platform".
I downloaded the Windows distribution and unzipped it (running on a mac)
From a terminal:

$ cd path/to/unzipped/mysql-utilities-1.5.6
$ python ./setup.py build
$ sudo python ./setup.py install

Use mysqlfrm to generate table creates

mysqlfrm  --diagnostic ~/folder/*.frm --quiet > ~/folder/recoverme-table-structures.sql

Set the ROW_FORMAT to compact

sed -i '' -e 's/ENGINE=InnoDB/ ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci/' recoverme-table-structures.sql

Step 2 - Generate dummy tables creates using .frm files

Even though we're going to use the output from Step 1, we will use the files generated in this step to reset the tablespace and import so go ahead and follow along. This is useful when you have many tables to restore.

First we'll get the table names from our .frm files

ls -1 *.frm > tables.sql

Prepend create table in front of each table name

awk '{print "create table " $0}' tables.sql > rtables.sql

Replace .frm and add a dummy int column with engine innodb

sed -i '' -e 's/.frm/ (id int) engine=innodb;/' rtables.sql

At this point, you should have three files: recoverme-table-structures.sql, tables.sql, rtables.sql.

Step 3 - Create Our Tables

First let's create our database

mysql -uroot -p --host=127.0.0.1 -P32780 -e "create database dbName"

Next, run sql

mysql -uroot -p --host=127.0.0.1 -P32780 dbName < recoverme-table-structures.sql

If you were to run rtables.sql instead of recoverme-table-structures.sql, you would have your tables added with a dummy id column. At that point, you would stop mysql and then copy over your .frm files into the mysql database directory, replacing the existing .frm files. After that you would run flush tables.

Step 4 - Replace Data

Now the spot we're in is that our internal id of our tables is different than the one in our .ibd files. We'll manually sync our tablespaces.

Create remove-tablespace.sql to remove the existing internal ids.

cp rtables.sql remove_tablespace.sql
sed -i '' -e 's/create/alter/' remove_tablespace.sql
sed -i '' -e 's/ (id int) engine=innodb;/ discard tablespace;/' remove-tablespace.sql

Create sync-tablespace.sql to import tablespace from existing data.

cp rtables.sql sync_tablespace.sql
sed -i '' -e 's/create/alter/' sync_tablespace.sql
sed -i '' -e 's/ (id int) engine=innodb;/ import tablespace;/' sync-tablespace.sql

Run remove-tablespace.sql

mysql -uroot -p --host=127.0.0.1 -P32780 dbName < remove-tablespace.sql

Remove any existing .ibd files in our MySQL directory

cd ~/MySQL/recoverme
rm -f *.ibd

Copy .ibd files over to MySQL directory

cp ~/directoryMyRestoreFilesAre/*.ibd .

Sync tablespace

mysql -uroot -p --host=127.0.0.1 -P32780 dbName < sync-tablespace.sql

If you get error - Schema mismatch (Table has ROW_TYPE_COMPACT row format, .ibd file has ROW_TYPE_DYNAMIC row format.). Restart the process and sed ROW_FORMAT=DYNAMIC instead of ROW_FORMAT=COMPACT

Dump our database to create a backup

mysqldump recover > recoverme-structure-data.sql

Now we have a legitimate MySQL dump of our data. We can import that in with the correct database name

mysql -e "create database actualdatabase"
mysql actualdatabase < recoverme-structure-data.sql

That's it! There are certainly more issues that can come up if the ibd files are corrupted. I encourage you to check out the Twindb github for some excellent tools on repairing ibd files and general data recovery practices.

References

Dealing with corrupted InnoDB data

Recover Orphan InnoDB Database from .ibd file

SO Recover Table Structure from ibd files

SO Repair/Recover data from MySQL database

Undrop for InnoDB Twindb CLI Tool

Using Undrop

Twindb - Recovering Corrupt MySQL database

Deconstructing an Object Relationship Mapper (ORM) in Typescript

James Ingold — Sat, 05 Feb 2022 02:51:26 +0000

Have you ever wondered how an ORM works? After working through rolling my own lightweight Typescript ORM, I have some answers. We're not going to talk through building a full ORM in this article but we will set up a basic object mapper which can be later extended to generate SQL and perform queries. Let's dive into it!

What is an ORM?

An ORM stands for Object Relational Mapping and these tools map programming languages to databases. ORMs allow you to query and manipulate data from a database generally in an object-oriented paradigm. They bridge your objects in code to your database tables.

Pros

ORMs are inherently DRY making it easier to re-use code.
They take care of some things automatically such as sanitization and transactions.
Relationships are handled elegantly which can be a pain to deal with manually.
Can use your programming language of choice instead of SQL.

Cons

The big issue comes down to performance on ORMs, they generate SQL which can be less optimal than handcrafting your own statements.
Learning curve as each ORM has a different implementation.

The Situation

The main pain point I was trying to solve was connecting Typescript classes to a database. In the codebase I was working in, the following pattern existed: there was a domain model, a repo model (matched database tables), and a dto (data transfer object). The domain model and repo model were manually mapped back and forth, to and from the database. The dto was also manually mapped but I'm going to skip this model for now. This required a fair amount of code to be created whenever someone wanted to create a new model to work with. It also made handling relationships difficult. Parameterized constructors can also become a burden, especially early in a project where requirements are bound to change often. There was an established database access pattern - repository classes using a shared library. Since multiple different services were using the database access shared library, I decided to roll my own light weight object mapper to map objects to the database without using an existing fully fledged ORM library.

Psuedo example of the current code

export class RepoModel {
  static propertyValueMap: IPropertyValueMap<DomainModel> = {
   const mapType = (type: TypeEnum) => {
      return RepoModel.propertyValueMap?.type?.[type] ?? handleError();
    };
  }

  constructor (prop1, prop2, prop3, ...) {}

  toDomain() : DomainModel {
      const mapType = (type: CustomEnum) => {
      const map = Translator.invert(RepoModel.propertyValueMap?.type);
      return map?.[type] ?? handleError();
    };
    ...
    return new DomainModel(
      mapType(this.type_id) as TypeEnum,
      this.id,
      this.createdAt)
  }

  static fromDomain(domain: DomainModel) : RepoModel {
    // code that maps each enum
      const mapType = (type: TypeEnum) => {
      return RepoModel.propertyValueMap?.type?.[type] ?? handleError();
    };
    ...
    return new RepoModel(
      mapType(domain.type),
      domain.id,
      domain.createdAt)
  }
}

export class DomainModel {
  constructor(
    public type: TypeEnum,
    public id?: string,
    public createdAt?: Date
  ) {}
}

export class ModelRepo {
  public async get(id: string): Promise<DomainModel> {
    const result = await this.db.query(this.db.getSql('getById'), [id]);
    const resultObject = this.db.get(result);
    return new RepoModel(
       resultObject.type_id,
        resultObject.id,
        resultObject.created_at
    ).toDomain();
  }
}

The Vision

I wanted to refactor the domain model to handle the conversion to database tables without requiring manual mapping of a repo model. The repo model would be removed. The domain model would inherit a base model which would handle the ORM logic. Since there was an established database access pattern, I didn't go the full way to make queries through our makeshift ORM but I will point out the spots that can be extended to achieve this functionality. The goal is to simplify the creation of domain models, transformations to/from the database and reduce the amount of code/complexity to ship features.

General Principles - Connecting your Typescript Classes to the Database

Database columns will be mapped to domain object properties using decorators. This will include relationships and enum types. reflect-metadata stores metadata about the classes and properties. Most of the work is a simple map for each class, renaming db column properties to domain model properties and vice versa. Reflect.defineProperty holds a list of field metadata on the target class. This is where more database ORM logic could live in the future such as column type, length, etc. A base domain model entity will use this metadata to map the models appropriately.

Domain entities use Model, Column, Enum, and HasMany (relationships) decorators to map to the database. A Domain entity extends BaseDomainModel which has toRepo() and fromRepo() functions. These functions do the heavy lifting of using metadata to transform objects.

Here's what our end state will look like:

@Model("DomainModel")
export class DomainModel extends BaseDomainModel implements IDomainModel {
  @Column("id")
  id?: string;
  @Enum("type_id", () => TypeEnum)
  type: TypeEnum;
  @HasMany("UserDomainModels", "domain_model_id")
  users: UserDomainModel[];
  @Column("created_at")
  createdAt?: Date;
  constructor(obj?: IDomainModel) {
    super();
    Object.assign(this, obj);
  }
}

export interface IDomainModel {
  id?: string;
  type: TypeEnum;
  users: UserDomainModel[];
  createdAt?: Date;
}

export class ModelRepo {
  public async get(id: string): Promise<DomainModel> {
    const result = await this.db.query(this.db.getSql("getById"), [id]);
    return DomainModel.fromRepo(this.db.get(result));
  }
}

Decorators

Decorators provide a way to add both annotations and a meta-programming syntax for class declarations and members. Even though it's an experimental feature, decorators provide great functionality. We'll leverage decorators to handle our mapping metadata. We'll briefly walk through each decorator in our ORM.

Model(identifier: string, alias?: string)

Adds the model and identifier to a class map. An alias can be set to avoid name collision with joins in raw sql for example if alias = model then in sql, select model.id as model_id will allow model_id to be set on child models as id which would be overwritten without an alias id column in the join.

export const classMap = new Map();
export function Model(identifier?: string, alias?: string): ClassDecorator {
  return (target: any) => {
    identifier = identifier || target.name;
    if (!target.prototype.modelName) {
      Reflect.defineProperty(target.prototype, "modelName", {
        value: identifier,
        writable: true,
        configurable: true,
        enumerable: true,
      });
      Reflect.defineProperty(target.prototype, "alias", {
        value: alias || "",
        writable: true,
        configurable: true,
        enumerable: true,
      });
    }
    classMap.set(identifier, target);
  };
}

Column(name: string)

Adds the database column name to a map for the class to be used for transforming. This could be extended to support more options and database support such as column type, size, etc. This is also where further options would live as well such as making a field required.

import "reflect-metadata";
export const METADATA_KEY = "design:type"; // reflect-metadata Type information design type
export type relationType = "HASONE" | "HASMANY";

export function setTransform(
  object: object,
  propertyName: string | symbol,
  name: string | symbol
) {
  const metadataMap = getMetadata(PARAM_TYPE_KEY, object);
  metadataMap.set(propertyName, name); // would need to actually implement a map with db types
}

export function Column(name?: string): PropertyDecorator {
  return (target: any, propertyKey?: string | symbol) => {
    if (!target.fields) {
      Reflect.defineProperty(target, "fields", {
        value: {},
        writable: true,
        configurable: true,
        enumerable: true,
      });
    }
    const designType = Reflect.getMetadata(
      METADATA_KEY,
      target,
      propertyKey as string
    );
    const values: any = { type: designType.name, name }; // This is where we could do more DB ORM mapping if we wanted - column type, size, etc
    Reflect.defineProperty(target.fields, propertyKey as string, {
      value: values,
      writable: true,
      configurable: true,
      enumerable: true,
    });
    setTransform(target, propertyKey as string, name as string);
  };
}

Enum(name: string, () => Dictionary)

Supports mapping to and from an enum type. Parameters are the database column name and a function which points to the enum options to use

export function Enum(name: string, options: () => Dictionary) {
  return (target: any, propertyKey?: string | symbol) => {
    const opts = {
      value: { name: propertyKey as string, enum: true, options: options() },
      writable: true,
      configurable: true,
      enumerable: true,
    };
    Reflect.defineProperty(target.fields, propertyKey as string, opts);
    setTransform(target, propertyKey as string, name as string);
  };
}

export type Dictionary<T = any> = { [k: string]: T };

HasMany(modelName: string, relationKey?: string)

Adds a HasMany relationship to the object map supporting transformation when going fromRepo. relationKey is optional but could be used in the future for more database mapping.

export const PARAM_TYPE_KEY = "PARAM_TYPE_KEY";
import { getMetadata } from "./utils"; // wraps Reflect.getMetadata to return class or property info
export function HasMany(
  modelName: string,
  relationKey?: string
): PropertyDecorator {
  return (target: any, propertyKey?: string | symbol) => {
    if (!target.relationship) {
      Reflect.defineProperty(target, "relationship", {
        value: {},
        writable: true,
        configurable: true,
        enumerable: true,
      });
    }
    const values: any = {
      as: propertyKey as string,
      relationshipType: "HASMANY",
      from: modelName,
      on: { [propertyKey as string]: relationKey },
      type: "left", // could use this for joins in the future
    };
    if (!target.relationship.HASMANY) {
      Reflect.defineProperty(target.relationship, "HASMANY", {
        value: [values],
        writable: true,
        configurable: true,
        enumerable: true,
      });
    } else {
      target.relationship.HASMANY.push(values);
    }
    const originMap = getMetadata(PARAM_TYPE_KEY, target);
    originMap.set("relationship", target.relationship.HASMANY);
  };
}

BaseDomainModel

Each domain model which wants to support object mapping will need to extend BaseDomainModel.

Static functions:

fromRepo(obj): DomainModel
toRepo(): obj

import "reflect-metadata";
import { classMap, PARAM_TYPE_KEY, getMetadata } from "../../decorators/utils";

export class BaseDomainModel {
  static toRepo(data: any): any {
    const retVal = {};
    let cls: any;
    if (data instanceof this) {
      cls = data;
    } else {
      cls = Reflect.construct(this, []);
    }
    const originMap = getMetadata(PARAM_TYPE_KEY, this);
    originMap.forEach((value: string, key: string) => {
      if (cls.fields[key] && cls.fields[key].enum) {
        if (typeof data[key as string] === "number")
          retVal[value] = data[key as string];
        else {
          const options = Object.values(cls.fields[key].options);
          retVal[value] = options.findIndex(
            (x: any) => x === data[key as string]
          );
          if (retVal[value] < 0) retVal[value] = 0;
        }
      } else if (key && Object.prototype.hasOwnProperty.call(data, key)) {
        retVal[value] = data[key];
      }
    });
    return retVal;
  }

  static fromRepo(data: any) {
    const objData = Array.isArray(data) ? data[0] : data;
    let cls: any;
    if (data instanceof this) {
      cls = objData;
    } else {
      if (!isObject(objData)) {
        data = {};
      }
      cls = Reflect.construct(this, []);
    }

    const originMap = getMetadata(PARAM_TYPE_KEY, this);
    originMap.forEach((value: any, key: string) => {
      // set the values
      if (
        value &&
        Object.prototype.hasOwnProperty.call(objData, value as string)
      ) {
        if (cls.fields[key] && cls.fields[key].enum) {
          cls[key] = Object.values(cls.fields[key].options)[
            objData[value as string]
          ];
        } else {
          cls[key] = objData[value as string];
        }
      } else if (key === "relationship" && data.length >= 1) {
        // handle relationships mapping
        value.forEach((r: any) => {
          const model = classMap.get(r.from);
          const om = getMetadata(PARAM_TYPE_KEY, model);
          cls[r.as] = [];
          data.forEach((childData: any, index: number) => {
            cls[r.as].push(new model());
            om.forEach((value: string, key: string) => {
              // set value here
              cls[r.as][index][key] =
                childData[`${model.prototype.alias}_${value}`] ||
                childData[value];
            });
          });
        });
      }
    });
  }
}

Conclusion

That's it! We now have a basic ORM in place to handle mapping our objects back and forth between database and domain models. In the future we can extend our ORM to generate SQL and provide further database support. Happy Codings!

Let me know what you think at hey[@]jamesingold.com

References:

Reflect Metadata

Great Article on Decorators and Metadata

Sequelize Typescript Decorators

Amazon Cognito MFA with Email Using Lambda Triggers

James Ingold — Sun, 15 Aug 2021 01:35:11 +0000

Amazon Cognito is a great service for easily getting started with authentication. It also has multi-factor authentication (MFA) right out of the box using a cell phone for SMS or a TOTP (Time-based One Time Password) device such as Authy or Google Authenticator. The requirement that I had was to only use MFA via email. I would have preferred to use the built-in AWS Cognito MFA workflows but this was a hard requirement for this project. This was in the healthcare industry and their email was usually restricted to being on the hospital network. Therefore, I had to allow MFA by email only.

This post will describe how to implement MFA with email on Amazon Cognito by using a Custom Authentication Flow and Amazon SES (Simple Email Service).

First, go to the AWS console and set up SES. Make sure that you have an email verified in Amazon SES. If your account is in Sandbox mode for Amazon SES, you will want to make sure to verify both the sender and the receiver email address. When the account is in production mode, you will not need to verify email addresses that you are sending to. I point this out because I spent 2 hours trying to figure out why sending an email was failing due to this issue.

To get started, we have to use a Custom Authentication Flow via Lambdas to handle this deviation from the built-in AWS Cognito MFA workflows. Amazon Cognito works via Lambda Functions and they allow different hooks to customize the authentication flow:

Amazon Cognito Lifecyle Triggers:

Define Auth Challenge: Determines the next challenge in a custom authentication flow (tell Cognito about our custom challenge)
Create Auth Challenge: Creates a challenge in a custom authentication flow (send the MFA code)
Verify Auth Challenge: Determines if a response is correct in a custom authentication flow (verify that the code entered matches the code we created)

There are a couple built in challenges that we can utilize, including FORCE_NEW_PASSWORD and PASSWORD_VERIFIER.

In order to create a custom auth flow that allows us to use MFA code via email we're going to implement it using these Lambda lifecycle hooks. The authentication flow will be as follows:

The user enters their email and password on the app.
On our secure server side (NodeJS/Express), we'll use the UpdateUserAttributes Cognito method to generate a new MFA code for the user and save it into their attributes as a custom property.
Our define auth challenge lambda function will be hit. If the user has a temporary password, we'll return the FORCE_NEW_PASSWORD challenge to the client.
Due to a bug in the custom authentication workflow, after the user changes their password, we'll force them to log in again. The bug makes it so we won't get to our custom challenges if the user is changing their password
Alright, now they're back to the login screen where they'll enter their email and new password
The PASSWORD_VERIFIER challenge will run verifying the users password. After that, we'll return a CUSTOM_CHALLENGE - our MFA by email challenge!
Our create auth challenge lambda function will run. In this function, we will send the MFA code. We will have the users attributes and we'll use SES to email them the code generated on the secure server side.
Once the challenge is created and the email sent, we'll return the current authentication flow state (CUSTOM_CHALLENGE) which should update the UI to ask for the MFA code.
The user inputs the MFA code that they received from their email. Now we send that answer to the verify auth challenge. This will verify the answer to what we have in the user's custom attributes for their current MFA code. We'll also make sure that the time frame for a valid code has not passed.

Part 1: Create Serverless Project for Cognito Lifecycle Hooks

I'm going to use the Serverless framework because it's easy to manage and I know it well. However, you could just use Lambda functions if you wanted to keep it simple.

First, create a new pool through Amazon Cognito console. You can also use a cloud formation template and I've provided an example here.

Next, add a custom attribute called "authChallenge". This will hold our MFA code and a timestamp to make sure it's not expired.

Keep Multi-Factor Authentication (MFA) set to "OFF" since we're going to be implementing this ourselves via a Custom Auth Flow and the Cognito Lifecycle Triggers.

Once the pool is created, create a new serverless project

npm install -g serverless     
serverless create -n cognito-mfa-email-example

Define Lifecycle Function

The Define Cognito Lifecyle Event will determine the next challenge in a custom auth flow. This is a flow, we're going from one event to another starting with the PASSWORD_VERIFIER. There are a few important properties to return: issueTokens, failAuthentication, and the next challengeName to run. Once the password is confirmed (by Cognito), then we run our custom challenge. The PASSWORD_VERIFIER is a built in Cognito challenge which checks the user's password.

module.exports.handler = async event => {
  // Kicks off with Secure Remote Password
  if (
    event.request.session.length === 1 &&
    event.request.session[0].challengeName === 'SRP_A' &&
    event.request.session[0].challengeResult === true
  ) {
    event.response.issueTokens = false
    event.response.failAuthentication = false
    event.response.challengeName = 'PASSWORD_VERIFIER'
    return event
  }
  if (event.request.userNotFound) {
    event.response.failAuthentication = true
    event.response.issueTokens = false
    return event
  }

  // Check result of last challenge
  if (
    event.request.session &&
    event.request.session.length > 2 &&
    event.request.session.slice(-1)[0].challengeResult === true
  ) {
    // The user provided the right answer - issue their tokens
    event.response.failAuthentication = false
    event.response.issueTokens = true
    return event
  }

  event.response.issueTokens = false
  event.response.failAuthentication = false
  event.response.challengeName = 'CUSTOM_CHALLENGE'
  return event
}

Create Lifecycle Function

The Create function will run, creating our CUSTOM_CHALLENGE. We're going to use AWS SES to send an email with the MFA code in this function. The request will contain the user attributes from Cognito, we'll use the authChallenge custom attribute to get the code created on our server side and email that code to the user which they will use to complete MFA. We also set the publicChallengeParameters and privateChallengeParameters which we'll pass through the flow.

const mailer = require('./mailer')
module.exports.handler = async event => {
  const challenge = event.request.userAttributes['custom:authChallenge']
  const [authChallenge, timestamp] = (event.request.userAttributes['custom:authChallenge'] || '').split(',')
  // This is sent back to the client app
  event.response.publicChallengeParameters = {
    email: event.request.userAttributes.email
  }

  // add acceptable answer
  // so it can be verified by the "Verify Auth Challenge Response" trigger
  event.response.privateChallengeParameters = {
    challenge: challenge
  }

  // we want to check and make sure we haven't sent the code before in this login session before sending the code
  if (event.request.session.length < 3 && !event.request.session.find(s => s.challengeName === 'CUSTOM_CHALLENGE'))
    await mailer.send(
      'Your Access Code',
      event.request.userAttributes.email,
      'Please use the code below to login: <br /><br /> <b>' + authChallenge + '</b>'
    )

  return event
}

Verify Lifecycle Function

In the verify lifecycle function, we take the code that the user inputted and the one we have in privateChallengeParameters to compare if they have the right code. Amazon Cognito invokes this trigger to verify if the response from the end user for a custom Auth Challenge is valid or not. We also check the timestamp to make sure it's not expired. We set the challenge response property of answerCorrect based on if they answered correctly. You could have even more challenges and the custom challenge flow loop would repeat until all challenges are answered.

const LINK_TIMEOUT = 30 * 60

module.exports.handler = async event => {
  // Get challenge and timestamp
  const [authChallenge, timestamp] = (event.request.privateChallengeParameters.challenge || '').split(',')

  // Check if code is equal to what we expect...
  if (event.request.challengeAnswer === authChallenge) {
    // Check if the link hasn't timed out
    if (Number(timestamp) > new Date().valueOf() / 1000 - LINK_TIMEOUT) {
      event.response.answerCorrect = true
      return event
    }
  }

  event.response.answerCorrect = false
  return event
}

Deploy the Project

I've included a full sample serverless.yaml here. You'll want to the following IAM role capabilities and the following function declarations. I'm using the Amazon Systems Parameter Store for setting the EMAIL_ADDRESS variable which was used in the Create lifecycle function.

serverless.yaml example

iamRoleStatements:
    - Effect: "Allow"
      Action:
        - cognito-idp:AdminGetUser
        - cognito-idp:AdminUpdateUserAttributes
      Resource:
        - {"Fn::GetAtt": [UserPool, Arn]}
    - Effect: "Allow"
      Action:
        - ses:SendEmail
      Resource:
        - "*"

functions:
  define:
    handler: defineAuth.handler
    events:
      - cognitoUserPool:
          pool: ${self:custom.userPoolName}
          trigger: DefineAuthChallenge
          existing: true
  create:
    handler: createAuth.handler
    environment:
      EMAIL_ADDRESS: ${ssm:/${self:provider.stage}_EMAIL_ADDRESS}
    events:
      - cognitoUserPool:
          pool: ${self:custom.userPoolName}
          trigger: CreateAuthChallenge
          existing: true
  verify:
    handler: verify.handler
    events:
      - cognitoUserPool:
          pool: ${self:custom.userPoolName}
          trigger: VerifyAuthChallengeResponse
          existing: true

Part 2: Pseudo Backend Code

Now that we've got our Cognito handlers set up and a user pool, we can run through some example server side code that handles the auth sequence. We start with the SRP_A (Secure Remote Password) challenge which kicks off the authentication flow.

Login Route: Kick off auth flow and prepare custom challenge

router.post('/login', async (req, res, next) => {
  try {
    const SRP_A = CognitoHelper.calculateA()
    const userHash = generateHash(req.body.username, this.secretHash, this.clientId)
    const params = {
      UserPoolId: this.poolId,
      AuthFlow: 'CUSTOM_AUTH',
      ClientId: this.clientId,
      AuthParameters: {
        USERNAME: req.body.username,
        PASSWORD: req.body.password,
        SECRET_HASH: userHash,
        SRP_A: SRP_A,
        CHALLENGE_NAME: 'SRP_A'
      }
    }

    try {
      const data = await this.cognitoIdentity.adminInitiateAuth(params).promise()
      // SRP_A response
      const { ChallengeName, ChallengeParameters, Session } = data
      // Set up User for MFA
      const authChallenge = _.map([...Array(8).keys()], n => Math.floor(Math.random() * 10)).join('')
      await this.cognitoIdentity
        .adminUpdateUserAttributes({
          UserAttributes: [
            {
              Name: 'custom:authChallenge',
              Value: `${authChallenge},${Math.round(new Date().valueOf() / 1000)}`
            }
          ],
          UserPoolId: this.poolId,
          Username: req.body.username
        })
        .promise()
      const hkdf = CognitoHelper.getPasswordAuthenticationKey(
        ChallengeParameters.USER_ID_FOR_SRP,
        req.body.password,
        ChallengeParameters.SRP_B,
        ChallengeParameters.SALT,
        this.poolId
      )
      const dateNow = CognitoHelper.getNowString()
      const signatureString = CognitoHelper.calculateSignature(
        hkdf,
        this.poolIdOnly,
        ChallengeParameters.USER_ID_FOR_SRP,
        ChallengeParameters.SECRET_BLOCK,
        dateNow
      )
      const responseParams = {
        ChallengeName: ChallengeName,
        ClientId: this.clientId,
        ChallengeResponses: {
          PASSWORD_CLAIM_SIGNATURE: signatureString,
          PASSWORD_CLAIM_SECRET_BLOCK: ChallengeParameters.SECRET_BLOCK,
          TIMESTAMP: dateNow,
          USERNAME: ChallengeParameters.USER_ID_FOR_SRP,
          SECRET_HASH: generateHash(ChallengeParameters.USERNAME, this.secretHash, this.clientId)
        },
        Session: Session
      }
      // Password verifier response, should be set up for custom mfa challenge now
      const respData = await this.cognitoIdentity.respondToAuthChallenge(responseParams).promise()
      res.status(200).json(respData).end()
    }
  } catch (err) {
    console.log(err)
    if (err.code === 'UserNotFoundException') return res.status(400).send('Invalid Credentials')
    res.sendStatus(500)
  }
})

Verify Route: This handles responding to our auth challenge with Cognito which will call our verify lifecycle handler.

router.post('/verify', async (req, res, next) => {
  try {
    const { email, password, confirmPassword, user, code } = req.body
    const params = {
      ChallengeName: user.ChallengeName,
      ClientId: this.clientId,
      ChallengeResponses: {
        USERNAME: email,
        ANSWER: code,
        SECRET_HASH: generateHash(email, this.secretHash, this.clientId)
      },
      Session: user.Session
    }
    const data = await this.cognitoIdentity.respondToAuthChallenge(params).promise()

    // if we failed the custom challenge
    if (!data.AuthenticationResult && user.ChallengeName === 'CUSTOM_CHALLENGE' && code) {
      data.error = 'Invalid Code'
      return res.json({ success: false, data: data })
    } else {
      // add any user claims here
      data.AuthenticationResult.UserClaims = {}
      return res.json({ success: true, data: data })
    }
  } catch (err) {
    console.log(err)
    res.sendStatus(500)
  }
})

Conclusion

That's it! We've now built a custom authentication workflow for handling MFA by email using Amazon Cognito. Our server code initiates the auth flow and then provides challenge responses until we have verified that the user has a valid MFA code. Happy Codings!

References:

Source Code Repository

Cognito Custom Auth Lambdas

Automating Route53 DNS Updates for Blue/Green Deployments (the hard way)

James Ingold — Fri, 30 Jul 2021 00:54:31 +0000

Blue/Green Architecture

A blue/green architecture in some form or fashion is a necessity for highly available systems. This means that you have at least two seperate production environments. The blue one is in use, handling live traffic while the green one is a clone of the production system and ready to go in case of emergency or routine maintenance. Ideally, these systems are in different regions and possibly even different cloud providers depending on your need to reduce risk. There are several ways to achieve blue/green deployments and it could be as simple as having two different servers which you can switch between. Regardless of your setup, at some point you will have to switch the DNS to point from the blue environment to the green environment. For example, AWS CodePipeline manages this for Elastic Beanstalk. Most environments won't manage the DNS updates for you though, so in this post I'll show you how to script out updates to AWS Route53 to manage blue/green DNS the hard way. For my use case, I have several domains pointed to an elastic load balancer in front of a kubernetes cluster. EKS clusters in different regions make up the blue/green architecture, which means we have to switch the DNS to move between them.

As a side note, it's good to practice switching environments so that the first time isn't under the intense pressure of something going wrong. Switching which environment is live should be as seamingless as possible, with no downtime or stress. In this example, I'll be using Route53 to switch DNS urls from one AWS elastic load balancer to another with a python script.

Boto, the python library for AWS, isn't the easiest to use for Route53. I'll break down each step of the script but if you want to skip ahead, here's the full gist. We're going to build a python utility script to handle updating DNS entries and then a shell script for handling bulk updates. Let's write some code!

Python Script for Updating Route53 Entries

Create a new python file and add the following code:

#!/usr/bin/env python

"""
Update DNS for a RecordSet on AWS Route53
"""
import argparse
import sys

import boto3

parser = argparse.ArgumentParser()

parser.add_argument("--zoneId", type=str, default="", help="Route53 Zone Id, recommend setting this as an environment variable")
parser.add_argument("--hostname", type=str, default="", help="The hostname to update example.domain.com")
parser.add_argument("--dns", type=str, default="", help="The new dns value")

args = parser.parse_args()

route53 = boto3.client('route53')
zoneid = args.zoneId
hostname = args.hostname
CNAME = args.dns


if not zoneid or not hostname or not CNAME:
   print("Please provide a zone id, hostname and new dns")
   return

We're using argparse to pass in our zoneId, hostname, and the new dns. I recommend setting your hosted zone id as an environment variable so you don't have to remember it. If you only have one Route53 hosted zone, you can use Boto to get all the hosted zones (route53.get_all_hosted_zones()) and then use the first one.

Now that we've got our AWS Route53 Hosted Zone ID, we can write the update DNS function. I'm going to use a shell script to pass in an array of domain names because in production, there are some domains that I don't want to update. If you have a simpler set up, you could iterate through the record sets and update each DNS without the need for the shell script. An environment variable could also hold the domains to update without using the shell script as well.

def updatedns(hostname, newdns):
 sets = route53.list_resource_record_sets(HostedZoneId=zoneid)

 for rset in sets['ResourceRecordSets']:
    if rset['Name'] == hostname and rset['Type'] == 'CNAME':
        curdnsrecord = rset['ResourceRecords']
        print(curdnsrecord)
        if type(curdnsrecord) in [list, tuple, set]:
            for record in curdnsrecord:
                curdns = record
        # print('Current DNS CNAME: %s' % curdns)
        curttl = rset['TTL']
        # print('Current DNS TTL: %s' % curttl)

        if curdns != newdns:
            # UPSERT the record
            print('Updating %s' % hostname)
            route53.change_resource_record_sets(
              HostedZoneId=zoneid,
              ChangeBatch={
                'Changes': [
                  {
                    'Action': 'UPSERT',
                    'ResourceRecordSet': {
                      'Name': hostname,
                      'Type': 'CNAME',
                      'TTL': curttl,
                      'ResourceRecords': [
                        {
                          'Value': newdns
                        }
                      ]
                    }
                  }
                ]
              }
            )

We're passing in the hostname to update and the new DNS entry to the updatedns function. From there, we check out each RecordSet and see if it matches the hostname that we're trying to update. If it does, we copy over the time to live property and then change the dns if it doesn't match what was passed in. The Route53 client's change_resource_record_set is handy because we can use the 'UPSERT' action to create or update by hostname.

That's it for the python file, check out the full gist here.

Bonus: Shell script to Pass Domains to Update

#!bin/bash
# Shell script to update AWS Route53 DNS when switching Blue/Green Deployments

if [ $# -lt 1 ]
then
  echo "Please supply $1 destination DNS address (likely an ELB address)"
  return 1
fi

# array of domains to update
domains=("example.mydomain.com" "example2.mydomain.com")

for i in "${domains[@]}"
do
  echo "updating $i";
  python updatedns.py --zoneId="$MY_ROUTE53_HOSTED_ZONE" --hostname="$i" --dns="$1"

done

echo "Finished updating Route53 records"

The End

That's it! We've now automated changing DNS for blue/green environments using AWS Route53, the hard way. If you know any easier way, let me know! Happy Codings

References:

Boto Route53 Documentation

updatedns.py gist

Migrating from Sequel Pro to Sequel Ace

James Ingold — Thu, 29 Jul 2021 04:13:26 +0000

Sequel Pro is a MySQL/MariaDB management GUI for macOS and it has been around for a long time. Sequel Pro's first release was in 2008 and at the time of this writing, the last one was in 2016. I've been using Sequel Pro for the past seven years but with the update to Big Sur, it's time to move on. Sequel Pro has become slow to use but I've continued on using it as a habit. A co-worker mentioned that the project forked into one called Sequel Ace. It's still open source and actively maintained. Here's how to install Sequel Ace and pull over your favorite database connections from Sequel Pro with the least amount of headache.

Install Sequel Ace

brew install --cask sequel-ace

and if you have a Brewfile, add the cask "sequel-ace"

Copy Preferences and Favorites to Sequel Ace

cp ~/Library/Preferences/com.sequelpro.SequelPro.plist ~/Library/Containers/com.sequel-ace.sequel-ace/Data/Library/Preferences/com.sequel-ace.sequel-ace.plist

cp ~/Library/Application\ Support/Sequel\ Pro/Data/Favorites.plist ~/Library/Containers/com.sequel-ace.sequel-ace/Data/Library/Application\ Support/Sequel\ Ace/Data/Favorites.plist

Copy Passwords (optional)

The database connections that we copied over will not have the passwords because those are stored in the Keychain Access app. You can re-enter those passwords and save them as you connect to the different databases or you can open KeyChain Access and copy them over ahead of time. Open up KeyChain Access and search for "Sequel Pro". From there, you can click on the different connections and then check "Show Password" to view the plain text password. Next, copy that password over to Sequel Ace.

If you have many favorite connections, Sequel Ace offers the following bash script:

#!/bin/bash

results=$(security dump-keychain -r | grep "Sequel Pro" | sed -E -e 's/^.*"([^"]+)"$/\1/' | sort | uniq)
IFS=$'\n' read -r -d '' -a items < <(printf '%s\0' "$results")

for key in "${items[@]}"
    do
        newkey=$(echo "$key" | sed -E -e 's/Sequel Pro/Sequel Ace/')
        echo "Migrating: $key --> $newkey"
        account=$(sudo security find-generic-password -l "$key" | grep "acct\"<blob>" | sed -E -e 's/^.*"acct"<blob>="(.+)"$/\1/')
        pwd=$(sudo security find-generic-password -l "$key" -w)
        security add-generic-password -a "$account" -s "$newkey" -w "$pwd" -T "/Applications/Sequel Ace.app" -U
        echo "Done: $key --> $newkey"
done

Uninstall Sequel Pro

brew uninstall --cask sequel-pro

Wrapping Up

Now we've got our Sequel Pro connections moved over to Sequel Ace, an actively maintained database GUI ready for the next decade of coding on a Mac!

References:

Migrating From Sequel Pro

Setting up a Serverless Project with Webpack, Babel, and Knex

James Ingold — Sun, 20 Dec 2020 01:45:55 +0000

Using Webpack with the Serverless Framework is handy if you want to use the latest Javascript features along with Babel. It also helps to optimize the packaging of functions so we can make sure we're only shipping code that is lean and mean. However, adding the delightful query builder Knex to the mix can cause some issues that I spent a good amount of time on. Hopefully this article will help anyone dealing with similar issues save some time.

In this article we'll go through setting up a Serverless project with Webpack, Babel, and Knex along with Prettier and Eslint. We'll focus on specific issues with Knex in this scenario and how to solve them. If you want a TLDR; here's the final output, a Serverless starter template with Webpack, Babel, and Knex ready to go.

Project Setup

Install serverless globally

npm install serverless -g

First, we'll set up a new Serverless project using a default aws-nodejs template:

serverless create --template aws-nodejs

This will create a bare handler.js and a serverless yaml file to get us started.

Next add a package.json file to manage our dependencies

npm init -y

Add Dev Dependencies and Webpack:

We're going to add Babel to get access to the latest Javascript features and then we'll add Webpack to transform our Javascript code in a way that the Serverless platforms (AWS) can handle. We'll also add Serverless-Offline which emulates AWS and AWS Gateway, allowing us to run our functions locally.

npm install --save-dev @babel/core @babel/preset-env webpack serverless-webpack serverless-offline babel-loader dotenv

Adding Source Map Support

It's always nice to get stack traces, let's set up source map support.

npm install source-map-support --save npm install
babel-plugin-source-map-support --save-dev

The source-map-support module provides source map support for stack traces in node via the V8 stack trace API

Babel-plugin-source-map-support prepends this statement to each file, giving us stack traces with the source-map-support package:

import 'source-map-support/register';

Setting up Babel

Create a .babelrc file in the root of the project to handle our Babel configuration:

.babelrc

{
  "plugins": ["source-map-support"],
  "presets": [
    [
      "@babel/preset-env",
      {
        "targets": {
          "node": true
        }
      }
    ]
  ]
}

Adding Knex

Next, we'll add Knex and MySQL as the driver of choice for this purpose:

npm install --save mysql2 knex

Setting up Knex

Create a knexfile.js in the project root:

import dotenv from "dotenv";
dotenv.config({ silent: true });

module.exports = {
  development: {
    client: "mysql2",
    connection: {
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PW,
      database: process.env.DB_DB
    }
    // migrations: {
    // directory: './database/migrations',
    // },
    // seeds: { directory: './database/seeds' }
  },
  staging: {
    client: "mysql2",
    connection: {
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PW,
      database: process.env.DB_DB
    }
  },
  production: {
    client: "mysql2",
    connection: {
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PW,
      database: process.env.DB_DB
    }
  }
};

Create a folder called queries in your project root, this will be where the data retrieval functions will go:

mkdir queries

Add a knex file:
knex.js

const knex = require("knex");

const knexfile = require("../knexfile");

const env = process.env.NODE_ENV || "development";

const configOptions = knexfile[env];

module.exports = knex(configOptions);

Example query file - games.js:

const knex = require("./knex");

export async function getAll() {
  const res = await knex("matches").select("*");
  return res;
}

Setting up Webpack

In the root of the project create a webpack.config.js file and configure Webpack to use Babel to bundle up our Serverless functions.
We'll also exclude node development dependencies using the node externals package.

npm install webpack-node-externals --save-dev

webpack.config.js:

const slsw = require("serverless-webpack");
const nodeExternals = require("webpack-node-externals");

module.exports = {
  entry: slsw.lib.entries,
  devtool: "source-map",
  // Since 'aws-sdk' is not compatible with webpack,
  // we exclude all node dependencies
  externalsPresets: { node: true },
  externals: [nodeExternals()],
  mode: slsw.lib.webpack.isLocal ? "development" : "production",
  optimization: {
    minimize: false
  },
  performance: {
    // Turn off size warnings for entry points
    hints: false
  },
  // Run babel on all .js files - skip those in node_modules
  module: {
    rules: [
      {
        test: /\.js$/,
        loader: "babel-loader",
        include: __dirname,
        exclude: /node_modules/
      }
    ]
  },
  plugins: []
};

Setting up Serverless

Add our plugins to the serverless.yaml file:

- serverless-webpack
- serverless-offline

Add serverless-webpack configuration to serverless.yaml

custom:
  webpack:
    webpackConfig: ./webpack.config.js
    includeModules: true # enable auto-packing of external modules

We'll add an http endpoint to the default hello handler, so that we can test our api endpoint out:

events:
  - http:
    path: hello
    method: get
    cors: true

Full Serverless.yaml

service: serverless-webpack-babel-knex-starter
frameworkVersion: "2"

provider:
name: aws
runtime: nodejs12.x
apiGateway:
  shouldStartNameWithService: true

plugins:

- serverless-webpack
- serverless-offline

functions:
  hello:
    handler: handler.hello
      events:
        - http:
          path: hello
          method: get
          cors: true

custom:
  webpack:
  webpackConfig: ./webpack.config.js
  includeModules: true # enable auto-packing of external modules

Running and Knex Issues

Let's test it out!
Add a start npm script to package.json

"start": "serverless offline start --stage dev --noAuth"

Call our API

curl --location --request GET 'http://localhost:3000/dev/hello'

Knex Runtime Issues:

ES Modules may not assign module.exports or exports.*, Use ESM export syntax, instead: ./knexfile.js

It doesn't like that we're using module.exports in our knexfile, one potential solution would be to use es6 default export syntax
export default {}

This ended up causing more problems then it solved dealing with the internal knex library which doesn't play well with ES modules.

The solution I went for is to use a Babel plugin to transform ESM to CommonJS Modules which is the standard for Node modules. Client-side JavaScript that runs in the browser use another standard, called ES Modules or ESM.
In CommonJS, we export with module.exports and import with require statements. Since we're using Babel we can use import/export and our code will be transformed into CommonJS modules.

npm install --save-dev @babel/plugin-transform-modules-commonjs

Add to our plugins section in .babelrc

{
  "plugins": ["source-map-support", "@babel/plugin-transform-modules-commonjs"],
  "presets": [
    [
      "@babel/preset-env",
      {
        "targets": {
          "node": true
        }
      }
    ]
  ]
}

Using CommonJS should be enough to get you going but you might run into the next issue:

Can't resolve runtime dependencies

Module not found: Error: Can't resolve 'oracledb'
Module not found: Error: Can't resolve 'pg-native'
Module not found: Error: Can't resolve 'pg-query-stream'
Module not found: Error: Can't resolve 'sqlite3'

If you receive module not found errors for packages that you are not using, then we can fix this by ignoring those drivers/packages.
There are different ways this can be approached with Webpack and with Serverless but the solution that I landed on was to use the NormalModuleReplacementPlugin which is bundled with Webpack. This plugin allows you to replace resources that match a regular expression with another resource. We'll add the noop2 package to replace the drivers we're not using with a "no operation module".

npm install --save-dev noop2

const { NormalModuleReplacementPlugin } = require("webpack");

plugins: [
  // Ignore knex runtime drivers that we don't use
  new NormalModuleReplacementPlugin(
    /mssql?|oracle(db)?|sqlite3|pg-(native|query)/,
    "noop2"
  )
];

Adding Eslint and Prettier

To finish this starter template, we'll add some niceness to the project with eslint and prettier.

npm install --save-dev @babel/eslint-parser eslint eslint-config-prettier eslint-plugin-lodash eslint-plugin-prettier prettier

prettierrc.json

{
  "trailingComma": "none",
  "tabWidth": 2,
  "semi": false,
  "singleQuote": true,
  "printWidth": 120
}

.eslintrc.js

module.exports = {
  env: {
    node: true
  },
  plugins: ["prettier"],
  parser: "@babel/eslint-parser",
  parserOptions: {
    sourceType: "module",
    ecmaFeatures: {
      classes: true,
      experimentalObjectRestSpread: true
    }
  },
  extends: [
    "eslint:recommended",
    "plugin:prettier/recommended",
    "plugin:lodash/recommended"
  ],
  rules: {
    "prettier/prettier": "error"
  }
};

Starter Project

Now we have a nice starter project to get us off the ground with Serverless, Webpack, Babel, and Knex.

To grab all this goodness or if you have improvements, check out the Github
repository

The Ultimate Guide to Learning Neo4j with Netflix's Dark Series

James Ingold — Sat, 30 May 2020 15:28:12 +0000

Netflix's Dark is the best TV series ever created. Hands down. Dark is a science fiction mind-bender and time twister where children in the town of Winden go missing. Folks in the town start asking questions and before you know it your head is spinning through multiple different universes as time unravels. Graph databases were built to handle highly-connected and complicated data. I can't think of better adverbs for Dark. Time and everything else is woven together, highly related and complex. Let's learn abut graph databases and explore Netflix's Dark TV Series as we pick up some Neo4j knowledge.

This post is written just before Season 3 comes out, so we'll be making some predictions in this post.

SPOILER ALERT - THERE WILL BE SPOILERS

Goals

To learn about Graph Databases using Neo4JS
To gain insights into the great Dark series

Neo4j (Better Data, Betters Decisions) Crash Course

Neo4j is a flavor of graph database. These types of databases are relational but they're not a traditional relational database. In fact, traditional relational databases are not great at handling highly related data. We'll get more into that later in this post.

Neo4j was originally built on top of MySQL, but it was redesigned from the ground up to focus specifically on optimizing graphs. It features ACID (Atomicity, Consistency, Isolation, Durability) transactions:

Atomicity => All or nothing, if the transaction fails, it's rolled back.
Consistency => this is debatable as Neo4j is schemaless but we'll give it to them as the schema is in a consistent state.
Isolation => This requires that multiple transactions which are executed in parallel on the same database instance do not impact each other.
Durability => Persisted, data in Neo4j is written to disk.

Nodes, Labels, Properties, Relationships

A graph database is a collection of nodes which can be labeled, connected by relationships, both of which can have properties. A node is an object or an entity. In our Dark data, we'll have nodes that have labels of Character, Family, Year and Sic Mundus. For example, Jonas Kahnwald is a character in Dark and a node in our graph. He is related to another node Hannah Kahnwald. This relationship is that Hannah is his parent. We have a PARENT_OF relationship. Jonas could also have a CHILD_OF relationship pointing back to Hannah. There can be multiple relationships between nodes. These relationships are what connect nodes in the graph. The id of a node is an auto incrementing integer but it can also be set manually. Properties are key value pairs where key is a string and value is a primitive. Properties can be indexed, if the property for the index is not there, the node simply is not indexed. Properties can also have constraints.

Graph databases are schemaless, meaning they are highly flexible. You can easily add properties to new nodes. Relational databases have nullable columns, but for graph databases, you just don't add the properties. This gives us some nice document database (MongoDB) features with highly relational data.

Why not just use a traditional relational database?

Relational databases are actually quite anti-relational. There is a limit to the number of join operations that a system can effectively perform, before the "join bombs" go off and the system becomes very unresponsive. Anyone who has spent time with an RDMS knows this intuitively but it's not clear until something better comes along to offer you the red pill. (Neo4j really brings out the Matrix references). Instead of tables like in a relational database, we have nodes. Instead of using joins, we have pattern matching. Joins decrease performance exponentially. Joins are expensive. Relations are cheap. In short, relational databases weren't actually built for deeply related data.

In Neo4j in Action - Affiliate Link they compare Neo4j against MySQL for a social network. They show that when you have 110,000 records and need to go 3 levels deep, Neo4j takes less than a second while MySQL takes 30,000 seconds. At a certain point, say five levels deep, the relational database is not even usable.

When to Use

Complex queries: when the types of questions that you want to ask of your data are composed of a number of complex join-style operations. Basically, when you have more than 3 joins, prefer pattern matching queries.
Pathfinding queries: where you will be looking to find out how different data elements are related to each other.

Performance: as soon as you grab a starting node, the database will only explore the things that relate to it and will be completely oblivious to anything that is not connected to the starting node. This makes querying by patterns efficient.

When to not use a Graph Database:

When the complexity is low. Graph databases are meant for highly related and complex data. If you don't have that, use a traditional relational database or NoSQL solution. You need to envision joining multiple tables together in your project. If you're going to go 3 or more levels deep, take a look at graph databases.

Query Language

Cypher is the declarative, pattern-matching query language for Neo4j. It makes graph database management systems understandable and workable for any database user. We'll learn some more about Cypher later in this tutorial.

Getting Started

Now that we're acquainted, let's get up and running by starting our own instance of Neo4j. We'll grab the Docker image, enabled with apoc (Awesome Procedures Of Cypher) - Apoc kills Cypher in the Matrix :D. Apoc isn't going to rid our use of Cypher but it is the home to lots of cools functions and graph algorithms. Like most databases, Neo4j stores it's data on the file system (Durability), so we will map some folders to our local system that Neo4j in Docker can use.

docker pull neo4j

docker run \
    --name neo4j \
    -p7474:7474 -p7687:7687 \
    -d \
    -v $HOME/neo4j/data:/data \
    -v $HOME/neo4j/logs:/logs \
    -v $HOME/neo4j/import:/var/lib/neo4j/import \
    -v $HOME/neo4j/plugins:/plugins \
    --env NEO4J_AUTH=neo4j/securepassword \
    --env NEO4JLABS_PLUGINS='["apoc"]' \
    --env apoc.import.file.enabled=true \
    neo4j:latest

docker container logs testneo4j -f

Ports we exposed:
7474: Neo4J Browser (View our data in a local web browser)
7687: Bolt port (how we communicate with the database)
We mapped some folders: data, logs, plugins to our home/neo4j directory.
We created a neo4j user with "securepassword" as the password.

Open up the Neo4j Browser at http://localhost:7474/browser/ and make sure it loads

Setting up our Dev Environment

Now that we've got a Neo4j instance up and running, let's get a couple things in order for our development environment. Here are a few tools to use when working with Neo4j and the Cypher query language. These are handy for writing Cypher but if you want to stay just in the Neo4j Browser, that will work perfectly fine as well.

VSCode/Codium:

This is an excellent Cypher extension for VSCode editors: agatlin/vscode-cypher-query-language-tools.

Vim: Syntax highlighting

Data

I created my own Dark data set which was heavily influenced by this great reddit post from season one. The Dark Wikipedia is also a great source of Dark knowledge. This Gitub repository will contain characters.json which you will need to move to $HOME/neo4j/import to be able to use the corresponding cypher import script below.

cp characters.json $HOME/neo4j/import

Open up characters.json and we'll take a look at the data. There are several relations defined in there, this is a series about a small town in Germany so mainly we're talking about families. People also tend to die of non-nature causes.

siblings
parents
parentOf
married
killedBy
sicMundus

Relations can also contain properties, so killedBy could have date.

The characters.json file defines character objects and the relationships above. Here is a json representation of our main character Jonas Kahnwald.

{
  "name": "Jonas Kahnwald",
  "lastname": "Kahnwald",
  "alias": [
    "Stranger",
    "Adam"
  ],
  "years": [
    1921,
    1953,
    1986,
    2019,
    2052,
    2085
  ],
  "parents": [
    "Hannah Kanhwald",
    "Mikkel Nielsen"
  ],
  "sicMundus": true
}

The Plan => Cypher Language and Importing Data

Now that we've had a good look at the data, let's import it. Take a look at the import cypher file. This is the code we will run to import characters.json into the database. Once you've read the below comments, run the file in the Neo4j Browser.

We'll start out by creating some constraints for our character and year node. We're indexing families and characters on name. While, we won't run into performance issues with this small of a data set, it is good practice to get into the habit.

CREATE constraint on (c:Character) assert c.name is unique;
CREATE constraint on (y:Year) assert y.id is unique;  
CREATE constraint on (s:SicMundus) assert s.name is unique
CREATE index on :Character(name);
CREATE index on :Family(name);

Next, we'll manually create some nodes for years and family. We could have done this using the json but I just wanted to get up and running:

CREATE (`1921`:`Year` {id: 1921}), (`1953`:`Year` {id: 1953}), 
...
...

Enter apoc. Now we'll use an Awesome Procedure on Cypher function to import our character.json file. We reference it by the word value, then we'll UNWIND the array of characters, basically iterating through each character object in the json. We'll use another apoc function to clean up any empty or nulls. From there we create a new Character node with a name and then attach the rest of the properties.

CALL apoc.load.json("file:///characters.json") YIELD value 
UNWIND value.characters AS character
with apoc.map.clean(character, [],['',[''],[],null]) as data
MERGE (c:Character {name:data.name})
SET
c += apoc.map.clean(data, [],['',[''],[],null])

Now onto setting up relationships. Mapping our array relationships is fairly straight forward. We'll loop through each relationship property and add the appropriate relationship. We're using MERGE which will create a node or use an existing. Remember, we have a unique constraint on character name. Notice how the relationships can go either way.

FOREACH (name in data.married | MERGE (m:Character {name:name}) MERGE (m)-[:MARRIED]->(c))
FOREACH (name in data.parentOf | MERGE (m:Character {name:name}) MERGE (c)-[:PARENT_OF]<-(m))
FOREACH (name in data.siblings | MERGE (m:Character {name:name}) MERGE (m)-[:SIBLING_OF]->(c))
FOREACH (name in data.killedBy | MERGE (m:Character {name:name}) MERGE (c)-[:KILLED_BY]<-(m))
FOREACH (id in data.years | MERGE (y:Year {id:id}) MERGE (c)-[:WHEN]->(y))

We have a couple properties to relate to nodes, Sic Mundus and Family which are just primitives (boolean and string). We use case to test if the property is null and then empty it out before once again using MERGE to create our nodes and relationships.

FOREACH (name in case data.lastname when null then [] else [data.lastname] end | MERGE (f:Family {name:name}) MERGE (c)-[:FAMILY]->(f))    
FOREACH (name in case data.sicMundus when null then [] else [data.sicMundus] end | MERGE (s:SicMundus) MERGE (c)-[:SIC_MUNDUS_MEMBER]->(s))

Perfect, now let's view entire graph in the Neo4J Browser

match (n) return n

Basic Cypher Quick Hits

Now that we've got a populated Neo4j database and an introduction to Cypher, let's dive a little deeper on this steak loving declarative querying language.

CREATE: This creates nodes and relationships with their properties.

CREATE (`Jonas Kahnwald` :`Character` {name:'Jonas Kahnwald'}-[:CHILD_OF]->(`Hannah Kahnwald`:`Character` {name: 'Hannah Kahnwald'})

We've created two nodes Jonas and Hannah and added a relationship of CHILD_OF. Relationships can go both ways or we could reverse it like this:

(`Jonas Kahnwald` :`Character` {name:'Jonas Kahnwald'}<-[:PARENT_OF]-(`Hannah Kahnwald`:`Character` {name: 'Hannah Kahnwald'})

MATCH: basically like a SQL select, it describes a pattern that should match in the database.

WHERE: pretty much like SQL, filters.

A difference between SQL and Cypher is that in the latter, you return the data that you want declaratively. As we saw above, this Cypher will return the entire database. Match all nodes and then return them.

match (n) return n

We can query for relationships as well, this will look at all the Years when Magnus Nielsen has been present. We are just returning the nodes here but we could return properties such as c.name, y.id, etc.

MATCH(c:Character)-[r:WHEN]->(y:Year) where c.name = 'Magnus Nielsen' return c, y

We could make a character, not a character by removing a label:

MATCH (c:Character) 
WHERE c.name = 'Magnus Nielsen'
REMOVE c:Character
RETURN c ;

Remove a node:

MATCH (c.Character, {name: 'Magnus Nielsen'})
DELETE c

Use detach delete to remove relations and then delete node:

 MATCH (c.Character {name: 'Magnus Nielsen'})
 DETACH DELETE c;

WITH: This is a handy keyword which passes results from one query part to the next. WITH is like RETURN; it allows you to separate the query in parts. You'll see this in action in the next section.

MERGE: As mentioned above, this is like CREATE but it won't fail if the node or relationship already exists.

Contrived example of WITH and MERGE

MATCH (h: {name: "Hannah Kahnwald"}) as hannah  
WITH MATCH(j:User {name: "Jonas Kahnwald"}) jonas
MERGE (hannah)-[:PARENT_OF]->(jonas)

Questions and Findings

Now we have more than enough Neo4j knowledge to be dangerous. Let's get to the fun part, finding some patterns in our dataset! When creating a graph database, it's good to know what kind of questions you will want to ask the data. Here is a list of questions and queries:

Who is the most mysterious (least amount of relations)?

MATCH(c:Character)-[r]-(x) RETURN c.name, COUNT(*) as relations
ORDER BY relations limit 5

Bartosz Tiedeman and Clausen each come in with 2 nodes. Yasin Friese only has one, however Clausen and Bartosz are likely key characters who we will find out more about in Season 3.

Are there any characters not related to a family?

MATCH (c:Character) WHERE NOT (c)-[:FAMILY]->() RETURN c

Nope, every character has a family.

A better question is which family has the most members?

MATCH(c:Character)-[r:FAMILY]-(f:Family) RETURN COUNT(c) as cnt, f.name ORDER BY cnt desc

Despite Jonas being the main character, the Nielsen's have the most known family members at 9. While the Kahnwalds sit at 4, not including Mikkel. The Doppler's come in at 7.

Which "family" has only one member?

MATCH(c:Character)-[r:FAMILY]-(f:Family) 
WITH count(c) as cnt, f
WHERE cnt = 1
RETURN f.name

What are the most active time segments?

MATCH(c:Character)-[r:WHEN]->(y:Year) RETURN count(c.name) as cname, y.id

2019 is obviously the most when'd timeverse as that's where Dark starts. As the timeverses edge out, we know less characters who have traveled there.

Who has gone the farthest in time?

MATCH(c:Character)-[r:WHEN]->(y:Year) return c.name, count(r) as whens ORDER BY whens desc

Jonas is the only confirmed character to have traveled to 2085. We will probably learn more about that year in Season 3. He has also seen the most timeverses.

Which characters are in Sic Mundus?

MATCH(c:Character)-[:SIC_MUNDUS_MEMBER]->(s:SicMundus) RETURN c

Now for our most ambitious query of this post, who are the likely other Sic Mundus members?

This type of query can give you a hint to what Neo4j can do in terms of predictions or recommendations out of the box. Notice how we can specify which relationships we're looking for with -> and -

MATCH(c:Character)-[:SIC_MUNDUS_MEMBER]->(s:SicMundus)
WITH c
MATCH(c)-[r]-(x:Character)
WHERE not (x)-[:SIC_MUNDUS_MEMBER]->() and not (x)-[:KILLED_BY]->()
RETURN x.name, count(r) as cnt
ORDER BY cnt desc

"Martha Nielsen" 2
"Elisabeth Doppler" 2
"Charlotte Doppler" 2

Martha, Elisabeth, and Charlotte all seem likely Sic Mundus candidates. This query could be much higher quality if we had even more data points to look at.

Which characters have only been to one timeverse?

MATCH(c:Character)-[r:WHEN]->(y:Year) WITH c.name as name, count(r) as whens WHERE whens = 1 return name

"Greta Doppler"
"Doris Tiedeman"
"Daniel Kahnwald"
"Mads Nielsen"
"Torben Woller"
"Erik Obendorf"
"Bartosz Tiedeman"
"Ulla Obendorf"
"Peter Doppler"
"Yasin Friese"
"Jurgen Obendorf"
"Clausen"

Most characters don't skip time frames, if you've been to 1986, you've likely been to 1953 or 2019. However, some characters have probably warped around the 33 years time frames, let's find them.
There are a few new functions to explain:

collect will turn a group of nodes or properties into an array.

Size is like a .length or .count - it gets the size of the array.

Reduce lets you do some iteration over a collection.

MATCH(c:Character)-[r:WHEN]->(y:Year)
WITH c as character, y
ORDER BY y.id
WITH character, reverse(collect(y.id)) as years
WITH character, years, range(0, size(years)-1) AS is
WITH character, years, is, years + (last(years) - 33) as isa
WITH character, years, reduce(total=0, n in is | (total + toInteger((isa[n] - isa[n+1]) / 33 - 1))) as sum
WHERE sum > 0
RETURN character.name, years

"Franziska Doppler" [2019, 1921]
"Magnus Nielsen" [2052, 2019, 1921]

I think it's a good bet that we see a lot more of Magnus and Franziska in Season 3 as most characters don't skip around the time frames.

Which characters have never been to the past?

MATCH(c:Character)-[r:WHEN]->(y:Year) 
WITH c as character, MIN(y.id) as my
WHERE my >= 2019  
RETURN character.name

Bartosz is questionable - there is a theory that Noah killed him in back in 1921. Bartosz as noted above is one of the more mysterious characters and there are rumors that he is also a Sic Mundus member. Will these characters travel further back in season 3?

"Torben Woller"
"Erik Obendorf"
"Bartosz Tiedeman"
"Ulla Obendorf"
"Peter Doppler"
"Martha Nielsen"
"Elisabeth Doppler"
"Yasin Friese"
"Jurgen Obendorf"
"Clausen"

Which characters are the most deadly?
<> means not equals or does not match.

MATCH(c:Character)-[r:KILLED_BY]->(k:Character) 
WHERE c.name <> k.name
RETURN c.name, k.name

Noah has killed Claudia Tiedeman, Mads Nielsen. You could probably add Yasin to this list as well and possibly Bartosz. Agnes Nielsen killed Noah. The Nielsen family is the most deadly. What does that mean for Season 3?

What percentage of deaths in Dark have been suicides?

MATCH(c:Character)-[r:KILLED_BY]->(k:Character) 
WHERE c.name <> k.name
WITH count(c) as cnt
MATCH(s:Character)-[r:KILLED_BY]->(sk:Character) 
WHERE s.name = sk.name
RETURN toFloat(count(s)) / (count(s) + cnt)

40%

Conclusion

Hopefully this post has helped you get up to speed with Neo4j with data from Netflix's Dark Series. If you noticed while running the queries, all of them take under 10ms. Crazy fast. There are definitely ways to improve the Dark dataset. For instance, we could have a relationship of APPEARS_IN with each season being a node. There could also be an INTERACTS_WITH relationship when characters interact with each other and a count on that relationship. These queries may not be the most efficient to express our questions.

What do you think? Have better ways to create the data model? Got some cool queries to run? Want to add to the Dark dataset? Checkout the Github repo.

Replacing AngularJS $templateCache with Webpack

James Ingold — Tue, 19 May 2020 16:58:19 +0000

AngularJS Webpacking
Part One - Megathread to Convert Gulp/Bower Build to Webpack in AngularJS
Part Two - You are here now

In part two of transitioning a Gulp/Bower built AngularJS app to Webpack, we'll be picking up where we left off: replacing AngularJS's $templateCache with Webpack.

We left off with a production Webpack configuration that contained a plugins section somewhat like this:

// webpack.prod.js    
plugins: [
      new CleanWebpackPlugin(),
      new CopyPlugin([
        { from: 'app/**/*.html', context: 'public' }, // TODO: need to figure out template cache with webpack
        { from: 'favicon.ico', to: 'favicon.ico', context: 'public' }
      ]),
      new MiniCssExtractPlugin({
        filename: '[name].[hash].css',
        chunkFilename: '[id].css'
      }),
      new webpack.IgnorePlugin(/^\.\/locale$/, /moment$/) // ignore all locales by default, only ship with en-us 
]

We're going to figure out that TODO item.

What is the $templateCache in AngularJS?

It's a simple key-value store returned from the $cacheFactory. The $templateCache is a cache of your templates. The key is the url to the template and the value is the HTML content.

How it works:

When you request a template, AngularJS will check the $templateCache service first. If it's found, great, you've saved a request to the server. If it's not then the template is retrieved and it gets stored in the cache. From there, it can quickly be retrieved on subsequent calls.
Or calling the cache directly:

$templateCache.get('mytemplate.html')

What we were doing on builds was pre-populating the $templateCache, so that the very first call is a cache hit and not a cache miss.
Here's the gulp build task that did the building using gulp-angularTemplateCache:

gulp.task('templatecache', function() {
    return gulp.src('app/**/*.html', {cwd: path + '/public/'})
      .pipe($.angularTemplatecache('templates.js',
        { module: 'templateCache', standalone: true, root: '/app/'}))
      .pipe(gulp.dest('dist'));
    });

Webpack HTML Templates - Enter html-loader

In part one, we were moving all of the HTML files to the dist/ folder. This was somewhat by design, as I wanted to modify the least amount of code as possible to make the move to Webpack a seamless transition for developers on the team. Now that Webpack is settled in as the build process and we're not getting tons of bug reports, it's time to tie up the loose ends. We want to get those HTML templates/fragments into our bundles.

There are two options: continuing to use AngularJS's $templateCache: build a templates.js file and import it into app.js or use Webpack.

I initially set out to use the $templateCache because that's what the app was doing before Webpack. But after reading more about html-loader in Webpack, I decided to get rid of the $templateCache all together.

html-loader: exports HTML as a string. Any time we require an html file, it will be returned from our bundle. Super speedy like. This functionality completely replaces the $templateCache and there is no reason to continue to use this native AngularJS service when we're going to move away from the framework in the future.

The Process

require() all templates, change templateUrls to templates. Since the html-loader will be returning the HTML string, we need to use the template property instead of templateUrl which expects a file path. All of our template urls were pathed to the app folder, we can just use the relative file when requiring.
```
templateUrl: '/app/analytics/analytics.html'
```
To:
```
template: require('./analytics.html')  
```

Transform ng-includes into components. ng-includes promotes using HTML fragments which are usually accompanied by an ng-controller="controller as vm". This was okay five years ago, but now it's better to use AngularJS components (if you still have to use AngularJS :D)

index.html (or index.pug in my case)

<div id="Header" class="fixed-top border-bottom" ng-include="'/app/header/myheader.html'"></div>

header.component.js

  const module = angular.module('app.header')

  module.component('myHeader', {
    bindings: {
    },
    controllerAs: 'vm',
    controller: 'headerController',
    template: require('./myheader.html')
  })

index file, componentized

    <my-header />

Make sure you're using valid html, as html-loader will error if there are issues. I had one problem where single quotes were used in an attribute like class='red' and html-loader did not like that.
Clean up app.js - I had some previous TODO comments to remove and I could delete the old templates.js file
```
    -// TODO: template cache?    
    -// require('./templates')
```

Remove copy plugin line from webpack.prod.js:

-  { from: 'app/**/*.html', context: 'public' }, // TODO: need to figure out template cache with webpack

Wrapping Up

We've removed a uniquely AngularJS $templateCache service with Webpack and moved some HTML fragments to components. All of this will be of use as we slowly transition away from AngularJS. Also, in Webpack's production mode, the HTML will be minified which is great. We don't need to modify our Webpack configuration further for deployments. We've added another notch in our Webpack belt as our templates are now bundled. The next steps are better bundle splitting and potentially using the file-loader plugin for the images. Stay tuned!

Originally posted on jamesingold.com

Social Distancing, Moving Offices, and an Introduction to Probabilistic Thinking

James Ingold — Sat, 16 May 2020 22:35:40 +0000

originally posted on jamesingold.com

Juxly is moving offices, it's not a big move, just right down the street but we have to pack and unpack: all the fun stuff that goes with moving. We've been working remotely for around two months at this point due to Covid-19 but we'll need to go to the office to pack or take stuff home that we don't want moved.

A co-worker created a schedule to use for who is going to the office and what time. This is to stay within the social distancing guidelines. My knee-jerk reaction to the calendar was that it "seems like overkill". We have 14 days to pack and ~12 in-office employees. I thought, we don't need a schedule (too many days, too few employees) but I wanted to examine that further with a mini introduction into probabilistic thinking.

Probabilistic thinking is using probability theory (statistics) in every day life. What is the expectation that X will happen when you know Y sorts of things? You probably do this naturally from habit and life in general. You see dark clouds in the sky and internally your view of probable rain goes up a tick.
In probability terms, things are expressed as percentages (0 to 1). When you see that dark cloud in the sky, depending on its nature maybe your expectation of rain goes from 0 to 20% or 90% if it looks really nasty.

When you start to look around, probability is baked into pretty much everything in life.

Life Skill: Thinking in Percentages

Annie Duke has a great book called "Thinking in Bets" which details how to go from thinking in binary (yes/no) to expressing thoughts as percentages. This is immensely powerful, because black and white outcomes are actually quite rare. For most outcomes, the best answer is "it depends". Things in real life are not simple but we tend to simplify our thinking because it's easy. We can only make so many decisions in each day. But there is a big difference between "No, that can't happen", to "there's probably a 5% chance of that happening". “I’m not sure” is simply a more accurate representation of the world than it can't happen. When you change the way you think, you start to illuminate the unknown and enter a world of possibilities.

Back to the Task at Hand

That's enough of the map, let's get into the territory. What is the probability that multiple people will be in the office at the same time?

We're going to examine this from a probabilistic nature, there are probably more ways to evaluate this and spoiler - being extra cautious in a time like this is the right call. We're going to use a schedule, this is just a learning exercise to examine my thought processes and explain why my gut reaction was initially adverse to the schedule.

Let's start with the basics, we have 14 days to pack up and 24 hours in each day.

That's 14 * 24 = 336 hours

20,160 minutes (336 * 60)

How long does it take someone to move? This is definitely a flexible metric, some people will have more stuff and some less. I picked a half hour out of thin air. It seems reasonable, it's just a workspace right?

20,160 / 30 = 672

We have 672 half hour chunks to work with.

My co-workers are rationale people though and probably are not going to be burning the midnight oil to pack their workspaces up. Let's remove between 10pm and 6am from our time slots. That gives us only 16 hours in a day.

224 hours (14 * 16)

(224 * 60) / 30 = 448 available half hour chunks

What are the odds that two people will pick the same half hour chunk to pack up?

This is essentially a birthday problem. What is the likelihood that N randomly chosen people will have the same birthday. Or in another way, what is the likelihood that 12 people will choose the same number out of 448 numbers. The choosing is completely independent, every participant is free to choose any half hour chunk they like.

Solving the birthday problem is a basic statical function that requires some contrarian thinking. We're going to solve the likelihood that two people do not pick the same half hour and then work from there. This is called the complement. We'll subtract the probable space (100%) from the number of times people did not pick the same chunk to pack up. That will result in the percentage that two people do pick the same number.

If someone chose one half hour chunk out of 448, that leaves 447 chunks that other people could attend.

X = (447/448)

Y = 1 - X

1 - (447/448) = 0.0022321428571429047

That's a great sign, two people randomly choosing the same time period to move is pretty unlikely.

This is the odds of a unique pair, but when you start saying what are the chances that two of the twelve people pick the same chunk, the odds become more likely.

The number of pairs of chosen chunks will be

66 = (12 * 11)/2

Our chances of 66 unique pairs is

U = (Chance of unique pair)^pairs
or
87.09% = (0.9977678571)^66

The chance that 2 people out of 12 select the same chunk is actually another complement
12.91% = 1 - 87.09%

Although the chance of two people randomly selecting the same half hour to move their stuff is quite low, the chances increase dramatically when we start adding people to the mix. There's almost a 13% chance that two people will choose the same half hour chunk with twelve people involved.
It would only take ~27 people in an office to make it statically likely (>50%) that two people would show up in the same half hour to move.

Two people in one office is probably fine with social distancing but what are the probabilities when we start increasing the number of people in the office at the same time? This is where things get weird.

What are the odds that three people will pick the same half hour chunk to pack up?

We're going to use Poisson approximation. A Poisson distribution can answer the question, how many times will X occur? Checking every triple and calling it X or a "success" if all three picked the same number. The total number of successes is approximately Poisson with mean value:

(12/3)/478^2 = 0.0009628682971

WolframAlpha Equation

Why 478^2? Take the three random people one at a time. The first one picks a time, say May 19th, 1:00pm. The chance that the second person picks the same time is 1/478, and the chance that the third person also picks that same time is 1/478. Multiplying these gives 1/478^2.

You would need to have around ~90 employees before it's statically likely that three people would choose the same time.

WolframAlpha Equation

Probability of all 12 employees picking the same half hour chunk to pack:

1/478^12 = 7.0285419e-33

Conclusion

The odds that two people out of twelve go to the office at the same time are small but still higher than one would expect at 12.91% with twelve in office employees. Those odds increase more and get astronomical when you start adding more people being in the office at the same time. This model could be better, there will be decaying likelihoods, when one person goes into the office, they would be removed from the pool. Also, half hour chunks are not going to fall into a uniform distribution, some hours of the day will be more popular than others. This exercise was just to track down my thought process.

To state it again, it is better to be overly cautious in times like these, using a schedule to make sure multiple people are not in the office at the same time is a great way to practice being socially responsible, especially if you have the luxury to do so. I just wanted to track down my gut level instinct and explain it in probabilistic thinking terms.

Gulp/Bower to Browserify to Webpack. An AngularJS Journey

James Ingold — Wed, 13 May 2020 23:22:33 +0000

A Gulp/Bower post in 2020? I know, but when you've been heads down working in a new industry, getting feedback, building and pivoting to keep up with the changing landscape: things like your build process just don't seem that important. If it ain't broke, don't fix it. The time had come though, to transition a flagship AngularJS app off Gulp/Bower to Webpack.
Some background: In 2014, I had opportunity to select the frontend framework for what at the time was going to be a next generation Electronic Medical Record application. The choices were basically AngularJS, Durandal, Ember, and Backbone. React was a baby, about a year old. AngularJS was the hotness, a few years old and backed by Google. It also sported an intuitive Model-View-Controller framework that I knew developers on the team would be able to pick up (once they got past some AngularJS black magic and naming conventions). It has turned out to be a solid choice and has supported the development efforts well, for over six years. Allowing the team to move fast and keep up with changing stakeholder needs. However, its writing is on the wall and this is the first step to making a smooth transition.

Motivations

To be able to write the same version of Javascript on the frontend and the backend. Lessen context switching.
Staying current with latest Javascript changes, return to cutting edge form. "When you spend all your time on features, the inevitable outcome is that easy tasks become difficult and take longer."
To pave the way for slowly transitioning away from AngularJS
Kaizen culture => everything around you can be improved and deserves to be improved

The Process

I had actually attempted to make this change twice before, going from Gulp => Webpack. However, I had failed both times. This was a large scale change, I had to update the code to use ES Modules in AngularJS and write the Webpack configurations for both production and development. Current web frameworks come pre-rolled with the Webpack configuration (Angular CLI, Vue CLI, etc). You usually don't have to write your own Webpack configuration and even back in the early Vue days, you just had to modify a few bits for your production build process. Writing one from scratch for an already existing app is a tall order. Webpack introduces a new way of thinking with it's entry, output, loader and rules. It's definitely less intuitive than Gulp which is just passing streams around.

So for those first two attempts, I got hung up on Webpack. I spent a lot of time spinning my wheels. I had written a couple Webpack configurations before in greenfield projects and I had modified my fair share but moving an existing Gulp configuration to Webpack just wasn't clicking.

Enter Browserify.

require('modules') in the browser. I had never used Browersify before, I had heard of it but mainly in the context that it was Webpack's younger brother and you should just use Webpack.
Pros:

Dead simple, command line first.
Sticks to the Linux philosophy of do one thing well.

Cons:

You're probably going to want more functionality in a complex application.
Doing everything on command line can be hard for some developers to follow.
The configuration options are not that great, I don't want to put browserify properties into package.json. It just feels wrong to me.

Browserify is Punk-Rock to Webpack’s Top 40 Hits. Learning about Browserify just clicked and I started to devise a plan on getting this app bundled. I really enjoyed learning about Browserify, everything about the project resonated with me. Equipped with some Browersify knowledge, I could now move forward.

Implementation

Here's what I needed to do to move an AngularJS app from Gulp/Bower to Browersify:

Update AngularJS modules to ES modules. I wanted to keep as much of the codebase intact as possible and not harm any developers productivity. We use folder by feature/module structure and using the AngularJS module as the entry point was the best way to do this. This allows us to ESnext our javascript files more incrementally. For Browserify, I used bulk-require and bulkify (Browserify plugins all end in ify which is nice). Here's an example of ES Moduling a standard AngularJS module

Before:
```
 (function () {
     'use strict'
     angular.module('blocks.logger', [])
 })()
```
After:
```
angular.module('blocks.logger', []) // create the module
const bulk = require('bulk-require') 
// bulk require all the files in this folder such as logger.js
bulk(__dirname, ['./**/!(*.module).js']) 
export default angular.module('blocks.logger') // export our module
```

Use app.js as the entry file and use import syntax to include all the modules and dependencies for the application.

Before:

(function () {
  'use strict'

  var app = angular
    .module('app', [
      'common',
      'blocks.logger',
      'blocks.exception'
      ...etc
    ])

After:

// globals - lodash, jquery, etc go here  

import angular from 'angular/index'
// other angularjs depencies go here, ui-router, etc
import ngRoute from 'angular-route'

// application modules
import logger from './blocks/logger/module'
import common from './common/module'
import exception from './blocks/logger/exception'  

var app = angular.module('app', [
    ngRoute,
    'blocks.exception',
    'blocks.logger',
    ...etc

export default app

Move frontend dependencies from Bower into modules
This is pretty simple, just npm install -s the dependencies you're using and import them in app.js.
```
    import $ from jquery  
```

Shim globals

For this app, there was existing code in the pug index file that relied on jQuery being on the window and AngularJS needs to pull in jQuery or it will use JQlite. For this, there is the shim-browersify plugin.

package.json 
  "browser": {
    "angular": "./node_modules/angular/angular.js",
    "jquery": "./node_modules/jquery/dist/jquery.js"
  },
  "browserify": {
    "transform": [
      "browserify-shim"
    ]
  },
  "browserify-shim": {
    "angular": {
      "depends": "jquery:jQuery",
      "exports": "angular"
    }
  }

Browersify build script using tinyify for minification

    browserify -t [ babelify --presets [ @babel/preset-env ] ] -t bulkify public/app/app.js -o public/bundle.js -p [ tinyify --no-flat ]

Browerify dev script - enter watchify. Watches files in bundle for changes and updates only what changed. Creates sourcemaps.

watchify --full-paths -t [ babelify --presets [ @babel/preset-env ] ] -t bulkify public/app/app.js -o public/bundle.js -v -p mapstraction --debug

A compound VSCode launch task to watch for changes automatically and rebundle things.

Here's an example task that runs the watchify npm script which can be used in a VSCode launch:

{
    "version": "2.0.0",
    "tasks": [{
        "label": "build-full",
        "command": "npm run build:dev",
        "type": "shell"
    }]
}

Enter Webpack

Now we've got a nice module bundler pipeline going and a dev workflow that is not intrusive. After a day of work to get the project to this point, I certainly felt like I was #winning. I was not going to get three Webpack strikes.

Injecting Bundles, The Final Frontier:

The last piece of the puzzle is to inject our hashed (cache busting) bundles into a pug file. In the Gulp world, I used gulp-inject which worked great. This is the hangup with Browersify, it fits into a build pipeline while Webpack can be the build pipeline. This was the last piece I needed. I could probably write a plugin to do this but it would feel weird. Plugins in Browersify go off "Transforms". The transform function fires for every file in the current package and returns a transform stream that performs the conversion. Not ideal. There are a multitude of ways to handle this problem, but they all rely on adding more pieces to the puzzle instead of using existing piece. I want to keep the puzzle small.
At this point, it's either change the way our pug file works, use Gulp, or write a hacky solution. Option 1 is not going to work, I don't want to impact other devs and the whole reason we're going through this exercise is to make things better and move away from Gulp.

Here's an example of the Gulp task I was using to build the bundle:

Upgrading an Angular1x app to ES2015 Syntax

  var babelify = require('babelify')
  var browserify = require('browserify')
  var vinylSourceStream = require('vinyl-source-stream')
  var vinylBuffer = require('vinyl-buffer')

/* Compile all script files into one output minified JS file. */
  gulp.task('bundlify', function () {
    var sources = browserify({
      entries: [
        'public/app/app.js'
      ],
      debug: true // Build source maps
    })
    .transform(babelify.configure({
      presets: ['@babel/preset-env']
    }))
    .transform(bulkify)

    return sources.bundle()
    .pipe(vinylSourceStream('main.min.js'))
    .pipe(vinylBuffer())
    .pipe($.sourcemaps.init({
      loadMaps: true // Load the sourcemaps browserify already generated
    }))
    .pipe($.ngAnnotate())
    .pipe($.uglify())
    .pipe($.sourcemaps.write('./', {
      includeContent: true
    }))
    .pipe(gulp.dest('./dist'))
  })
}

We've come so far, won many battles => moving the modules to ES modules, shimming globals, removing bower from the process, getting our app bundled. We're going to need Webpack to win the war though and finally excise Gulp from the project.

Webpack is a vastly configurable static module bundler.

Reasons for moving to Webpack:

I need to inject sources to align with the current build process which uses Gulp. I want to remove Gulp from the process.
I want to bundle styles, I know I could probably do this with Browersify but I didn't get to that point yet.
Configuration based: even though configuring Webpack is more complex than Browersify, I thought the configuration nature would be easier for future developers to comprehend and extend.
It's popular, this one hurts to say as I really bonded with Browersify and their ethos. It fits my style, 100%. However, as an enterprise application, the well-known option has it's benefits.

Webpack Crash Course:

Entry: which module Webpack should use to begin building out its internal dependency graph. Basically where things start, for us it's app.js.

Output: Where bundles go

Loaders: Processes types of files. Two properties:

test: which files types should be transformed (usually regexes are used /.js$/)
use: what loader (processor) to use on those files

Plugins: Used for more functionality than transforms (minification, asset optimization, generate an html file, etc).

Mode: Development, Production, None. Built-in optimizations happen for production mode.

Webpack Conversion

Replace bulk-require and bulkify with Webpack's require.context.
The bulk-require solution felt like a hack while Webpack's require.context is essentially the same functionality natively supported:
After:

angular.module('blocks.logger', []) // create the module
function importAll (r) {
  _.forEach(r.keys(), r)
}

importAll(
  require.context('./', true, /^(?!.*\.module\.js$)^(?!.*\.spec\.js$).*\.js$/)
)
export default angular.module('blocks.logger') // export our module

Get a working Webpack config going to bundle Javascript. Use Webpack's ProvidePlugin to expose globals.

const webpack = require('webpack')
const path = require('path')

module.exports = {
  mode: 'none',
  entry: {
    app: path.join(__dirname, '/public/app/app.js')
  },
  output: {
    path: path.join(__dirname, '/public/'),
    filename: '[name].js'
  },
  devtool: 'eval-source-map',
  module: {
    rules: [
      {
        test: /\.js$/,
        use: {
          loader: 'babel-loader',
          options: {
            presets: ['@babel/preset-env']
          }
        },
        exclude: /node_modules/
      }]
  },
  // Use ProvidePlugin to expose jQuery to the window object, replaces /browersify-shim:
  plugins: [
    new webpack.ProvidePlugin({
      'window.$': 'jquery',
      'window.jQuery': 'jquery',
      $: 'jquery'
    })
  ]
}

Include styles. This project uses sass. In app.js we're going to import our sass files and use the sass-loader (npm install sass-loader -D)

app.js

import '../assets/scss/styles.scss'

webpack.config.js

{
    test: /\.s[ac]ss$/i,
    use: [
      // Creates `style` nodes from JS strings
      'style-loader',
      // Translates CSS into CommonJS
      'css-loader',
      // Compiles Sass to CSS
      'sass-loader'
    ]
}

autoprefixer is something else to look at, it parses css and adds vendor rules.

Development and Production Webpack configurations - Webpack Merge

npm install webpack-merge

webpack.dev.js -> replaces watchify, watch: true will watch the bundle files and rebuild. You can use the --silent option to suppress the output.

webpack.dev.js

const merge = require('webpack-merge')
const common = require('./webpack.config.js')
const path = require('path')

module.exports = merge(common, {
  mode: 'development',
  devtool: 'inline-source-map',
  output: {
    path: path.join(__dirname, '/public/'),
    filename: '[name].js'
  },
  watch: true
  plugins: []
})

For Production:

mode: set this to production
Minification: terser-webpack-plugin and optimize-css-assets-webpack-plugin
Copy Files to Dist Directory: copy-webpack-plugin
Clean Dist Directory: clean-webpack-plugin
Cache-Busting: Use hash in output
Extract CSS into separate bundle to decrease bundles sizes: mini-css-extract-plugin

webpack.prod.js

const webpack = require('webpack')
const merge = require('webpack-merge')
const common = require('./webpack.config.js')
const path = require('path')

const TerserJSPlugin = require('terser-webpack-plugin')
const OptimizeCSSAssetsPlugin = require('optimize-css-assets-webpack-plugin')

const MiniCssExtractPlugin = require('mini-css-extract-plugin')
const CopyPlugin = require('copy-webpack-plugin')
const { CleanWebpackPlugin } = require('clean-webpack-plugin')

module.exports = merge(common, {
  mode: 'production',
  devtool: false,
  output: {
    path: path.resolve(process.cwd(), 'dist'),
    publicPath: '',
    filename: '[name].[hash].js'
  },
  module: {
    rules: [
      {
        test: /\.s[ac]ss$/i,
        use: [
          MiniCssExtractPlugin.loader,
          'css-loader',
          'postcss-loader',
          'sass-loader'
        ]
      }
    ]
  },
  optimization: {
    minimizer: [new TerserJSPlugin(), new OptimizeCSSAssetsPlugin()]
  },
  plugins: [
    new CleanWebpackPlugin(),
    new CopyPlugin([
      { from: 'app/**/*.html', context: 'public' }, // TODO: need to figure out template cache with webpack
    ]),
    new MiniCssExtractPlugin({
      filename: '[name].[hash].css',
      chunkFilename: '[id].css'
    })
  ]
})

Injecting bundles

We're finally to the point we were with Browersify plus we've got our sass files imported now. Injecting the hashed bundles into a pug file. This is where I got stuck for a little bit. The html-webpack-plugin is okay but it mainly focuses on generating a new index file. There are pug plugins but none of them are as seamless as gulp-inject. Basically in the pug file we have marker comments like //- inject:js //- endinject. And the files are injected between those comments.
Webpack has a much dynamic plugin architecture, so I ended up writing my own naive plugin to replace the gulp-inject functionality. It’s basic and doesn’t support SplitChunks at the moment but it gets the job done.
```
const InjectPlugin = require("pug-gulp-inject-webpack-plugin");

new InjectPlugin({
  template: "views/includes/head.jade",
  output: path.join(process.cwd(), "views/includes/head.jade")
});
```

Bundle Size Optimization: Bonus Round

Two useful tools for tracking down bundle size issues:
discify: Browersify plugin that generates graph and stats of your bundle
source-map-explorer: Analyze and debug JavaScript (or Sass or LESS) code bloat through source maps.

Slimming down moment and moment-timezone:
I'm able to get by only shipping the en-us locale with moment which saves some space.

     new webpack.IgnorePlugin(/^\.\/locale$/, /moment$/) // ignore all locales by default, only ship with en-us

moment-timezone ships with a ton of data, to slim it down, you can change the import to only bring in a ten year span of data:

    import momentTz from 'moment-timezone/builds/moment-timezone-with-data-2012-2022'

Webpack Chunk Splitting: More on this in the future but I'm currently using two entry points to generate two separate bundles. It's the basic form of bundle splitting which doesn't really allow deduplication but that's okay in my case for now.

Conclusion

The journey from Gulp to Webpack for this AngularJS application is mostly complete. It took getting Browersify involved to finally be able to make the transition to Webpack for a 2014 AngularJS app. There are still more hills to climb, getting AngularJS's template cache working and better bundle splitting but this is a good start. Now that we can write frontend javascript with ES-whatever, the sky is the limit. Maybe we start transitioning to Svelte? :D

If you read this far, give me a shoutout on Twitter / send any questions or comments to yo[@]jamesingold.com

DEV Community: James Ingold

Restoring MySQL Database from .frm and .ibd Files on MacOS

Restore Process High-Level:

Let's Get Started

Step 1: Create Files to Reconstruct Table Structure

Step 1.1: Use MySQL's command line utilities

Use mysqlfrm to generate table creates

Step 2 - Generate dummy tables creates using .frm files

Step 3 - Create Our Tables

Step 4 - Replace Data

References

Deconstructing an Object Relationship Mapper (ORM) in Typescript

What is an ORM?

Pros

Cons

The Situation

The Vision

General Principles - Connecting your Typescript Classes to the Database

Decorators

Model(identifier: string, alias?: string)

Column(name: string)

Enum(name: string, () => Dictionary)

HasMany(modelName: string, relationKey?: string)

BaseDomainModel

Conclusion

References:

Amazon Cognito MFA with Email Using Lambda Triggers

Part 1: Create Serverless Project for Cognito Lifecycle Hooks

Define Lifecycle Function

Create Lifecycle Function

Verify Lifecycle Function

Deploy the Project

Part 2: Pseudo Backend Code

Login Route: Kick off auth flow and prepare custom challenge

Verify Route: This handles responding to our auth challenge with Cognito which will call our verify lifecycle handler.

Conclusion

References:

Automating Route53 DNS Updates for Blue/Green Deployments (the hard way)

Blue/Green Architecture

Python Script for Updating Route53 Entries

Bonus: Shell script to Pass Domains to Update

The End

References:

Migrating from Sequel Pro to Sequel Ace

Install Sequel Ace

Copy Preferences and Favorites to Sequel Ace

Copy Passwords (optional)

Uninstall Sequel Pro

Wrapping Up

References:

Setting up a Serverless Project with Webpack, Babel, and Knex

Project Setup

Add Dev Dependencies and Webpack:

Adding Source Map Support

Setting up Babel

Adding Knex

Setting up Knex

Setting up Webpack

Setting up Serverless

Running and Knex Issues

Knex Runtime Issues:

Adding Eslint and Prettier

Starter Project

The Ultimate Guide to Learning Neo4j with Netflix's Dark Series

Goals

Neo4j (Better Data, Betters Decisions) Crash Course

Nodes, Labels, Properties, Relationships

Why not just use a traditional relational database?

When to Use

When to not use a Graph Database:

Query Language

Getting Started

Setting up our Dev Environment

Data

The Plan => Cypher Language and Importing Data

Basic Cypher Quick Hits

Questions and Findings

Conclusion

Further Reading / References / Tools

Replacing AngularJS $templateCache with Webpack