Using skysqlcli with the DBaaS API

Overview

SkySQL operations can be automated using the MariaDB SkySQL DBaaS API.

The skysqlcli command-line utility is one way to access the SkySQL DBaaS API.

The examples on this page demonstrate common service management operations. For a full list of supported operations and command-line options, see skysqlcli Commands.

The skysqlcli utility is a Technical Preview. Software in Tech Preview should not be used for production workloads.

For a guided walkthrough on how to use the skysqlcli utility, see "Get Started Using SkySQL DBaaS API".

Use Cases

  • Scripting or automating the creation or deletion of SkySQL services

  • Retrieving the default database credentials for a SkySQL service

  • Updating the IP allowlist for a SkySQL service

Compatibility

  • Distributed Transactions

  • Multi-Node Analytics

  • Replicated Transactions

  • Single Node Analytics

  • Single Node Transactions

Installation

  1. Access MariaDB Downloads for the skysqlcli utility.

  2. In the "OS" dropdown, select your operating system.

  3. Click the "Download" button to download the binary for your operating system.

  4. Rename the downloaded file.

    On Linux:

    $ mv skysqlcli-linux skysqlcli
    

    On macOS:

    $ mv skysqlcli-macos skysqlcli
    

    On Microsoft Windows:

    C:\> ren skysqlcli-windows.exe skysqlcli.exe
    
  5. Set the executable's permissions, so that it can be executed.

    On Linux and macOS:

    $ chmod 0755 skysqlcli
    

    On Microsoft Windows, the default file permissions allow the file to be executed. If you encounter issues, check the file's Access Control List (ACL) to confirm your user account or security group have Read & Execute permissions on the executable file.

  6. Place the executable in an appropriate directory on your system, such as:

    Operating Systems

    Typical Executable Directories

    • Linux

    • macOS

    • /usr/local/bin

    • $HOME/bin

    • Microsoft Windows

    • Any directory referenced in the PATH environment variable

Authentication and Authorization

skysqlcli interfaces with the MariaDB SkySQL DBaaS API. The DBaaS API requires authentication and authorization.

  1. Sign up for SkySQL

  2. Obtain a SkySQL API Key with SkySQL API: Databases scope

  3. Provide the SkySQL API key to the skysqlcli command-line interface.

    Substitute your SkySQL API key in place of YOUR_SKYSQL_API_KEY using one of the following methods:

    • The --api-key command-line option can be used:

      $ skysqlcli get services \
         --api-key 'YOUR_SKYSQL_API_KEY'
      
    • A YAML configuration file can be used:

      api-key: 'YOUR_SKYSQL_API_KEY'
      

      The default configuration file is $HOME/.skysqlcli.yaml, but a different configuration file can be specified using the --config command-line option.

    • The SKYSQL_API_KEY environment variable can be used:

      $ export SKYSQL_API_KEY='YOUR_SKYSQL_API_KEY'
      

Retrieve Service Information

The following examples retrieve information about SkySQL services.

These types of operations are commonly automated to confirm service configuration, prepare to execute other commands, and to confirm service readiness to receive traffic.

Services List

Similar to the "Your Services" list in the SkySQL Portal, you can retrieve the list of all active services you (or your SkySQL Team) have created.

Detail

Description

CLI Reference

get services

Behavior

  • On success, the command returns JSON with details about each service associated with the SkySQL account.

Command-Line Example

$ skysqlcli get services \
   | jq .

Retrieve Service ID by Service Name

skysqlcli commands related to specific services require that the Service ID be specified. The Service ID can be looked-up from the Service Name.

Detail

Description

CLI Reference

get services

Behavior

Returns JSON with server IDs

Options

  • --name=SERVICE_NAME_PREFIX - returns IDs for names that match the provided prefix

Command-Line Example

$ skysqlcli get services \
   --name 'doc-test-tx-single' \
  | jq '{ "name": .[0].name, "id": .[0].id }'

Sample Response

{
  "name": "doc-test-tx-single",
  "id": "db00000001"
}

Service Status

Service status indicates when a new service is ready to receive connections.

Detail

Description

CLI Reference

get status

Behavior

  • Returns JSON with server status for the service with the specified Service ID.

  • New services remain in the Pending state until the creation process is fully complete.

  • When the status shows "Running", the service is available.

Command-Line Example

For a Service ID of db00000001:

$ skysqlcli get status 'db00000001' \
   | jq .

Sample Response

{
  "status": "Running"
}

Create a Service

The following examples cover activities commonly performed when creating a new SkySQL service.

The use of automation to create services helps ensure repeatability and the correct creation of services of the desired specification.

Create a Database Service

SkySQL enables creation of services with multiple cloud providers and regions, customized to your specifications.

