DBaaS API Launch Walkthrough

Overview

This walkthrough explains how to launch database services and manage the lifecycle of database services using the SkySQL DBaaS API with a REST client.

For users who prefer other interfaces, SkySQL offers the following alternatives:

This walkthrough demonstrates a service configuration that is suitable for a quick test. A more customized configuration should be selected for performance testing or for alignment to the needs of production workloads.

For this simple test, we store credentials in environment variables. You are encouraged to adapt these instructions as needed to meet your security requirements.

Compatibility

SkySQL Topologies

The examples in this procedure create a SkySQL service that uses the Enterprise Server Single Node topology, but the procedure can be adapted to other topologies as well:

  • Enterprise Server Single Node

  • Enterprise Server With Replica(s)

  • Xpand Distributed SQL

  • ColumnStore Data Warehouse

  • Serverless Analytics

Operating Systems

This procedure uses curl as the REST client. curl is available for Linux, macOS, and MS Windows. If curl is not available for your operating system, the procedure can be adapted for other REST clients.

Dependency

This Quickstart describes how to make API calls using curl, but could be adapted for an alternative REST API client. curl is available for Linux, macOS, and MS Windows. Install curl then proceed.

Examples below also use jq, a JSON parsing utility. jq is available for Linux, macOS, and MS Windows. Install jq then proceed.

The examples also make use of tee to save the response JSON data to a file while also allowing it to be piped to jq for output. Both Linux and macOS support tee as described in the examples. On MS Windows, Powershell has a tee command that requires the -filepath option to be inserted prior to the filename.

The chmod command is used to make a file private to the current user. If your environment doesn't support chmod, you can set the file's permissions using other means.

The examples also make use of exported variables and ${VARIABLE_NAME} variable references that are compatible with Bourne-like shells (such as sh, bash, and zsh). On MS Windows, you will need to adapt these instructions if you are not using a Bourne-like shell. For example, you can copy just the jq part of an export command (from inside the backticks), run that on its own, and then copy/paste the resulting string into a variable assignment for your shell.

Finally, the examples use a backslash at the end of some of the lines to indicate to the shell that a command spans multiple lines. If your shell doesn't allow this, remove each trailing backslash character and join the following line to the end of the current line.

Launch a Service

Step 1: Generate API Key

  1. Go to the Generate API Key page.

  2. Fill out the API key details:

    • In the "Description" field, describe the purpose of the API key.

    • In the "Expiration" field, specify how long this key will be valid. If you need to revoke the key before it expires, you can revoke it from the API Keys page.

    • In the "Scopes" field, select the "read" and "write" scopes under SkySQL API: Databases.

  3. Click the "Generate API Key" button.

  4. After the page refreshes, click the "Copy to clipboard" button to copy the API key.

  5. Paste the API key somewhere safe and do not lose it.

  6. Set the SKYSQL_API_KEY environment variable to the new API key value:

    $ export SKYSQL_API_KEY='... key data ...'

    The SKYSQL_API_KEY environment variable will be used in the subsequent steps.

Step 2: Determine the Client IP Address

When your new service is created, your client can only connect through the service's firewall if the client IP address is in the service's IP allowlist.

Before creating the new service, determine the public IP address of your client host and save it to the SKYSQL_CLIENT_IP environment variable.

If you are not sure of your public IP address, you can use a lookup service, such as checkip.amazonaws.com:

$ export SKYSQL_CLIENT_IP=`curl -sS checkip.amazonaws.com`

Step 3: Launch a Service

To launch a service:

  1. Prepare a request body containing the desired service options in a file called request-service.json:

    $ cat > request-service.json <<EOF
{
  "service_type": "transactional",
  "topology": "es-single",
  "provider": "gcp",
  "region": "us-central1",
  "architecture": "amd64",
  "size": "sky-2x8",
  "storage": 100,
  "nodes": 1,
  "version": "10.6.11-6-1",
  "name": "skysql-nr-quickstart",
  "ssl_enabled": true,
  "allow_list": [
     {
        "comment": "Describe the IP address",
        "ip": "${SKYSQL_CLIENT_IP}/32"
     }
  ]
}
EOF

    This configuration is suitable for a quick test, but a more customized configuration should be selected for performance testing or for alignment to the needs of production workloads:

  2. Provide the request to the /provisioning/v1/services API endpoint to create (launch) a new database service and save the response to the response-service.json file:

    $ curl -sS --location --request POST \
   --header "Authorization: Bearer ${SKYSQL_API_KEY}" \
   --header "Accept: application/json" \
   --header "Content-type: application/json" \
   --data '@request-service.json' \
   https://api.mariadb.com/provisioning/v1/services \
   | tee response-service.json | jq .

    Upon success, the command will return JSON with details about the new service.

  3. Read the service ID for the new service and save the value in the SKYSQL_SERVICE environment variable:

    $ export SKYSQL_SERVICE=`jq -r .id response-service.json`

