Data Services

Helion Stackato includes a number of data services which you can bind to your applications. These services include several databases (MySQL, PostgreSQL, MongoDB, Redis), the RabbitMQ messaging service, a persistent file system service, and Memcached.

Configuring Helion Stackato Data Services

You must specify the data services for your application at the time you push your application to Helion Stackato. You can configure data services in three ways:

  • Specify services in your application's manifest.yml file
  • Specify services during the push command
  • Manually create and bind services

You can also use an external database system. For more information, see Using External Database Services.

Specifying Services Using manifest.yml

The manifest.yml file can hold many application-specific details that instruct the Helion Stackato Client without the need to be specified during stackato push. For a detailed description of manifest.yml file, see Manifest.yml Options.

In the following example, the manifest.yml file instructs the Helion Stackato Client to request a MySQL database called cirrusdb:

name: cirrus
memory: 256M
instances: 2
services:
  cirrusdb: mysql

The following are the default service types in Helion Stackato that you can include in manifest.yml.

To access data services after they have been created, see Accessing Configured Database Services.

Specifying Services During stackato push

If you do not specify services in the manifest.yml file, you will be prompted to create one during the push process. To set up a database service, enter y when prompted:

$ stackato push
Would you like to bind any services to 'cirrus' ?  [yN]: y
The following system services are available
1. mongodb
2. mysql
3. postgresql
4. redis
5. <None of the above>
Please select one you wish to provision: 2
Specify the name of the service [mysql-18cab]: cirrusdb
Creating Service: OK
Binding Service: OK

In order to ensure the correct services are configured each time the app is pushed, your services should be listed in the manifest.yml file.

Manually Using create-service And bind-service

You can create and bind services to an application even after they are pushed to Helion Stackato. You can do this in two ways:

  • To combine all of the parameters into a single command, run the stackato create-service <service> <name> <app> command.

    • <service> is the type of service (for example, mysql, redis, postgresql, or mongodb).
    • <name> is the name you want to assign to the service.
    • <app> is the name of the application the service will be bound to.

    For example:

    $ stackato create-service mysql ordersdb myapp
        Creating Service: OK
        Binding Service: OK
        Stopping Application [myapp]: OK
        Staging Application [myapp]: OK
        Starting Application [myapp]: OK
    

    To see the services bound to your application, run the stackato apps command.

  • You can also run the create-service <service> <name> and bind-service <servicename> <app> commands separately. This option accomplishes the same result but also allows you to create and configure a service before binding it.

    While the parameters are the same as in the combined command, <servicename> is the name of the service assigned during the create-service command.

    For example:

    $ stackato create-service mysql customerdb
        Creating Service: OK
    
        $ stackato bind-service customerdb myapp
        Binding Service: OK
        Stopping Application [myapp]: OK
        Staging Application [myapp]: OK
        Starting Application [myapp]: OK
    

For more information on server management commands, see stackato services.

Note

To remotely check the settings and credentials of any Helion Stackato service, you can run the stackato service command.

Using SQLite

Applications can also use an SQLite database as an alternative to Helion Stackato database services.

Important

Because the filesystem of an application container is ephemeral (it is destroyed when an application is stopped, restarted, or updated), always store the SQLite file on a Persistent File System mount point to avoid losing your data.

Using Database Services

When you bind a database service to an application running on Helion Stackato, the environment variables containing that service's host, port, and credentials are added to the application container. You can use these environment variables in your code to connect to the service, rather than hard-coding the credentials.

For examples of parsing and using these variables, see Language Specific Deployment.

DATABASE_URL

If only one relational database service is bound to an application, use the DATABASE_URL environment variable. DATABASE_URL contains the connection string for the bound database in the following format:

protocol://<username>:<password>@<host>:<port>/<database-name>

For example, a DATABASE_URL environment variables for a PostgreSQL service may look as follows:

postgres://u65b0afbc8f8f4a1192b73e8d0eb38a24:p9eb83c11c59c4bcabfa475a4871e9242@192.168.69.117:5432/da17e48ddc82848499cb387bc65f5d4f9

The protocol portion specifies the type of database. For example:

  • mysql://
  • postgresql://

Note

The database name portion of the URL is the actual database name (for example, da17e48ddc82848499cb387bc65f5d4f9), not the user-specific service name set during deployment or service creation (for example, myapp-db).

Database-Specific URLs

  • If a non-relational data service type is bound to an application, use the corresponding named environment variable:

    • MONGODB_URL
    • REDIS_URL
    • RABBITMQ_URL
  • If more than one relational database service type is bound to an application (for example, MySQL and PostgreSQL), the DATABASE_URL variable will not be set. However, the following database-specific variables will be set:

    • MYSQL_URL
    • POSTGRESQL_URL
    • ORACLE_URL (with the Oracle Database add-on)

    Note

    These variables have the same format as DATABASE_URL.

  • If more than one database of the same type is bound to an application (for example, two MongoDB services), none of the URL formatted environment variables will be available. In this case, use the STACKATO_SERVICES or VCAP_SERVICES environment variable.

STACKATO_SERVICES

