MySQL Engine for pixl-server-storage

This package provides a MySQL storage engine for pixl-server-storage.

Installation

npm install pixl-server-storage-mysql-engine

Configuration

In your application's config file (usually config.json):

{
  "storage": {
    "engine": "MySQL",
    "list_page_size": 50,
    "concurrency": 10,
    
    "MySQL": {
      "host": "localhost",
      "port": 3306,
      "user": "username",
      "password": "password",
      "database": "pixl_storage",
      "table_prefix": "pixl_",
      "create_schema": true,
      "key_template": "",
      "md5_keys": false,
      "binary": false,
      
      "pool_opts": {
        "connectionLimit": 10,
        "waitForConnections": true
      },
      
      "backup_folder": "backups",
      "backup_retention": 5,
      "backup_compress": true
    }
  }
}

Integration with pixl-server

This package automatically registers itself with pixl-server-storage when loaded, so you don't need to explicitly register the engine.

const Server = require('pixl-server');

// Create server
let server = new Server({
  __name: 'MyServer',
  __version: '1.0.0',
  
  config: {
    "configFile": "conf/config.json"
  },
  
  components: [
    require('pixl-server-storage')
  ]
});

// The MySQL engine is auto-registered, so just start your server
server.startup(function() {
  // Get storage component
  let storage = server.Storage;
  
  // Use storage system
  storage.put('users/jsmith', { 
    name: 'John Smith', 
    email: '[email protected]' 
  }, function(err) {
    if (err) server.logger.error('Error storing record', err);
    else server.logger.debug(9, 'Record saved successfully');
  });
});

If for some reason the auto-registration doesn't work (e.g., if you need to register a custom version of the engine), you can still register it manually:

const MySQLEngine = require('pixl-server-storage-mysql-engine');
storage.addEngine('MySQL', MySQLEngine);

Storage Schema

When create_schema is set to true, the engine will create tables as needed. By default, this will create tables with the following structure:

CREATE TABLE IF NOT EXISTS `table_name` (
  `id` VARCHAR(255) NOT NULL,
  `modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `value` LONGBLOB NOT NULL,
  PRIMARY KEY (`id`),
  INDEX (`modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

To customize table creation, you can extend the MySQL engine class and override the _createSchema method:

const Class = require('pixl-class');
const MySQLEngine = require('pixl-server-storage-mysql-engine');

const CustomMySQLEngine = Class.create({
  __name: 'CustomMySQL',
  __parent: MySQLEngine,
  
  _createSchema: async function() {
    // Create custom schema here
    var lists = ['users', 'sessions', 'logs'];
    
    for (var idx = 0, len = lists.length; idx < len; idx++) {
      var list = lists[idx];
      var tableName = this._getTableName(list);
      
      try {
        await this.pool.execute(`
          CREATE TABLE IF NOT EXISTS ${tableName} (
            id VARCHAR(255) NOT NULL,
            modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            value LONGBLOB NOT NULL,
            content_type VARCHAR(64) NULL,
            size INT UNSIGNED NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            INDEX (modified),
            INDEX (size)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        `);
        
        this.logDebug(9, "Created or verified table: " + tableName);
      }
      catch (err) {
        this.logError('schema', "Failed to create table " + tableName + ": " + err);
        throw err;
      }
    }
  }
});

// Then use CustomMySQLEngine instead of MySQLEngine
storage.addEngine('MySQL', CustomMySQLEngine);

License

ISC