Data 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.

• filesystem
• memcached
• mongodb
• mysql
• postgresql
• rabbitmq
• redis

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.

Using SQLite¶

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

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://

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¶

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 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>
  

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.

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.

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:

  • Perl
  • Python
  • PHP

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

  • Perl Data Services
  • PHP Data Services
  • Python Data Services