This environment variable contains a JSON string listing the credentials for all bound services, grouped by service name. For example:

{
        "postdb": {
                "name": "d4854a20e5854464891dbd56c08c440d9",
                "host": "192.168.0.112",
                "hostname": "192.168.0.112",
                "port": 5432,
                "user": "u74499595373c4bea84be2a87c2089101",
                "username": "u74499595373c4bea84be2a87c2089101",
                "password": "pdbbe19398c5a4463bba0644f7798f1f1"
        },
        "mydb": {
                "name": "d0a60c0be931f4982bbef153f993237bc",
                "hostname": "192.168.0.112",
                "host": "192.168.0.112",
                "port": 3306,
                "user": "u93Mm8XmGXQ9R",
                "username": "u93Mm8XmGXQ9R",
                "password": "p8LwNeQXMrNzi"
        }
}

VCAP_SERVICES

Note

VCAP_SERVICES variables in Helion Stackato v2.2 and later use non-versioned service names. The version number remains in the label key.

This environment variable contains a JSON string listing the credentials for all bound services, grouped by service type. For example:

{
        "mysql": [
                {
                        "name": "mydb",
                        "label": "mysql-5.5",
                        "plan": "free",
                        "tags": [
                                "mysql",
                                "mysql-5.5",
                                "relational"
                        ],
                        "credentials": {
                                "name": "d0a60c0be931f4982bbef153f993237bc",
                                "hostname": "192.168.0.112",
                                "host": "192.168.0.112",
                                "port": 3306,
                                "user": "u93Mm8XmGXQ9R",
                                "username": "u93Mm8XmGXQ9R",
                                "password": "p8LwNeQXMrNzi"
                        }
                }
        ]
}

VCAP_SERVICES also contains additional meta-information. You can use this variable for compatibility with Cloud Foundry.

Using External Databases

You can allow applications that run on Helion Stackato to use external databases in three ways:

  • Hard-code the host and credentials in the code.

    Important

    This practice is not recommended.

    Applications that write database connection details during staging rather than taking them from environment variables at run time, must be restaged to pick up the new service location and credentials. Restarting the application will not automatically force restaging.

  • Specify the URL or credential array in an environment variable, and/or

  • Specify the connection details in a user-provided service instance.

For security reasons, Helion Stackato container security prevents applications from connecting to arbitrary servers and ports on the local subnet. To allow your application to connect to a database server you must either:

Accessing Database Services

You may need to connect to a database service directly to perform initial database setup, modify fields, run queries, or back up data. You can perform these operations using the dbshell (recommended) or tunnel commands.

Using dbshell To Access Database Services

The stackato dbshell command creates an SSH tunnel to database services. To open an interactive shell to a service, run the stackato dbshell command:

$ stackato dbshell <application-name> <service-name>

This command automatically opens the appropriate database client for the database you connect to. (The client must be installed on the local system.)

This command is also available inside application containers, allowing you to quickly import data from dump files, or to set up schemas. For example, to import data from file in an application directory, you can use a hook in the manifest.yml file:

hooks:
  post-staging:
    - dbshell < setup/sample-data.sql

To Use tunnel To Access Database Services

The stackato tunnel command is an alternative method for accessing database services. The command creates a small Ruby application that proxies database requests over HTTP. This is the standard method for database access in Cloud Foundry. However, using tinnel is slower than using dbshell.

  1. Create or use an existing tunnel:

    $ stackato tunnel <service-name>
    

    Depending on the service you connecting to, a list of options is displayed. In the following example, a connection to a MySQL service is established:

    Getting tunnel url: OK, at https://tunnel-xxxxx.stackato-xxxx.local
    Getting tunnel connection info: OK
    
    Service connection info:
    +----------+-----------------------------------+
    | Key      | Value                             |
    +----------+-----------------------------------+
    | username | uT9efVVFCk                        |
    | password | pHFitpIU1z                        |
    | name     | d5eb2468f70ef4997b1514da1972      |
    +----------+-----------------------------------+
    
    1. none
    2. mysql
    3. mysqldump
    Which client would you like to start?
    
    • For simple command-line access, enter 2.

    • To get a dump of the entire database, enter 3.

      You are prompted to enter a path for the directory where the database dump will be saved.

    • To connect with a database viewer, or to run multiple commands from the command line, passing in SQL files, select option enter 1.

      You how have all the information you need to access the database.

  2. In a terminal, connect to the MySQL database directly using the mysql command, for example:

    $ mysql --protocol=TCP --host=localhost --port=10000 --user=uT9efVVFCk --password=pHFitpIU1z d5eb2468f70ef4997b1514da1972
    
  • To import an SQL file, run the same command, and pipe in the file:

    $ mysql --protocol=TCP --host=localhost --port=10000 --user=<user> --password=<pass> <name> < mydatabase.sql
    
  • To pull a dump of all databases, run the mysqldump command:

    $ mysqldump -A --protocol=TCP --port=10000 --host=localhost --user=<user> --password=<pass>
    

Importing and Exporting Data

To Pre-Populate a Database While Pushing an Application

