This add-on is operated by JAWS DB, LLC

MariaDB, the open source drop-in replacement for MySQL now available on Heroku

JawsDB Maria

Last updated 26 August 2016

JawsDB Maria is an add-on for providing a fully functional MariaDB database for use with your Heroku application.

MariaDB is a drop-in replacement and alternative to MySQL which is the world’s most widely used open-source relational database and is trusted by companies such as Google, Facebook, Twitter, and Youtube. Its power, stability, security, and wealth of online documentation has cemented it as a premiere option for start-ups and established businesses alike.

JawsDB Maria is simple to use and requires no custom client libraries to interface with. Adding a JawsDB Maria plan to your Heroku app gives access to a fully-functional MariaDB database with no barriers or hoops to jump through. Connect to your instance the way you would to any other MariaDB or MySQL server.

Provisioning the add-on

Standard provisioning

JawsDB Maria can be attached to a Heroku application via the CLI:

$ heroku addons:create jawsdb-maria
-----> Adding jawsdb-maria to sharp-mountain-4005... done, v18 (free)

Once JawsDB Maria has been added, a JAWSDB_MARIA_URL setting will be available in the app configuration and will contain the MariaDB connection string.

This variable can be confirmed at any time using the heroku config:get command.

$ heroku config:get JAWSDB_MARIA_URL
mysql://user:pass@instance:port/default_schema

After installing JawsDB Maria the application should be configured to fully integrate with the add-on.

Provisioning with custom backup/maintenance windows

JawsDB Maria instances have a daily backup period and a weekly maintenance period that is chosen randomly from set 8-hour blocks of time. These 8-hour blocks are chosen to minimize impact based on average business operation hours. However, JawsDB Maria also allows users to customize these windows when the instances are provisioned using custom command parameters.

The available command parameters are --bkpwindowstart, --bkpwindowend, --mntwindowstart, and --mntwindowend. You can choose to use only the backup parameters or just the maintenance parameters but they must be used with their twin. For instance, using only --mntwindowstart will not set a custom window. It must be used with its twin, --mntwindowend as well.

$ heroku addons:create jawsdb-maria --bkpwindowstart 00:30 --bkpwindowend 01:00 --mntwindowstart Tue:23:30 --mntwindowend Wed:00:00
-----> Adding jawsdb-maria to sharp-mountain-4005... done, v18 (free)

Local setup

Environment setup

This section offers tips for using the service in your development environment.

After provisioning the add-on it’s necessary to locally replicate the config vars so your development environment can operate against the service.

Use the Heroku Local command-line tool to configure, run and manage process types specified in your app’s Procfile. Heroku Local reads configuration variables from a .env file. To view all of your app’s config vars, type heroku config. Use the following command to add the JAWSDB_MARIA_URL values retrieved from heroku config to your .env file.

$ heroku config -s | grep JAWSDB_MARIA_URL >> .env
$ more .env

For more information, see the Heroku Local article.

Connection strategies

This article will provide examples for how to connect in several popular languages. The most common method will be to update your DATABASE_URL environment variable with the value stored in the JAWSDB_MARIA_URL variable, but one can also parse the JAWSDB_MARIA_URL variable directly if desired.

Using with Rails

Ruby on Rails applications can begin using their provisioned database by resetting their DATABASE_URL environment variable with the value held by their JAWSDB_MARIA_URL. Heroku will autogenerate the database.yml changes needed to connect from there. This can be done from your app’s Dashboard page under 'Settings’. It can also be done through the command line interface.

First, grab the current JAWSDB_MARIA_URL from your Heroku account.

$ heroku config:get JAWSDB_MARIA_URL
mysql://username:pass@instance:port/default_schema

Next, use the command line interface to set your DATABASE_URL parameter to the value returned by the previous command.

$ heroku config:set DATABASE_URL='mysql://username:pass@instance:port/default_schema'

$ heroku config:set DATABASE_URL='mysql2://username:pass@instance:port/default_schema'

It is generally recommended to use the mysql2 gem instead of the mysql gem. To install this gem, include the following line in your Gemfile.

gem 'mysql2'

Update application dependencies with bundler.

$ bundle install

JawsDB Maria is now ready to use with your Rails application. Issue the following command to set up the database according to your migrations.

$ heroku run rake db:create db:migrate

Using with PHP

Connection information for connecting via PHP can be extracted from the JAWSDB_MARIA_URL configuration variable in the following way.

$url = getenv('JAWSDB_MARIA_URL');
$dbparts = parse_url($url);

$hostname = $dbparts['host'];
$username = $dbparts['user'];
$password = $dbparts['pass'];
$database = ltrim($dbparts['path'],'/');

