Other database flavours support

[LynnAU]

While running the bot is a breeze for the most part, it makes running it under automated deployment environments rather bad. Since the bot uses SQlite as the default db flavour, redeploying a config change or updated bot would very much wipe out the existing db therefore losing all log capability.

Now this can be overcome if the db file was stored on a network host that the deployment environment can connect to, that would fix the issue but another more preferable fix would be to support other flavours of databases like postgreSQL, MySQL, etc. Knex supports them directly so it's just a matter of changing a few lines in src/config.js to point to the other database and in db\migrations\20171223203915_create_tables.js line 21 to a different type (from table.mediumtext() to table.text()) . This is because textType is a MySQL datatype preference so it breaks on postgres for example, see here.

So something will appear in src/config.js along the lines of this:

// Default knex config
if (! finalConfig['knex']) {
  finalConfig['knex'] = {
    client: finalConfig.dbType,
    connection: {
      host: finalConfig.dbHost,
      user: finalConfig.dbUser,
      password: finalConfig.dbPass,
      database: finalConfig.dbName
    },
    useNullAsDefault: true
  };
}

And in defaultConfig of the same file or something else to default to SQLite instead:

const defaultConfig = {
  "token": null,
  "mailGuildId": null,
  "mainGuildId": null,
  "logChannelId": null,
  ...

  "dbDir": path.join(__dirname, '..', 'db'),
  "knex": null,

  "logDir": path.join(__dirname, '..', 'logs'),

  dbType: 'pg',
  dbHost: 'localhost',
  dbUser: 'postgres',
  dbPass: 'postgres',
  dbName: 'modmail',
};

I'll be using a patched version I made in a fork here https://github.com/LynnAU/modmailbot/commit/3e75c614a8036e8acb7904ce0984d73a259be52b

[Dragory]

There is actually support for this already ("knex" in config.json to provide custom knex options), but it's undocumented since I don't want to test on a bunch of databases every time. I use it with MariaDB myself. The only issue really is the incompatible data type in the migration you mentioned.

[LynnAU]

Ah yep, I see that now. Up to you with what you want to do whether or not to close this or document the feature/patch in better support...

[smooreace]

Sorry to bring this back from the dead, but I would like to know how to make use of my already established MySQL database thats already running on my web hosting box.

[Dragory]

@smooreace Not sure if you're still looking to use this, but you can use the knex config option to provide custom Knex.js options. You can find Knex's documentation here: https://knexjs.org/#Installation-client

Note that I currently won't provide support for using this option, since it's largely untested with databases other than SQLite (the default) and MariaDB, and is primarily offered as an advanced option for those who really need it.

[smooreace]

Thanks for the response, but with the bot not working at all, the need for specific database utilization is not a big concern any longer.

[ashortdev]

You mention that this is tested with MariaDB, could you provide an example of the configuration required to connect to a MariaDB instance?

[smooreace]

Maria is no different than SQLSent via the Samsung Galaxy S8+, an AT&T 5G Evolution capable smartphone -------- Original message --------From: ashortdev notifications@github.com Date: 9/7/19 7:15 PM (GMT-06:00) To: Dragory/modmailbot modmailbot@noreply.github.com Cc: smooreace smooreace@gmail.com, Mention mention@noreply.github.com Subject: Re: [Dragory/modmailbot] Other database flavours support (#206) You mention that this is tested with MariaDB, could you provide an example of the configuration required to connect to a MariaDB instance?

—You are receiving this because you were mentioned.Reply to this email directly, view it on GitHub, or mute the thread. [ { "@context": "http://schema.org", "@type": "EmailMessage", "potentialAction": { "@type": "ViewAction", "target": "https://github.com/Dragory/modmailbot/issues/206?email_source=notifications\u0026email_token=AIPE4QYH5V6AWNJXLIEGPK3QIQ72RA5CNFSM4GL65J52YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD6FEYFQ#issuecomment-529157142", "url": "https://github.com/Dragory/modmailbot/issues/206?email_source=notifications\u0026email_token=AIPE4QYH5V6AWNJXLIEGPK3QIQ72RA5CNFSM4GL65J52YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD6FEYFQ#issuecomment-529157142", "name": "View Issue" }, "description": "View this Issue on GitHub", "publisher": { "@type": "Organization", "name": "GitHub", "url": "https://github.com" } } ]

[sporkwitch]

More specifically, mariadb is designed to be a drop-in replacement for MySQL, so should support all the same features and commands, and connecting would also be the same. There can occasionally be some deviations on more advanced functionality, but nothing you're going to encounter in most appplications. v / r Robert Klebes Mobile: 571-492-8639 https://keybase.io/sporkwitch https://www.linkedin.com/in/klebes

On September 7, 2019 9:30:40 PM EDT, smooreace notifications@github.com wrote:

Maria is no different than SQLSent via the Samsung Galaxy S8+, an AT&T 5G Evolution capable smartphone> -------- Original message --------From: ashortdev notifications@github.com Date: 9/7/19 7:15 PM (GMT-06:00) To: Dragory/modmailbot modmailbot@noreply.github.com Cc: smooreace smooreace@gmail.com, Mention mention@noreply.github.com Subject: Re: [Dragory/modmailbot] Other database flavours support (#206) You mention that this is tested with MariaDB, could you provide an example of the configuration required to connect to a MariaDB instance?>

—You are receiving this because you were mentioned.Reply to this email directly, view it on GitHub, or mute the thread.> [> {> "@context": "http://schema.org",> "@type": "EmailMessage",> "potentialAction": {> "@type": "ViewAction",> "target": "https://github.com/Dragory/modmailbot/issues/206?email_source=notifications\u0026email_token=AIPE4QYH5V6AWNJXLIEGPK3QIQ72RA5CNFSM4GL65J52YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD6FEYFQ#issuecomment-529157142",> "url": "https://github.com/Dragory/modmailbot/issues/206?email_source=notifications\u0026email_token=AIPE4QYH5V6AWNJXLIEGPK3QIQ72RA5CNFSM4GL65J52YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD6FEYFQ#issuecomment-529157142",> "name": "View Issue"> },> "description": "View this Issue on GitHub",> "publisher": {> "@type": "Organization",> "name": "GitHub",> "url": "https://github.com"> }> }> ]>

-- > You are receiving this because you are subscribed to this thread.> Reply to this email directly or view it on GitHub:> https://github.com/Dragory/modmailbot/issues/206#issuecomment-529160521

[ashortdev]

Let me clarify - What is the syntax required for a MySQL connection within the config?

I see an example on the linked knex documentation lists: connection: { host : '127.0.0.1', user : 'your_database_user', password : 'your_database_password', database : 'myapp_test' }

You mentioned the bot is tested on MariaDB, so I'm looking for an example of what a database-linked config file should look like - as the linked knex documentation isn't very actionable. What key names should be used to assign connection parameters?

[Dragory]

Here's an example of a config that uses MariaDB/MySQL:

{
  "knex": {
    "client": "mysql2",
    "connection": {
      "host": "127.0.0.1",
      "user": "modmailbot",
      "password": "password here",
      "database": "modmailbot",
      "charset": "utf8mb4",
      "timezone": "UTC",
      "supportBigNumbers": true,
      "bigNumberStrings": true,
      "dateStrings": true
    },
    "pool": { "min": 0, "max": 10 },
    "acquireConnectionTimeout": 30000
  }
}

You'll also need to run npm i --no-save mysql2 (and re-run it any time you do npm ci)

[mesub7]

The bot now supports MySQL (as well as continuing to support SQLite) in the latest version. It's currently a pre-release which you can get here.