greenlock-store-sequelize | A Root project

A database-driven Greenlock storage plugin with wildcard support.

Features

• Many Supported SQL Databases
  • [x] PostgreSQL (best)
  • [x] SQLite3 (easiest)
  • [x] Microsoft SQL Server (mssql)
  • [x] MySQL, MariaDB
• Works on all platforms
  • [x] Mac, Linux, VPS
  • [x] AWS, Heroku, Akkeris, Docker
  • [x] 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 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 })
  ...
});

The general format of a DATABASE_URL is something like this:

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

For example:

postgres://aj:[email protected]: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.

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.

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 })
  ...
});

• 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);