mysql-admin-sql-gen

MySQL Administrative SQL Generator Library

A library for generating MySQL administrative SQL statements. Similar to Squel but for CREATE DATABASE, CREATE ROLE, CREATE USER, GRANT, and other administrative statements.

(If anyone knows of a JavaScript/Node.js library that performs the same or nearly the same functionality as this library, please let me know. I searched for an hour or two looking for a similar library before I decided to create this library.)

Note: This library does NOT provide the ability to connect to a MySQL database and therefore also does NOT execute generated SQL. There are several packages that can be used for executing the generated SQL: bookshelf, knex, mysql, mysql2, sequelize, etc.

Why?

Almost all, if not all, ORM's expect that the role(s), user(s), database(s), et al already exist before using the ORM to generate tables and other database objects. Some will provide you some documentation on how to execute raw SQL which can be used to execute SQL statements that create role(s), user(s), database(s), et al but they don't have an API to generate these types of statements. That's where this library comes into play.

For many projects, these administrative SQL statements are not configuration-driven, and hard coding them is perfectly valid. But for those projects that are configuration-driven, an API provides some benefit. For example, this library will properly escape special characters for the arguments that need it.

This library works in the browser too.

Have online documentation instructing the user to execute some administrative SQL? Use this library to provide data-driven documentation.

Examples

// database-config.js
const dbConfig = {
  database: 'example',
  users: {
    readonly: { name: 'readonly', host: 'localhost' },
  },
}
export default dbConfig

Creating a Database

import { createDatabase } from 'mysql-admin-sql-gen'

import dbConfig from './database-config'

const sql = createDatabase(dbConfig.database, { ifNotExists: true })
// 'CREATE DATABASE IF NOT EXISTS `example`;'

Creating a User

import { createUser } from 'mysql-admin-sql-gen'

import dbConfig from './database-config'

const sql = createUser(dbConfig.users.readonly, { ifNotExists: true })
// "CREATE USER IF NOT EXISTS 'readonly'@'localhost'"

Grant Privileges

import { grant } from 'mysql-admin-sql-gen'

import dbConfig from './database-config'

const sql = grant({
  privileges: ['EXECUTE', 'SELECT'],
  on: {
    level: { database: dbConfig.database },
  },
  to: dbConfig.users.readonly,
})
// "GRANT EXECUTE, SELECT ON `example`.* TO 'readonly'@'localhost'"

These examples are just the tip of the iceberg. See the unit tests for a complete set of usage examples.

Maintainers

• Danny Hurlburt

License

ISC