This article covers:

1. How to set up a connection to a MySQL database
2. How to add datasets
3. How to do connection overrides on a MySQL connection
4. The supported MySQL data types and the respective Luzmo data type they are mapped to

1. How to setup a MySQL connection

To make a connection to a MySQL database, navigate to the Connections page, select New Connection, then select Mysql from the New Connection modal:

You will be prompted to provide the following connection details to establish a connection to your MySQL database:

• Host: Specify the hostname or IP address of your MySQL database.
• Port: Specify the port number on which your MySQL database is listening.
• Database: Enter the name of your MySQL database.
• Username: Provide the username associated with your MySQL database.
• Password: Enter the corresponding password for the specified MySQL user.

Please refer to the examples in our developer documentation to find out how to create a connection to MySQL via our API.

Notes:

1. Luzmo only fires SELECT queries to your data source via this user, we thus recommend to only connect using a read-only MySQL user.
2. To ensure secure access to your MySQL database, it is recommended to whitelist Luzmo's range of IP addresses. You should whitelist Luzmo's range of IP addresses as described here.

2. How to add datasets

Once you have connected your MySQL you can add datasets as explained here.

1. You can select one or multiple datasets as available in your MySQL and link them in Luzmo to ensure they can be used together in a dashboard.
2. You can also add SQL datasets by switching to the SQL tab in the dataset creation modal. While creating or editing a SQL dataset, you can parameterize anything within the query by specifying {{metadata.< parameter name >|< default value >}}. Find out more about parameterized SQL Datasets.

Also make sure to check out this article on Preparing your data for analytics.

To find out how to add datasets using our API, please refer to the examples in our developer documentation.

3. MySQL Connection Overrides

When generating an Authorization token to grant a user acces to your embedded dashboards it is possible to override the data source properties in the authorization request to dynamically use different properties. Find out more about connection overrides.

The fields available for overriding a MySQL connection are as follows:

• Connection ID: The ID of the Connection to be overridden. Retrieve the ID to specify as detailed here.
• host: The new database host to connect to. The database must be of the same type as the originally configured database.
• port:The new port to connect to.
• user: The new user to use when connecting.
• password: The new password to use when connecting.
• database:The new schema to retrieve data from. Note that to MySQL the concepts database and schema are identical. So when overriding a database / schema for MySQL, make sure to pass the same value for both schema and database.
• schema: The new schema to retrieve data from. Note that to MySQL the concepts database and schema are identical. So when overriding a database / schema for MySQL, make sure to pass the same value for both schema and database.
• table:The new table to retrieve data from.
• datasets: List of dataset-level overrides. Useful if you want to override only a single dataset in your dashboard or if you have a separate table per client. The SQL query of the dataset can also be overridden if it's a SQL dataset within Luzmo.
  • schema: the new schema to connect to
  • table: The new MySQL table to query.
  • sql: The new SQL query to run (only for SQL datasets). Alternatively, you could also use parameterized SQL Datasets.

Our developer documentation has more info about connection overrides and examples of MySQL connection overrides.

Example code demonstrating how to override a MySQL connection:

import Luzmo from '@luzmo/nodejs-sdk';
const client = new Luzmo({
  api_key: '<your Luzmo API key>',
  api_token: '<your Luzmo API token>',
  host: 'https://api.luzmo.com:443'
});


const response = await client.create('authorization',
  {
    type: "embed",
    username: "< A unique and immutable identifier for your user >",
    name: "< user name >",
    email: "< user email >",
    suborganization: "< a suborganization name >",
    access: {
      collections: [
        
        {
          id: "<collection_id>",
          inheritRights: "use"
        }
      ]
    },
    account_overrides: {
      <your connection_id>: {
        host: "<The new database host URL to connect to>",
        user: "<username>",
        password: "<password>"
      }
    }
  }
);

4. Supported Data Types