Detail

Description

Detail

Description

CLI Reference

create service

Behavior

Options

  • --name (required) - Specify a name for the new database service

  • --provider (required) - Specify the cloud provider as "AWS" or "GCP"

    • Available providers can be shown using the get providers command

  • --region (required) - Specify a region

    • Available regions can be shown using the get regions command

  • --release-version (required) - Specify a release version

    • Available versions can be shown using the get versions command

  • --replicas (required) - Specify the count of replicas

    • Supported replica counts for each topology can be shown using the get topologies command

    • For a single node topology, specify "0"

    • For a multi-node topology, specify the number of replicas

  • --size (required) - Specify a supported instance size

    • Available sizes can be shown using the get sizes command

  • --storage (required) - Specify the amount of transactional storage in GB

    • For AWS, supported sizes are here

    • For GCP, supported sizes are here

  • --tier (required) - Specify either "Foundation" or "Power", depending on the tier.

    • Available tiers can be shown using the get tiers command

  • --topology (required) - Specify the service topology, which can be "Single Node Transactions", "Replicated Transactions", "Distributed Transactions", "Single Node Analytics", or "Multi-Node Analytics"

  • --volume-iops - Specifies the amount of provisioned IOPS

    • For AWS, specify the provisioned IOPS for the transactional storage

    • For GCP, omit this option

Command-Line Example

For example, create a Single Node Transactions service in AWS using the following options:

$ skysqlcli create service \
   --name 'doc-test-tx-single' \
   --provider 'AWS' \
   --region 'us-east-2' \
   --release-version 'MariaDB Enterprise Server 10.6.4-1' \
   --replicas '0' \
   --size 'Sky-2x8' \
   --storage '100' \
   --tier 'Foundation' \
   --topology 'Single Node Transactions' \
   --volume-iops '100' \
   | jq .

Add IP Address to Allowlist

By default, access to SkySQL services is blocked by a firewall. An IP allowlist specifies which IP addresses are permitted to connect to the service.

Detail

Description

CLI Reference

Behavior

  • Adds the specified addresses to the allowlist to access the service with Service ID SERVICE_ID

  • The specified CLIENT_IP_ADDRESS IPv4 address with SUBNET_MASK subnet mask defines which addresses are allowlisted

  • If CLIENT_IP_ADDRESS references a single IPv4 address, use a SUBNET_MASK of 32

Command-Line Example

For a Service ID of db00000001:

$ skysqlcli create allowed-address 'db00000001' 'CLIENT_IP_ADDRESS/SUBNET_MASK'

To check the status of this operation:

$ skysqlcli get allowlist-status 'db00000001' \
  | jq .

Sample Output

{
  "status": "Enforcing"
}

Obtain Database Credentials

Default credentials are set at time of service creation.

Detail

Description

CLI Reference

get credentials

Behavior

  • Returns JSON with default username and default password.

  • Returned default password is as of time of service launch and does not reflect password changes.

Command-Line Example

For a Service ID of db00000001:

$ skysqlcli get credentials 'db00000001' \
   | jq .

Sample Response

{
  "username": "DB00000001",
  "password": "SKYSQL_DEFAULT_PASSWORD"
}

Obtain Service Connection Details

Service details are used to connect to the database service.

Detail

Description

CLI Reference

get services

Behavior

  • Returns JSON with service details, which can be filtered to those needed to connect to the service.

  • For Single Node Transactions and Single Node Analytics services, the value of read_only_port is always an empty string.

  • If the value of ssl_tls is Enabled, you will need to download the SkySQL CA chain for your chosen cloud provider:

Command-Line Example

For a Service ID of db00000001:

$ skysqlcli get service 'db00000001' \
   | jq '{ "provider": .provider, "host": .fqdn, "read_only_port": .read_only_port, "read_write_port": .read_write_port, "ssl_tls": .ssl_tls }'

Sample Response

{
  "provider": "Amazon AWS",
  "host": "doc-test-tx-single.mdb0000001.db.skysql.net",
  "read_only_port": "",
  "read_write_port": "5001",
  "ssl_tls": "Enabled"
}

Use the Service

After creating a SkySQL service, it is typical to:

Delete a Service

Deletion is non-reversible. Deletion removes the service and all data in the service. Charges for a service stop accruing when the service is deleted.

Ephemeral environments (such as Continuous Integration and testing) are commonly deleted when no longer needed.

Production environments are rarely deleted. Before deleting a service, consider whether production workloads are present.

Detail

Description

CLI Reference

delete service

Behavior

  • Deletes the service with specified Service ID.

Command-Line Example

For a Service ID of db00000001:

$ skysqlcli delete service 'db00000001' \
   | jq .