These variables can then be used in your preferred method for connecting to a MariaDB/MySQL database. MySQLi and PDO methods are shown below.

MySQLi (object-oriented example)

// Create connection
$conn = new mysqli($hostname, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connection was successfully established!";

MySQLi (procedural example)

// Create connection
$conn = mysqli_connect($hostname, $username, $password, $database);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connection was successfully established!";

PDO

try {
    $conn = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }

Using with Python/Django

As with Rails, Python/Django applications can begin using their provisioned database by resetting their DATABASE_URL environment variable with the value held by their JAWSDB_MARIA_URL. Heroku will autogenerate the settings.py changes needed to connect from there. This can be done from your app’s Dashboard page under 'Settings’. It can also be done through the command line interface.

First, grab the current JAWSDB_MARIA_URL from your Heroku account.

$ heroku config:get JAWSDB_MARIA_URL
mysql://username:pass@instance:port/default_schema

Next, use the command line interface to set your DATABASE_URL parameter to the value returned by the previous command.

$ heroku config:set DATABASE_URL='mysql://username:pass@instance:port/default_schema'

No extra work is needed. All subsequent deployments of your application will use your new JawsDB Maria instance.

Using with Java

Java using JDBC

The connection information necessary for creating a JDBC connection is all in the JAWSDB_MARIA_URL and can be extracted with code like the following.

private static Connection getConnection() throws URISyntaxException, SQLException {
    URI jdbUri = new URI(System.getenv("JAWSDB_MARIA_URL"));

    String username = jdbUri.getUserInfo().split(":")[0];
    String password = jdbUri.getUserInfo().split(":")[1];
    String port = String.valueOf(jdbUri.getPort());
    String jdbUrl = "jdbc:mysql://" + jdbUri.getHost() + ":" + port + jdbUri.getPath();

    return DriverManager.getConnection(jdbUrl, username, password);
}

Using Node.js

You can connect to your JawsDB Maria database using Node.js via the node-mysql module. Below is an example of how to do this using the environment variable that is set automatically by JawsDB Maria.

var mysql = require('mysql');
var connection = mysql.createConnection(process.env.JAWSDB_MARIA_URL);

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
  if (err) throw err;

  console.log('The solution is: ', rows[0].solution);
});

connection.end();

Backup/import data from another MariaDB database

For the purposes of this section of the tutorial, let’s assume the connection information of the old database is represented by the following.

Hostname: OLDHOST

Username: OLDUSER

Password: OLDPASS

Database: OLDDATABASE

If your connection is in a MariaDB or MySQL connection string, it would look similar to the following.

mysql://OLDUSER:OLDPASS@OLDHOST/OLDDATABASE?…

Backup existing database

The first step is to back up the existing database. To do this with mysqldump, you would issue the following command from a terminal.

$ mysqldump -h OLDHOST -u OLDUSER -pOLDPASS OLDDATABASE > backup.sql

You now have a backup of your database in your terminal’s working directory in a file called backup.sql

Migrating the backup to JawsDB Maria

Your JAWSDB_MARIA_URL configuration variable will contain the connection information in the following manner.

mysql://NEWUSER:NEWPASS@NEWHOST:3306/NEWDATABASE

To load your backup file into the new JawsDB Maria database, simply issue the following command from the same directory that you saved backup.sql

$ mysql -h NEWHOST -u NEWUSER -pNEWPASS NEWDATABASE < backup.sql

Your JawsDB Maria database will now contain the backed up data from your previous database.

Dashboard

Status information about your instance can be viewed by going to Dashboard > Resources > Add-ons and clicking JawsDB Maria. This will take you to a personalized page with information about your particular database. The dashboard can also be accessed via the CLI:

$ heroku addons:open jawsdb-maria
Opening jawsdb-maria for sharp-mountain-4005…

Databites

Once your database is listed with a status of 'AVAILABLE’, the dashboard will allow you to create Databites. Similar to Heroku’s own “Dataclips”, Databites provide users the ability to run basic SQL queries against their provisioned database and save those queries for later execution. Once a Databite is created, it is saved to your dashboard where it can be re-run, edited, or deleted with ease.

On your dashboard, under the section labeled Databites, select “Create New Databite”. This will direct your browser to the Databite page where custom queries can be entered into the editor which features SQL syntax highlighting. Clicking “Update” underneath the editor will execute the query against your database and return the result set in the same browser window in a tabular format.

Using the “Back” link will return you to the dashboard where your Databite will now be listed in the Databite section. Clicking on an existing Databite will redirect the browser to the Databite page and re-execute the query.

Troubleshooting

If the JAWSDB_MARIA_URL config var is not updated after a reasonable period of time, visit the Heroku Dashboard and select your application. Then, select JawsDB Maria from the Add-ons menu. If your MariaDB connection URL is there, you can use it to manually set your DATABASE_URL config var via the CLI or in Dashboard > Settings > Config Variables.

If it is not displayed, there was an error provisioning your database. Please contact us through the Heroku Support channels.

Migrating between plans

JawsDB Maria provides situational support for automatic upgrades and downgrades between plans. See the below sections for when an up/downgrade may be possible. In all cases where up/downgrading is allowed, your data should be preserved. However, it is always a good idea to backup your database before a plan change. See the section in this documentation entitled 'Backup/Import Data from another MariaDB Database’ for information on how to do this.

Upgrading/downgrading multi-tenant plans (Kitefin, Leopard, Blacktip)

Upgrading and downgrading between multi-tenant plans can be done through a CLI command.

Use the heroku addons:upgrade command to migrate to a new plan.

$ heroku addons:upgrade jawsdb-maria:newplan
-----> Upgrading jawsdb-maria:newplan to sharp-mountain-4005... done, v18 ($49/mo)
       Your plan has been updated to: jawsdb-maria:newplan

Upgrading single-tenant plans (Whitetip and higher)

Upgrading to a higher storage single-tenant plan can be done through a CLI command.

Use the heroku addons:upgrade command to migrate to a new plan.

$ heroku addons:upgrade jawsdb-maria:newplan
-----> Upgrading jawsdb-maria:newplan to sharp-mountain-4005... done, v18 ($49/mo)
       Your plan has been updated to: jawsdb-maria:newplan

Downgrading single-tenant plans (Whitetip and higher)

At this time, downgrading of single-tenant plans is not allowed through the addons:upgrade command. To move to a lower plan, please provision a plan of the size you want and follow the steps in this documentation for backing-up/importing from a MariaDB database to move your data to the new plan. Once you confirm that your new database is functioning correctly, simply remove the original plan.

Plans supporting Encryption at Rest

Encryption at Rest (EAR) is a new feature applying transparent AES-256 encryption to the underlying data storage of a JawsDB Maria database. It is supported for new provisions and upgrades for plans that had EAR at the time of their provision. Upgrading to an EAR-supporting plan from a non-EAR plan will not bestow EAR on the upgraded server. Current users of EAR-supporting plans from prior to the EAR feature-addition would need to provision a new version of their plan and migrate in order to gain Encryption at Rest.

Removing the add-on

JawsDB Maria can be removed via the Heroku Dashboard or the Heroku CLI.

$ heroku addons:destroy jawsdb-maria
-----> Removing jawsdb-maria from sharp-mountain-4005... done, v20 (free)

Before removing JawsDB Maria a data backup is advised. JawsDB Maria daily backups are only available while the add-on is attached to your account.

FAQ

I am unable to insert records into my database. What gives?

If you are on one of JawsDB Maria’s shared plans, you may have exceeded the database size allotted via your plan. When a database grows larger than the plan allows, INSERT privileges are revoked on the database user until the database is brought back into compliance. SELECT, UPDATE, and DELETE privileges are still available during this time. Once the database size is reduced back under the maximum allowed size, the INSERT privilege will be reinstated.

The following can be run against the database or placed into a databite in order to see current database size in MB and whether the account currently has INSERT privileges.

select
    s.schema_name
    ,sp.grantee user
    ,cast(round(sum(coalesce(t.data_length + t.index_length, 0)) / 1024 / 1024, 3) as char) db_size_mb
    ,sp.has_insert
from
    information_schema.schemata s
    inner join
    information_schema.tables t on s.schema_name = t.table_schema
    inner join (
        select
            spi.grantee
            ,spi.table_schema
            ,max(
                case
                    when spi.privilege_type = 'INSERT' then 1
                    else 0
                end
            ) has_insert
        from
            information_schema.schema_privileges spi
        group by
            spi.grantee
            ,spi.table_schema
    ) sp on s.schema_name = sp.table_schema
group by
    s.schema_name;

I am getting an error saying I have exceeded the 'max_questions’ resource. What is that?

The 'max_questions’ value is a limit that is placed on shared plan accounts to help preserve computing power for other shared plan users on the same server. It is essentially a limit on the number of queries that can be executed from that account in an hour. After 1 hour of reaching the limit, the limit will be reset and queries can again be executed.

Single-tenant plans (such as Whitetip and above) carry no 'max_questions’ limits.

Support

All JawsDB Maria support and runtime issues should be submitted via one of the Heroku Support channels. Any non-support related issues or product feedback is welcome at our email address and our Twitter account.