Reaching into your Oracle Database with Puppet

So now you have used Puppet to manage your application schema in the database. But you notice that the application also has some settings stored in tables inside of the database. Settings for external services like the URL of a WebService or the UNC of a server you have to connect to. Settings that contain different values in the production environment than in the test or development environment. This situation is a perfect match for Puppet. But how do we get Puppet to do this? This post will show you how the ora_config module, makes this possible.

The ora_record Puppet type

The ora_config Puppet module contains a type called ora_record. This is the Puppet type that allows you to manage a single record in the database. Let’s start with a simple definition:

ora_record{'set_external_service_name':
  ensure     => 'present',
  table_name => 'CONFIG_DATA',
  username   => 'ORACLE_USER',
  password   => 'verysecret',
  key_name   => 'CONFIG_ID',
  key_value  => 10,
  data       => {
    CONFIG_NAME  => 'service_name',
    CONFIG_VALUE => 'http://external.data-server.com',
    ...
  }
}

This Puppet code tells you that the table CONFIG_DATA from user ORACLE_USER must contain a record where the primary key CONFIG_ID is 10. If Puppet notices that this record doesn’t exist, It will create the record and fill its data with the data specified in the data property. If Puppet sees that the key already exists, it does nothing.

This code will make sure your database contains the record, but it will not ALWAYS set the data. This code is useful for example in use cases where there is a set of management screens to manage these settings. Puppet makes sure the setting exists, but will leave the settings as they are after the system is running and applications managers might have changed the values.

But the record must ALWAYS be like I specified

The ora_record also support this use case. Actually, it is very simple. Just change the present into updated and the way ora_racord manages the record changes.

ora_record{'set_external_service_name':
  ensure     => 'updated',
  table_name => 'CONFIG_DATA',
  username   => 'ORACLE_USER',
  password   => 'verysecret',
  key_name   => 'CONFIG_ID',
  key_value  => 10,
  data       => {
    CONFIG_NAME  => 'service_name',
    CONFIG_VALUE => 'http://external.data-server.com',
    ...
  }
}

Now Puppet will not only check if the record exists, but it will also always make sure the specified columns contain the specified values. If there are columns you don’t want to manage, then just leave them blank.

Now in the real world

So now we know the basics, how can we use this type to make sure the service_name is set correct. Let’s expand this example into setting other properties and also add support for different values in in different environments. Let’s first create a basic Puppet class.

class config_settings(
  $config,
)
{
  $username = hiera('database_username')
  $password = hiera('database_password')

  Ora_record{
    ensure     => 'updated',
    table_name => 'CONFIG_DATA',
    username   => $username,
    password   => $password,
  }
  create_resources('ora_record', $config)
}

This code allows you to put the actual configuration settings into hiera. We have extracted the username and password property. You’ll only have to fill them in once. For other settings, you can add a Hash containing all values. The YAML data could look like this:

database_username: ORACLE_USER
database_password: verysecret

config_settings:
  set_external_service_name:
    key_value: 10
    service_name: 'http://external.data-server.com'
  set_external_port_number:
    key_value: 11
    port_number: 8081

In hiera it is easy to distinguish between data for the production environment and data for the test or development environment. So using this code, you can easily differentiate multiple values between the different environments.

Anti patterns

So now we can manage the content of the database. Let’s manage 1.000 records using ora_record. Although this works, we don’t recommend this. The best use case for ora_record is to manage database content that relates to configuration information. Although you could use ora_record to manage other application data, this makes a (too) strong coupling between application and platform.

Get started on your own Puppet code

In the blog post about managing database schemas, we showed you how you can use Puppet to manage the schema definitions of your applications. Now we showed you how the ora_record in the ora_config can help you manage individual records containing configuration data that is stored inside the Oracle database.

We now have all the ingredients needed to manage a database installation for zero to a running production environment. Check the documentation of ora_config for details and get started on your own Puppet code.