Forge Home


Hiera 5 backend to query data lookups over MySQL/MariaDB


6,966 latest version

5.0 quality score

We run a couple of automated
scans to help you access a
module's quality. Each module is
given a score based on how well
the author has formatted their
code and documentation and
modules are also checked for
malware using VirusTotal.

Please note, the information below
is for guidance only and neither of
these methods should be considered
an endorsement by Puppet.

Version information

  • 3.0.1 (latest)
  • 3.0.0
  • 2.0.0
  • 0.2.0
released Oct 2nd 2017
This version is compatible with:
  • Puppet Enterprise 2018.1.x, 2017.3.x, 2017.2.x, 2017.1.x, 2016.4.x
  • Puppet >= 4.9.0 < 6.0.0
  • , ,

Start using this module

  • r10k or Code Manager
  • Bolt
  • Manual installation
  • Direct download

Add this module to your Puppetfile:

mod 'crayfishx-hiera_mysql', '3.0.1'
Learn more about managing modules with a Puppetfile

Add this module to your Bolt project:

bolt module add crayfishx-hiera_mysql
Learn more about using this module with an existing project

Manually install this module globally with Puppet module tool:

puppet module install crayfishx-hiera_mysql --version 3.0.1

Direct download is not typically how you would use a Puppet module to manage your infrastructure, but you may want to download the module in order to inspect the code.



crayfishx/hiera_mysql — version 3.0.1 Oct 2nd 2017


This is hiera-mysql for Hiera 5 users (Puppet 4.9+) - if you are running older versions please see the 2.x branch for the hiera-mysql Hiera 3 backend

For more information on migrating to Hiera 5, See the official documentation


puppet module install crayfishx/hiera_mysql


Hiera-mysql supports both native C extensions for use with standard ruby and the jRuby JDBC and will load whichever library is suitable for the ruby it has been installed on. This ensures that hiera-mysql operates under puppet apply which uses regular Ruby and also under puppetserver which implements jRuby.

If you are using Hiera-mysql under jRuby for Puppet Server you will need to install the jdbc gem:

/opt/puppetlabs/bin/puppetserver gem install jdbc-mysql

If you are using Hiera-mysql under standard ruby (eg: for puppet apply), you will need the mysql gem

/opt/puppetlabs/puppet/bin/gem install mysql


Hiera-mysql 2.0.0 is the legacy backend to Hiera 3.x and shipped as a rubygem. Important fixes may still be contributed to the 2.x branch, however it's highly recommended that users switch to 3.x. Hiera-mysql 3.0.0 is a complete refactor designed to work as a Hiera 5 backend, for users running Puppet 4.9 or higher. Hiera-mysql 3.0.0 does not ship as a rubygem and should be used from the Puppet module.


Hiera is a configuration data store with pluggable back ends, hiera-mysql is a back end that fetches configuration valus from a MySQL/MariaDB database. It can be use instead of or along side other back ends.


There are two different ways to configure the mysql backend. You can configure it as a lookup_key or data_hash backend. The differences between these two types are documented in the official Hiera docs. lookup_key should be used to perform a MySQL query for each individual lookup request and return the value. data_hash should be used to perform one MySQL query per catalog compilation that returns a key value map for all data values. Examples of both methods can be found below.

Example database

In the following examples, the following database structure is being used;

MariaDB [config]> DESC configdata;
| Field       | Type      | Null | Key | Default | Extra          |
| id          | int(11)   | NO   | PRI | NULL    | auto_increment |
| val         | char(255) | YES  |     | NULL    |                |
| var         | char(255) | YES  |     | NULL    |                |
| environment | char(255) | YES  |     | NULL    |                |
4 rows in set (0.00 sec)

MariaDB [config]> select * from configdata;
| id | val         | var           | environment |
|  1 | | ntp::server   | production  |
|  2 |    | ntp::server   | development |
|  3 | Hello       | motd::message | production  |

Hiera configuration

The hiera-mysql backend takes the following for the options hash of the Hiera configuration

  • host: Hostname to connect to
  • username: Username to use for authentication
  • password: Password to use for authentication
  • database: Name of the MySQL database
  • query: The SQL query to run. The special keyword __KEY__ can be used to interpolate the lookup key into the query (only for lookup_key)
  • return: For use with the lookup_key type. When set to first will always return the first row even if the query returned multiple, when set to array will always return an array even if the query only returned one row.


The lookup_key type defines a query that should be run for each lookup request and expects to return one value. If the query returns multiple rows, then the first row will be returned. __KEY__ may be used in the query and will be interpolated as the lookup key.


  - name: "MySQL lookup"
    lookup_key: hiera_mysql
      host: localhost
      username: root
      password: foobar
      database: config
      query: "SELECT val FROM configdata WHERE var='__KEY__' AND environment='%{environment}'"


The lookup_type method can return arrays. By default, it will always return a string if one row is returned from the query, and will return an array when multiple rows are returned. You can be more explicit by setting the return option in the options hash to:

  • array: Always return an array, even if the query only returned one row.
  • first: Always return the first row as a string, even if the query returned multiple rows.


The data_hash type defines a query that should be run just once for each Puppet run. The query should be one that returns rows of two columns, the first column matching the key and the second with the value. Further column will be ignored.


  - name: "MySQL lookup"
    data_hash: hiera_mysql
      host: localhost
      username: root
      password: foobar
      database: config
      query: "SELECT var,val FROM configdata WHERE environment='%{environment}'"