Step 4: Check Service State

Before advancing, check the service state using the /provisioning/v1/services/${SKYSQL_SERVICE} API endpoint:

$ curl -sS --location --request GET \
   --header "Authorization: Bearer ${SKYSQL_API_KEY}" \
   --header "Accept: application/json" \
   https://api.mariadb.com/provisioning/v1/services/${SKYSQL_SERVICE} \
   | tee response-state.json | jq .status

When the service is still being launched, the JSON payload will contain "pending_create" or "pending_modifying" as the service status.

When the service has been launched, the JSON payload contains "ready", and you can continue with the next steps. Keep in mind that some of the following values will not be populated in the JSON data until this ready status has been achieved.

Step 5: Obtain Connection Details

Obtain the connection credentials for the new SkySQL service by executing the following commands:

  1. If ssl_enabled is true on your service (the default), download skysql_chain_2022.pem, which contains the Certificate Authority chain that is used to verify the server's certificate for TLS:

    $ curl https://supplychain.mariadb.com/skysql/skysql_chain_2022.pem --output ~/Downloads/skysql_chain_2022.pem

  2. Obtain the hostname and port of the service and save them to the SKYSQL_FQDN and SKYSQL_PORT environment variables:

    • The hostname is specified with the "fqdn" key.

      $ export SKYSQL_FQDN=`jq -r .fqdn response-state.json`

    • Available TCP ports are specified in the "endpoints" array. For this test, connect to the "port" where "name" is "readwrite".

      $ export SKYSQL_PORT=`jq '.endpoints[0].ports[] | select(.name=="readwrite") | .port' response-state.json`

  3. Obtain the default username and password for the service using the /provisioning/v1/services/${SKYSQL_SERVICE}/security/credentials API endpoint and save the response to the response-credentials.json file:

    $ curl -sS --location --request GET \
   --header "Authorization: Bearer ${SKYSQL_API_KEY}" \
   --header "Accept: application/json" \
   --header "Content-type: application/json" \
   https://api.mariadb.com/provisioning/v1/services/${SKYSQL_SERVICE}/security/credentials \
   | tee response-credentials.json | jq .

    The default username and password will not be available until the service state is "ready".

  4. Set the file's mode to only allow the current user to read its contents:

    $ chmod 600 response-credentials.json

  5. Read the username and password from response-credentials.json and save them to the SKYSQL_USERNAME and SKYSQL_PASSWORD environment variables:

    $ export SKYSQL_USERNAME=`jq -r .username response-credentials.json`

    $ export SKYSQL_PASSWORD=`jq -r .password response-credentials.json`

Step 6: Connect

Connect to the database using the host, port, and default credentials using the mariadb client:

$ mariadb --host ${SKYSQL_FQDN} --port ${SKYSQL_PORT} \
   --user ${SKYSQL_USERNAME} --password="${SKYSQL_PASSWORD}" \
   --ssl-ca ~/Downloads/skysql_chain_2022.pem

If you don't want the password to appear on the command-line, specify the --password command-line option without an argument to be prompted for a password.

Step 7: Save Connection Information (Optional)

To connect to your SkySQL service easily, it is possible to create a .my.cnf file in your home directory that contains all the details of your connection.

  1. Use the following command to create a new .my.cnf file or overwrite an existing one and populates it with the connection information that was collected in the previous steps:

    $ cat > ~/.my.cnf <<EOF
[client]
host=${SKYSQL_FQDN}
port=${SKYSQL_PORT}
user=${SKYSQL_USERNAME}
password="${SKYSQL_PASSWORD}"
ssl-ca=${HOME}/Downloads/skysql_chain_2022.pem
EOF

  2. Set the file system permissions for the .my.cnf file to ensure that other users can't read it:

    $ chmod 600 ~/.my.cnf

  3. When all the connection parameters are in your ~/.my.cnf file, the mariadb client can connect without specifying any command-line options:

    $ mariadb

Contact Us

To contact us with questions or if you need assistance: