Creating Custom Extensions in MySQL Shell with JavaScript and Python

Since MySQL Shell 8.0.17 it supports custom extensions written in JavaScript or Python, allowing DBAs to package frequently used SQL scripts into reusable tools and even build a DevOps platform for the shell.

Two extension mechanisms are available: report , which defines a query‑like function that can be invoked with the built‑in \show or \watch commands, and plugin , which can expose arbitrary functions.

Custom scripts should be placed under ~/.mysqlsh/plugin or ~/.mysqlsh/init.d . Each plugin directory must contain an init.py or init.js file that initializes the extension. A typical layout looks like:

~/.mysqlsh/plugin
└── ext
    └── table
        └── init.py

The following init.py demonstrates how to register a report and a plugin that list tables without a primary key, and how to add a function that adds a primary key to a table:

# init.py
# -------
# Demonstration of registering a report and a plugin

def report_table_without_pk(session):
    query = '''SELECT tables.table_schema , tables.table_name
                FROM information_schema.tables
                LEFT JOIN (
                SELECT table_schema , table_name
                FROM information_schema.statistics
                GROUP BY table_schema, table_name, index_name HAVING
                    SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks
                ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name
                WHERE puks.table_name is null
                AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";'''
    result = session.run_sql(query)
    report = []
    if result.has_data():
        report = [result.get_column_names()]
        for row in result.fetch_all():
            report.append(list(row))
    # Register as a report, must return a dict
    return {"report": report}

# Plugin version of the same logic

def plugin_table_without_pk(session):
    query = '''SELECT tables.table_schema , tables.table_name
                FROM information_schema.tables
                LEFT JOIN (
                SELECT table_schema , table_name
                FROM information_schema.statistics
                GROUP BY table_schema, table_name, index_name HAVING
                    SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks
                ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name
                WHERE puks.table_name is null
                AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";'''
    result = session.run_sql(query)
    shell.dump_rows(result)
    return

# Function to add a primary key

def _add_pk(table, columns, session=None):
    query = 'ALTER TABLE %s ADD PRIMARY KEY (%s)' % (table, columns)
    if session is None:
        session = shell.get_session()
        if session is None:
            print("No session specified. Either pass a session object to this function or connect the shell to a database")
            return
    result = session.run_sql(query)

# Register the report
shell.register_report("table_without_pk", "list", report_table_without_pk, {"brief": "Lists the table without primary key."})

# Create or reuse a global extension object named 'ext'
if 'ext' in globals():
    global_obj = ext
else:
    global_obj = shell.create_extension_object()
    shell.register_global("ext", global_obj, {"brief": "MySQL Shell extension plugins."})

# Add a sub‑object for table operations
plugin_obj = shell.create_extension_object()
shell.add_extension_object_member(global_obj, "table", plugin_obj, {"brief": "Utility object for table operations."})

# Register the add_pk function as a plugin method
shell.add_extension_object_member(plugin_obj, "add_pk", _add_pk, {
    "brief": "Add a primary key to the table",
    "parameters": [
        {"name": "table", "type": "string", "required": true, "brief": "table name."},
        {"name": "columns", "type": "string", "required": true, "brief": "column name."},
        {"name": "session", "type": "object", "class": "Session", "required": false, "brief": "The session to be used on the operation."}
    ]
})

# Register the plugin version of the report
shell.add_extension_object_member(plugin_obj, "get_without_pk", plugin_table_without_pk, {"brief": "Lists the table without primary key."})

After logging into mysqlsh , the shell automatically discovers and initializes plugins. Using the --log-level option records detailed debugging information in ~/.mysqlsh/mysqlsh.log . If loading fails, consult the log.

To run a report, use \show or \watch followed by the report name. Plugin functions are called directly like any other shell function.

For more details and advanced use‑cases, refer to the official MySQL Shell documentation and the example repository on GitHub.