To populate a database with data the first time the database is run, you can use a hook during the staging process.

  1. Create a script file in the application's root directory that uses the same data source variables from STACKATO_SERVICES as the ones used in the application. In the following example, written in Perl, this file will open a connection to the database, create tables, and insert records as necessary.

    use strict;
    use warnings;
    
    use DBI;
    use DBD::mysql;
    use JSON "decode_json";
    
    my $services = decode_json($ENV{STACKATO_SERVICES});
    my $credentials = $services->{mydb};
    
    my $dbh = DBI->connect("DBI:mysql:database=$credentials->{name};hostname=$credentials->{hostname};port=$credentials->{port};",
                           $credentials->{'user'}, $credentials->{'password'})
        or die "Unable to connect: $DBI::errstr\n";
    
    my $sql_init =
        'CREATE TABLE customers (
                        id INT(11) AUTO_INCREMENT PRIMARY KEY,
                        customername TEXT,
                        created DATETIME
                );
        ';
    $dbh->do($sql_init);
    
    $sql_init =
                'INSERT INTO customers
                        (customername, created)
                VALUES
                        ("John Doe", now()),
                        ("Sarah Smith", now());
        ';
    $dbh->do($sql_init);
    
    $dbh->disconnect;
    
  2. Add a post-staging hook to your your manifest.yml file. This hook will execute a command that will run the script:

    name: customertracker
    services:
      mysql: customerdb
    hooks:
      post-staging: perl preload.pl
    

    Your script will execute after the staging process is complete but before the application begins to run.

To Import Data from a MySQL or PostgreSQL Database

You can import database dump files for MySQL or PostgreSQL using the stackato dbshell command:

$ stackato dbshell <application-name> <service-name> < dumpfile.sql

This command (equivalent to the stackato run dbshell command) redirects the contents of a local database dump file to the appropriate database client running in the application instance.

Tip

If you run this command from a directory that contains the manifest.yml file, you can omit the application and service names.

Important

The PostgreSQL database dump may contain some records that cannot be imported into the new database instance, causing error messages such as ERROR: role "uf44609668ffa4101b2945b7211a0de21" does not exist when the import is run. You can safely ignore these errors.

  1. run the tunnel command to access the database service.

  2. When prompted, enter 1 (none).

    A port for establishing a local connection is created:

    Starting tunnel to remarks on port 10000.
    Open another shell to run command-line clients or
    use a UI tool to connect using the displayed information.
    Press Ctrl-C to exit...
    
  3. Import the SQL dump file using a local client, such as mysql:

    $ mysql --protocol=TCP --host=localhost --port=10000 --user=<user> --password=<pass> <name> < mydatabase.sql
    

For detailed instructions on creating a mysqldump backup that can be imported into another database service, see Backing Up a MySQL database.

To Import Data from a MongoDB Database

You can import database archive for MongoDB using the mongorestore command.

  1. Upload the archive to the application container:

    $ stackato scp -a <app> dump.tgz :
    
  2. Extract the tar.gz file:

    $ stackato run -a <app> tar xvf dump.tgz
    
  3. Populate the database using the data service credentials from stackato service:

    $ stackato run -a <app> mongorestore -h <host> --port <port> -u <username> -p <password> --db <name> dump/db/
    

Backing Up Data from MySQL or PostgreSQL

You can export data from MySQL and PostgreSQL databases running in Helion Stackato into their respective dump file formats using the dbexport command available in Helion Stackato application containers.

To export a database, run the stackato run command to remotely execute the dbexport tool:

$ stackato run -a <application-name> dbexport <service-name> > dumpfile.sql

The dbexport command is run on the specified data service and directs the output to a local file.

Tip

If you run this command from a directory that contains the manifest.yml file, you can omit the application name.

To Back Up Data from a MongoDB Database

You can export data from MongoDB databases running in Helion Stackato into an archive using the mongodump command available in a bound application container. You must provide the database credentials manually.

  1. Acquire the credentials:

    $ stackato service <service-name>
    
  2. Use the username, password, name, host, and port values to run the mongodump command:

    $ stackato run -a <app> mongodump -h <host> --port <port> -u <username> -p <password> --db <name>
    

    A dump directory is created in the application container.

  3. Compress the directory:

    $ stackato run -a <app> tar cvf dump.tgz dump/db
    
  4. Download the archive:

    $ stackato scp -a <appname> :dump.tgz .
    

Understanding Database Version Changes

The VCAP_SERVICES environment variable in Helion Stackato does not include version numbers in the service name string. This can cause issues when migrating applications from Cloud Foundry v1 systems which reference versioned database names in VCAP_SERVICES. You can avoid these issues in one of two ways:

  • Update references to VCAP_SERVICES in the application code to exclude version numbers. For example:

    MySQL:         'mysql-5.x' -> 'mysql'
    PostgreSQL:    'postgresql-x.x' -> 'postgresql'
    Redis:         'redis-2.x' -> 'redis'
    
  • Update the application code to use the DATABASE_URL environment variable.

    The following changes (git commits) to sample applications show this modification:

    For more information, see Using Database Services and the following language-specific documentation: