greenlock-store-sequelize.js/README.md

greenlock-store-sequelize | A Root project

A database-driven Greenlock storage plugin with wildcard support.

Features

• Many Supported SQL Databases
  • PostgreSQL (best)
  • SQLite3 (easiest)
  • Microsoft SQL Server (mssql)
  • MySQL, MariaDB
• Works on all platforms
  • Mac, Linux, VPS
  • AWS, Heroku, Akkeris, Docker
  • Windows

Usage

To use, provide this Greenlock storage plugin as the store option when you invoke create:

Greenlock.create({
  store: require('greenlock-store-sequelize')
  ...
});

Configuration

SQLite3 (default)

SQLite3 is the default database, however, since it has a large number of dependencies and may require a native module to be built, you must explicitly install sqlite3:

npm install --save sqlite3

The default db file will be written wherever Greenlock's configDir is set to, which is probably ~/acme or ~/letsencrypt.

~/acme/db.sqlite3

If you wish to set special options you may do so by passing a pre-configured Sequelize instance:

var Sequelize = require('sequelize');
var db = new Sequelize({ dialect: 'sqlite', storage: '/Users/me/acme/db.sqlite3' });

Greenlock.create({
  store: require('greenlock-store-sequelize').create({ db: db })
  ...
});

PostgreSQL, SQL Server, and lesser databases...

The general format of a DATABASE_URL is something like this:

schema://user:pass@server:port/service?option=foo

For example:

postgres://aj:secret123@127.0.0.1:5432/greenlock

For each database the exact format may be slightly different:

• postgres://user:pass@hostname:port/database?option=foo
• sqlserver://user:pass@datasource:port/instance/catalog?database=dbname (mssql)
• mysql://user:pass@hostname:port/database?option=foo
• mariadb://user:pass@hostname:port/database?option=foo

There's also a way to specify objects instead of using the standard connection strings.

See the next section for more information.

Database URLs / Connection Strings

You may use database URLs (also known as 'connection strings') to initialize sequelize:

var dbUrl = 'postgres://user:pass@hostname:port/database';

Greenlock.create({
  store: require('greenlock-store-sequelize').create({ storeDatabaseUrl: dbUrl })
  ...
});

If you need to use custom options, just instantiate sequelize directly:

var Sequelize = require('sequelize');
var db = new Sequelize('postgres://user:pass@hostname:port/database');

Greenlock.create({
  store: require('greenlock-store-sequelize').create({ db: db })
  ...
});

See the Sequelize Getting Started docs for more info on database options for sequelize.

Environment variables (AWS, Docker, Heroku, Akkeris)

If your database connection string is in an environment variable, you would use the usual standard for your platform.

For example, if you're using Heroku, Akkeris, or Docker you're database connection string is probably DATABASE_URL, so you'd do something like this:

var Sequelize = require('sequelize');
var databaseUrl = process.env['DATABASE_URL'];
var db = new Sequelize(databaseUrl);

Greenlock.create({
  store: require('greenlock-store-sequelize').create({ db: db })
  ...
});

Table Prefixes

The default table names are as follows:

• Keypair
• Domain
• Certificate
• Chain

If you'd like to add a table name prefix or define a specific schema within the database (PostgreSQL, SQL Server), you can do so like this:

var Sequelize = require('sequelize');
var databaseUrl = process.env['DATABASE_URL'];
var db = new Sequelize(databaseUrl, {
    hooks: {
        beforeDefine: function (columns, model) {
          model.tableName = 'MyPrefix' + model.name.plural;
          //model.schema = 'public';
        }
    }
});

Greenlock.create({
  store: require('greenlock-store-sequelize').create({ db: db })
  ...
});

Table Structure

This is the table structure that's created.

CREATE TABLE `Keypairs` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `xid` VARCHAR(255) UNIQUE,
  `content` TEXT,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL);

CREATE TABLE `Domains` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `subject` VARCHAR(255) UNIQUE,
  `altnames` TEXT,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL);

CREATE TABLE `Certificates` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `subject` VARCHAR(255) UNIQUE,
  `cert` TEXT,
  `issuedAt` DATETIME,
  `expiresAt` DATETIME,
  `altnames` TEXT,
  `chain` TEXT,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL);

CREATE TABLE `Chains` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `xid` VARCHAR(255) UNIQUE,
  `content` TEXT,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  `CertificateId` INTEGER REFERENCES
  `Certificates` (`id`) ON DELETE SET NULL ON UPDATE CASCADE);