ember-cli-deploy-sql

This plugin, lovingly cribbed from ember-cli-deploy-redis, uploads the contents of a file, presumably index.html, to a specified database table. PostgreSQL, MySQL/MariaDB, Oracle, and other relational database management systems (RDBMS) are supported (courtesy of Knex.js).

More often than not this plugin will be used in conjunction with the lightning method of deployment where the Ember.js application assets will be served from S3 and the index.html file will be served alongside your API from a key-value store of some kind; in this case, a database table. However, it can be used to upload the contents of any file.

This plugin is a candidate to replace both ember-cli-deploy-mysql and ember-cli-deploy-postgres.

• What is an Ember CLI Deploy plugin?
• Why would I use this instead of ember-cli-deploy-redis?
• Quick Start
• Pipeline Hooks Implemented
• Configuration Options
• Configuration Prerequisites
• Activation
• What if my MySQL server isn't publicly accessible?
• Migrating from ember-cli-deploy-mysql
• Migrating from ember-cli-deploy-postgres
• Tests

What is an Ember CLI Deploy plugin?

A plugin is an addon that can be executed as a part of the Ember CLI Deploy pipeline. A plugin will implement one or more of the Ember CLI Deploy's pipeline hooks.

For more information on what plugins are and how they work, please refer to the Plugin Documentation.

Why would I use this instead of ember-cli-deploy-redis?

That's a great question. Redis is a more natural fit for this kind of problem and you can do neat things like serve directly from NGINX. Databases, on the other hand, aren't traditionally set up well for key-value storage and retrieval, and it ends up being a somewhat clumsy solution.

In our case, we were only using Redis for this particular function, so it seemed overkill to be running the service (and maintaining a connection pool to it in our Ruby application). Also, our API responses (including the Ember.js index) are already being cached (thanks to a reverse proxy), so talk about redundant layers! It makes more sense for us, for now, to serve the index from a database table and let our reverse proxy cache it. Perhaps your situation is similar?

Quick Start

To get up and running quickly, do the following:

• Ensure ember-cli-deploy-build is installed and configured.

• Install this plugin:

  $ ember install ember-cli-deploy-sql

• Install the appropriate driver:

  $ yarn add --dev <your-database-type>

  Or if you're still using npm:

  $ npm install --save-dev <your-database-type>

• Place the following configuration into config/deploy.js:

  ENV.sql = {
    client: '<your-database-type>',
    connection: '<your-connection-string>'
  }

• Run the pipeline:

  $ ember deploy <environment>

Pipeline Hooks Implemented

For detailed information on what plugin hooks are and how they work, please refer to the Plugin Documentation.

• activate
• didDeploy
• fetchInitialRevisions
• fetchRevisions
• setup
• upload
• willActivate

Configuration Options

For detailed information on how configuration of plugins works, please refer to the Plugin Documentation.

client

connection

migrations

Default: { tableName: 'ember_cli_deploy_migrations' }

sqlOptions

These four options are assembled and passed to Knex.js. Knex is used as a query builder and database abstraction layer (DAL). Please see its documentation for more information on these options. N.B.:

• connection can be either a string or an object.
• If a tunnel is present (see below), its port (and host localhost unless otherwise specified) will be automatically added to the connection object.
• sqlOptions is an optional object that may include any additional top-level configurables to pass to Knex, such as searchPath, debug, pool, etc.

filePattern

A file matching this pattern will be uploaded to the database table.

Default: 'index.html'

tableName

The name of the database table in which to store the revision keys and file contents. By default this option will use the project.name() property from the deployment context, snake-cased and suffixed with _bootstrap.

The table is created in your database automatically on the initial deploy, so your database user will need CREATE TABLE privileges—at least temporarily!

The table looks something like this (e.g. in MySQL):

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
key	varchar(255)	NO	UNI	NULL
value	text	NO		NULL
gitsha	binary(20)	YES		NULL
deployer	varchar(255)	YES		NULL
description	varchar(255)	YES		NULL
is_active	tinyint(1)	NO		0
created_at	timestamp	NO		CURRENT_TIMESTAMP

Default: ${projectName}_bootstrap

N.B.: gitsha, deployer, and description are reserved for future use.

allowOverwrite

A flag to specify whether the revision should be overwritten if it already exists in the database table.

Default: false

maxRecentUploads

The maximum number of recent revisions to keep in the MySQL table.

Default: 10

Configuration Prerequisites

The following properties are expected to be present on the deployment context object:

• distDir (unless a distDir is given in the config; provided by ember-cli-deploy-build)
• project.name() (unless a tableName is given in the config; provided by ember-cli-deploy)
• One of:
  • revisionData.revisionKey (provided by ember-cli-deploy-revision-data)
  • commandOptions.revisionKey (provided by ember-cli-deploy)
• deployEnvironment (provided by ember-cli-deploy)

The following properties are used if present on the deployment context object:

• tunnel.srcPort (provided by ember-cli-deploy-ssh-tunnel)

Activation

As well as uploading a file to the database table, ember-cli-deploy-sql has the ability to mark any revision of a deployed file as currently active.

The application could be configured to return any existing revision of the index.html file as requested by a query parameter. However, the revision marked as the currently active revision would be returned if no query parameter is present. For more detailed information on this method of deployment please refer to the ember-cli-deploy-lightning-pack README.

How do I activate a revision?

A user can activate a revision by either:

• Passing an additional command line argument to the deploy command:

  $ ember deploy <environment> --activate

• Running the deploy:activate command:

  $ ember deploy:activate <environment> --revision=<revision-key>

• Setting the activateOnDeploy flag in config/deploy.js

  ENV.pipeline = {
    activateOnDeploy: true
  }

  This has the same effect as passing --activate on every invocation of ember deploy.

What does activation do?

When ember-cli-deploy-sql uploads a file, it uploads it to the table defined by the tableName config property (which may be derived from the project name), with a key defined by the revisionKey config property (which may be derived from the file contents). So if there have been three revisons deployed (but not activated), the table might look something like this:

$ mysql -u root foo

MariaDB [foo]> select `key`, left(`value`, 10), `is_active` from bar_bootstrap;
+----------------------------------+-------------------+-----------+
| key                              | left(`value`, 10) | is_active |
+----------------------------------+-------------------+-----------+
| cc9d9af44ad70f4a6732c1c13deb246e | <!DOCTYPE         | 0         |
| 071be39412920947613c00d680b8e9c0 | <!DOCTYPE         | 0         |
| d56d56274aac91e229fa69f34f4cf81d | <!DOCTYPE         | 0         |
+----------------------------------+-------------------+-----------+

Activating a revison would update the corresponding entry in the database table:

$ ember deploy:activate production --revision=cc9d9af44ad70f4a6732c1c13deb246e
✔ Activated revision `cc9d9af44ad70f4a6732c1c13deb246e'
$ mysql -u root foo

MariaDB [foo]> select `key`, left(`value`, 10), `is_active` from bar_bootstrap;
+----------------------------------+-------------------+-----------+
| key                              | left(`value`, 10) | is_active |
+----------------------------------+-------------------+-----------+
| cc9d9af44ad70f4a6732c1c13deb246e | <!DOCTYPE         | 1         |
| 071be39412920947613c00d680b8e9c0 | <!DOCTYPE         | 0         |
| d56d56274aac91e229fa69f34f4cf81d | <!DOCTYPE         | 0         |
+----------------------------------+-------------------+-----------+

When does activation occur?

Activation occurs during the activate hook of the pipeline. By default, activation is turned off and must be explicitly enabled by one of the three methods described above.

What if my MySQL server isn't publicly accessible?

Not to worry! Just install the handy-dandy ember-cli-deploy-ssh-tunnel plugin:

ember install ember-cli-deploy-ssh-tunnel

And set up your config/deploy.js similar to the following:

ENV = {
  sql: {
    client: '<your-database-type>',
    connection: {
      // everything except host and port!
    }
  },
  'ssh-tunnel': {
    username: 'your-ssh-username',
    host: 'remote-mysql-host'
  },
  pipeline: {
    runOrder: {
      sql: {
        after: 'ssh-tunnel'
      }
    }
  }
}

What if my MySQL server is only accessible from my remote server?

Sometimes you need to SSH into a server (a "bastion" host) and then run mysql or what have you from there. This is really common if you're using RDS on AWS, for instance. Ember CLI Deploy has got you covered there, too: just set your SSH tunnel host to the bastion server and tell the tunnel to use your database server as the destination host, like so:

ENV = {
  sql: ..,
  'ssh-tunnel': {
    username: 'your-ssh-username',
    host: 'remote-mysql-client',
    dstHost: 'remote-mysql-server'
  },
  pipeline: ..
}

Migrating from ember-cli-deploy-mysql

1. Remove ember-cli-deploy-mysql from your project:

   $ yarn remove ember-cli-deploy-mysql

   Or if you're still using npm:

   $ npm uninstall --save-dev ember-cli-deploy-mysql

2. Add ember-cli-deploy-sql to your project:

   $ ember install ember-cli-deploy-sql

3. Add a MySQL/MariaDB driver to your project:

   $ yarn add --dev mysql

   Or if you're still using npm:

   $ npm install --save-dev mysql

4. Update config/deploy.js to use the sql key (instead of mysql), and adapt your connection options to Knex syntax. For example:

   Before:

   mysql: {
     user: 'jack',
     password: process.env.MYSQL_PASSWORD,
     database: 'momotaro'
   }

   After:

   sql: {
     client: 'mysql',
     connection: {
       user: 'jack',
       password: process.env.MYSQL_PASSWORD,
       database: 'momotaro'
     }
   }

5. Update your backend to serve the revision WHERE `is_active` = 1 (instead of the revision pointed to by the 'current' revision).

6. Run ember deploy:list <environment> to migrate the database and confirm that the correct revision is (still) active.

Migrating from ember-cli-deploy-postgres

1. Remove ember-cli-deploy-postgres from your project:

   $ yarn remove ember-cli-deploy-postgres

   Or if you're still using npm:

   $ npm uninstall --save-dev ember-cli-deploy-postgres

2. Add ember-cli-deploy-sql to your project:

   $ ember install ember-cli-deploy-sql

3. Add a PostgreSQL driver to your project:

   $ yarn add --dev pg

   Or if you're still using npm:

   $ npm install --save-dev pg

4. Update config/deploy.js to use the sql key (instead of postgres), and adapt your connection options to Knex syntax. For example:

   Before:

   postgres: {
     user: 'jack',
     password: process.env.POSTGRESQL_PASSWORD,
     database: 'momotaro'
   }

   After:

   sql: {
     client: 'pg',
     connection: {
       user: 'jack',
       password: process.env.POSTGRESQL_PASSWORD,
       database: 'momotaro'
     }
   }

5. Update your backend to serve the revision WHERE "is_active" = TRUE (instead of the revision pointed to by the 'current' revision).

6. Run ember deploy:list <environment> to migrate the database and confirm that the correct revision is (still) active.

Tests

• yarn install && yarn test

Why ember test doesn't work

Since this is a node-only Ember CLI addon, we use mocha for testing and this package does not include many files and devDependencies which are part of Ember CLI's typical ember test processes.