Read this on Signadot, by Emmanuel Oyibo.
Introduction
Testing database schema changes in a shared environment can be tough. When multiple teams rely on the same database, modifying schemas might disrupt others. This challenge becomes critical when adding new columns or altering existing tables.
However, you can overcome this problem. Using Signadot Sandboxes and Resource Plugins, you can create isolated databases for testing. Signadot allows you to spin up temporary databases specifically for your schema changes. This way, you can test modifications without affecting the shared database or impacting other teams.
In this tutorial, we'll show you how to set up database isolation using Signadot. We'll use the HotRod demo application for this tutorial, particularly the location service that interacts with a MySQL database. You can clone the repo to follow along.
You'll learn how to add a new description column to the locations table and test it within an isolated database instance.
By the end, you'll be able to:
- Create a resource plugin to provision a temporary MariaDB database.
- Register and manage the plugin using Signadot.
- Implement and test schema changes in isolation.
- Clean up resources after testing is complete.
Let's get started and see how Signadot can make testing database schema changes easier in a collaborative environment.
Prerequisites
Before we start, make sure:
- Your Kubernetes cluster is connected to Signadot. You can set this up using the Signadot Quickstart Guide.
- The HotRod application is running in your Kubernetes cluster. Deployment instructions are available in the HotRod repository.
Roles and Responsibilities
In this process, two main roles are involved:
1. Platform Team Member
The platform team member sets up the resource plugin that provisions temporary MariaDB instances. They ensure the infrastructure is ready for developers to use isolated databases during testing.
2. Application Developer
The application developer introduces the schema change and updates the code accordingly. They create a sandbox using the resource plugin and test the new feature in isolation.
Understanding these roles helps streamline the workflow. It clarifies who is responsible for each part of the process.
Platform Team Member: Setting Up the MariaDB Resource Plugin
To enable developers to test schema changes safely, set up a resource plugin that provisions temporary MariaDB instances. This plugin allows sandboxes to create and use their own databases without affecting the shared environment.
Creating the Resource Plugin
First, create a resource plugin that provisions a temporary MariaDB server for use within a sandbox. The plugin consists of several components:
1. Plugin Configuration: Defines the plugin's behavior, inputs, outputs, and the scripts to run during provisioning and deprovisioning.
# plugins/mariadb/plugin.yaml
name: mariadb
spec:
description: |
Provision a MariaDB instance
Sandbox should provide input 'dbname' for the name of the database.
Plugin provisioner provides outputs 'host', 'port', 'root-password' for
an empty database instance tied to the lifetime of the sandbox.
runner:
image: dtzar/helm-kubectl
namespace: signadot
podTemplateOverlay: |
spec:
serviceAccountName: sd-mariadb
create:
- name: provision
inputs:
- name: dbname
valueFromSandbox: true
as:
env: DBNAME
outputs:
- name: host
valueFromPath: /tmp/host
- name: port
valueFromPath: /tmp/port
- name: root-password
valueFromPath: /tmp/root-password
script: "@{embed: ./plugin/provision.sh}"
delete:
- name: deprovision
script: "@{embed: ./plugin/deprovision.sh}"
In this configuration:
name: The name of the plugin (mariadb).
spec: Contains the plugin specifications, including a description, runner configuration, and the create and delete steps.
runner: Specifies the Docker image and Kubernetes namespace for running the plugin scripts.
create: Defines the steps to provision the resource. It takes inputs from the sandbox (e.g.,dbname) and provides outputs (e.g.,host,port,root-password).
delete: Specifies the script to run when deleting the resource.
2. Provisioning Script: Contains the logic to deploy a MariaDB instance using Helm.
# plugins/mariadb/plugin/provision.sh
#!/bin/bash
# exit when any command fails
set -e
echo "Provisioning a MariaDB server"
echo "Sandbox id: ${SIGNADOT_SANDBOX_ID}"
echo "Resource name: ${SIGNADOT_RESOURCE_NAME}"
echo "DB name: ${DBNAME}"
# Install bitnami helm chart repo
helm repo add bitnami https://charts.bitnami.com/bitnami
# Deploy a temporary DB for this Sandbox
export NAMESPACE=signadot
RELEASE_NAME="signadot-${SIGNADOT_RESOURCE_NAME,,}-${SIGNADOT_SANDBOX_ID}"
echo "Installing Helm release: ${RELEASE_NAME}"
helm -n ${NAMESPACE} install "${RELEASE_NAME}" bitnami/mariadb \
--version 11.5.0 --wait --timeout 5m0s \
--set fullnameOverride="${RELEASE_NAME}-mariadb" \
--set primary.persistence.enabled=false \
--set serviceAccount.create=false \
--set auth.database="${DBNAME}"
# Get the generated password, based on instructions from the Helm chart.
MYSQL_ROOT_PASSWORD=$(kubectl -n ${NAMESPACE} get secret "${RELEASE_NAME}-mariadb" -o jsonpath="{.data.mariadb-root-password}" | base64 -d)
# Populate the outputs
DBHOST="${RELEASE_NAME}-mariadb.${NAMESPACE}.svc"
echo -n "${DBHOST}" > /tmp/host
echo -n 3306 > /tmp/port
echo -n "${MYSQL_ROOT_PASSWORD}" > /tmp/root-password
This script performs the following actions:
- Adds the Bitnami Helm repository.
- Installs a MariaDB instance using Helm, customized for the sandbox.
- Retrieves the root password from the generated Kubernetes secret.
- Writes the database host, port, and password to temporary files for Signadot to read as outputs.
3. Deprovisioning Script: Handles the cleanup of the MariaDB instance when the sandbox is deleted.
# plugins/mariadb/plugin/deprovision.sh
#!/bin/bash
# exit when any command fails
set -e
echo "Sandbox id: ${SIGNADOT_SANDBOX_ID}"
echo "Resource name: ${SIGNADOT_RESOURCE_NAME}"
# Undeploy the temporary DB for this Sandbox.
export NAMESPACE=signadot
RELEASE_NAME="signadot-${SIGNADOT_RESOURCE_NAME,,}-${SIGNADOT_SANDBOX_ID}"
echo "Deleting Helm release: ${RELEASE_NAME}"
helm -n ${NAMESPACE} uninstall "${RELEASE_NAME}" --wait --timeout 5m0s
This script uninstalls the MariaDB Helm release associated with the sandbox, effectively cleaning up the temporary database.
Configuring Kubernetes Permissions
For the plugin to work correctly, it needs appropriate permissions in the Kubernetes cluster. You'll create a ServiceAccount, Role, and RoleBinding to grant the necessary access.
# plugins/mariadb/k8s/mariadb-init.yaml
apiVersion: v1
kind: ServiceAccount
metadata:
namespace: signadot
name: sd-mariadb
---
apiVersion: rbac.authorization.k8s.io/v1
kind: Role
metadata:
namespace: signadot
name: sd-mariadb
rules:
- apiGroups: [""]
resources: ["pods"]
verbs: ["get", "list", "watch"]
- apiGroups: [""]
resources: ["configmaps", "secrets", "services"]
verbs: ["get", "list", "watch", "create", "delete"]
- apiGroups: ["apps"]
resources: ["statefulsets"]
verbs: ["get", "list", "watch", "create", "delete"]
---
apiVersion: rbac.authorization.k8s.io/v1
kind: RoleBinding
metadata:
namespace: signadot
name: sd-mariadb
subjects:
- kind: ServiceAccount
namespace: signadot
name: sd-mariadb
roleRef:
apiGroup: rbac.authorization.k8s.io
kind: Role
name: sd-mariadb
Apply these configurations to your cluster:
kubectl -n signadot apply -f plugins/mariadb/k8s/mariadb-init.yaml
This sets up the necessary permissions for the plugin to create and manage MariaDB instances within the signadot namespace.
Registering the Plugin with Signadot
Now that the plugin is configured, you need to register it with Signadot so that it can be used in sandboxes.
Use the Signadot CLI to apply the plugin configuration:
signadot resourceplugin apply -f plugins/mariadb/plugin.yaml
This command registers the mariadb plugin with the Signadot control plane. The plugin is now available for use in sandbox specifications.
Application Developer: Testing Schema Changes with an Isolated Database
Now that the platform team has set up the MariaDB resource plugin, you can safely test your schema changes. You'll add a new description field to the locations table, update the application code, create a sandbox using the plugin, and verify your changes—all without affecting the shared database.
Introducing the Schema Change
Let’s assume you need to add a description column to the locations table in the HotRod application. This field will store extra information about each location. Testing this change on the shared database could disrupt other teams. Therefore, you'll use an isolated database for testing.
You can clone the Hotrod repository to follow along.
Making Code Changes
First, update the Location struct in the interface.go file of the location service to include the new Description field:
// hotrod/services/location/interface.go
type Location struct {
ID int64 `json:"id"`
Name string `json:"name"`
Coordinates string `json:"coordinates"`
Description string `json:"description"` // New field
}
Next, modify the database.go file to handle the description field throughout the database interactions.
Updating the Table Schema
In database.go, update the tableSchema constant to add the description column:
// hotrod/services/location/database.go
const tableSchema = `
CREATE TABLE IF NOT EXISTS locations
(
id bigint unsigned NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
coordinates varchar(255) NOT NULL,
description varchar(255), -- New column
PRIMARY KEY (id),
UNIQUE KEY name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
`
Updating Seed Data
Update the seed variable to include descriptions for each location:
// hotrod/services/location/database.go
var seed = []Location{
{
ID: 1,
Name: "My Home",
Coordinates: "231,773",
Description: "Cozy two-bedroom apartment", // New field
},
{
ID: 123,
Name: "Rachel's Floral Designs",
Coordinates: "115,277",
Description: "Flower shop specializing in arrangements",
},
{
ID: 567,
Name: "Amazing Coffee Roasters",
Coordinates: "211,653",
Description: "Local coffee roaster with a variety of blends",
},
{
ID: 392,
Name: "Trom Chocolatier",
Coordinates: "577,322",
Description: "Artisan chocolate and confectionery",
},
{
ID: 731,
Name: "Japanese Desserts",
Coordinates: "728,326",
Description: "Traditional Japanese sweets and pastries",
},
}
Modifying Insert and Update Queries
Update the Create and Update methods to include the description field.
// hotrod/services/location/database.go
func (d *database) Create(ctx context.Context, location *Location) (int64, error) {
query := "INSERT INTO locations SET name = ?, coordinates = ?, description = ?"
res, err := d.db.Exec(query, location.Name, location.Coordinates, location.Description)
if err != nil {
if !d.shouldRetry(err) {
return 0, err
}
res, err = d.db.Exec(query, location.Name, location.Coordinates, location.Description)
if err != nil {
return 0, err
}
}
id, err := res.LastInsertId()
if err != nil {
return 0, err
}
return id, nil
}
func (d *database) Update(ctx context.Context, location *Location) error {
query := "UPDATE locations SET name = ?, coordinates = ?, description = ? WHERE id = ?"
res, err := d.db.Exec(query, location.Name, location.Coordinates, location.Description, location.ID)
if err != nil {
if !d.shouldRetry(err) {
return err
}
res, err = d.db.Exec(query, location.Name, location.Coordinates, location.Description, location.ID)
if err != nil {
return err
}
}
n, err := res.RowsAffected()
if err != nil {
return err
}
if n != 1 {
return fmt.Errorf("wrong number of rows on update: %d != 1", n)
}
return nil
}
Adjusting Select Queries
Modify the List and Get methods to retrieve the description field.
// hotrod/services/location/database.go
func (d *database) List(ctx context.Context) ([]Location, error) {
d.logger.For(ctx).Info("Loading locations", zap.String("location-id", "*"))
// Simulate opentracing instrumentation of an SQL query
_, span := d.tracer.Start(ctx, "SQL SELECT", trace.WithSpanKind(trace.SpanKindClient))
span.SetAttributes(
semconv.PeerServiceKey.String("mysql"),
attribute.Key("sql.query").String("SELECT id, name, coordinates, description FROM locations"),
)
defer span.End()
query := "SELECT id, name, coordinates, description FROM locations"
rows, err := d.db.Query(query)
if err != nil {
if !d.shouldRetry(err) {
return nil, err
}
rows, err = d.db.Query(query)
if err != nil {
return nil, err
}
}
defer rows.Close()
var cs []Location
for rows.Next() {
c := Location{}
if err := rows.Scan(&c.ID, &c.Name, &c.Coordinates, &c.Description); err != nil {
return nil, err
}
cs = append(cs, c)
}
if err := rows.Err(); err != nil {
return nil, err
}
return cs, nil
}
func (d *database) Get(ctx context.Context, locationID int) (*Location, error) {
d.logger.For(ctx).Info("Loading location", zap.Int("location_id", locationID))
_, span := d.tracer.Start(ctx, "SQL SELECT", trace.WithSpanKind(trace.SpanKindClient))
span.SetAttributes(
semconv.PeerServiceKey.String("mysql"),
attribute.Key("sql.query").String(fmt.Sprintf("SELECT id, name, coordinates, description FROM locations WHERE id = %d", locationID)),
)
defer span.End()
// if !config.MySQLMutexDisabled {
// // simulate misconfigured connection pool that only gives one connection at a time
// d.lock.Lock(ctx)
// defer d.lock.Unlock()
// }
// simulate RPC delay
delay.Sleep(config.GetMySQLGetDelay(), config.GetMySQLGetDelayStdDev())
var c Location
query := "SELECT id, name, coordinates, description FROM locations WHERE id = ?"
row := d.db.QueryRow(query, locationID)
if row.Err() != nil {
if !d.shouldRetry(row.Err()) {
return nil, row.Err()
}
row = d.db.QueryRow(query, locationID)
if row.Err() != nil {
return nil, row.Err()
}
}
if err := row.Scan(&c.ID, &c.Name, &c.Coordinates, &c.Description); err != nil {
return nil, err
}
return &c, nil
}
Creating the Sandbox Specification
Now, create a sandbox specification that uses the MariaDB plugin to provision an isolated database for testing your schema changes. Use the following sandbox.yaml file:
# sandbox.yaml
name: hotrod-schema-change
spec:
cluster: "@{cluster}"
resources:
- name: mydb
plugin: mariadb
params:
dbname: hotrod
defaultRouteGroup:
endpoints:
- name: location
target: "http://location.hotrod.svc:8081"
- name: frontend
target: "http://frontend.hotrod.svc:8080"
forks:
- forkOf:
namespace: hotrod
name: location
kind: Deployment
customizations:
images:
- image: <your_repo>/hotrod:test
container: hotrod
env:
- name: DB_HOST
valueFrom:
resource:
name: mydb
outputKey: provision.host
- name: DB_PORT
valueFrom:
resource:
name: mydb
outputKey: provision.port
- name: DB_ROOT_PASSWORD
valueFrom:
resource:
name: mydb
outputKey: provision.root-password
- name: DB_DATABASE
value: "hotrod"
Applying the Sandbox
Use the Signadot CLI to create the sandbox:
signadot sandbox apply -f sandbox.yaml --set cluster=<your-cluster-name>
Replace <your-cluster-name> with the name of your Kubernetes cluster. Ensure that you have built and pushed your custom location service Docker image to a container registry accessible by your cluster.
Testing the End-to-End Flow
With your sandbox created, it's time to test your schema changes in the HotRod application. There are two primary ways to test the new description field:
- Using
curlwithsignadot local connectto interact directly with the location service. - Previewing through the frontend using the Signadot Chrome Extension.
Let’s explore both options below:
Option 1: Testing via curl and signadot local connect
Since the location service is not exposed externally, you'll need to use signadot local connect to route traffic to it from your local machine. Additionally, you must include the sandbox routing key in your request headers.
Step 1: Start Signadot Local Connect
Start the local connection to your cluster:
signadot local connect
Step 2: Obtain the Sandbox Routing Key
Retrieve the sandbox routing key, which is used to route requests to your sandboxed services. You can find this key on your Signadot UI.
Step 3: Send a Request to the location Service
Use curl to send a POST request to the location service within the sandbox. You'll need to:
- Use the service's cluster-local DNS name.
- Include the routing key in the request headers.
curl -X POST http://location.<namespace>.svc:8081 \
-H "Content-Type: application/json" \
-H "Baggage: sd-routing-key=<routing-key>" \
-d '{
"name": "Central Park",
"coordinates": "400,500",
"description": "A large public park in New York City"
}'
Replace with the actual routing key value. You should get an output similar to below:
{
"id": 732,
"name": "Central Park",
"coordinates": "400,500",
"description": "A large public park in New York City"
}
Step 4: Retrieve All Locations
Send a GET request to list all locations:
curl http://location.<namespace>.svc:8081/locations \
-H "Baggage: sd-routing-key=<routing-key>"
Your output should be similar:
[
{
"id": 1,
"name": "My Home",
"coordinates": "231,773",
"description": "Cozy two-bedroom apartment"
},
{
"id": 123,
"name": "Rachel's Floral Designs",
"coordinates": "115,277",
"description": "Flower shop specializing in arrangements"
},
{
"id": 392,
"name": "Trom Chocolatier",
"coordinates": "577,322",
"description": "Artisan chocolate and confectionery"
},
{
"id": 567,
"name": "Amazing Coffee Roasters",
"coordinates": "211,653",
"description": "Local coffee roaster with a variety of blends"
},
{
"id": 731,
"name": "Japanese Desserts",
"coordinates": "728,326",
"description": "Traditional Japanese sweets and pastries"
}
]
Option 2: Previewing Through the Frontend Using the Chrome Extension
An alternative and user-friendly way to test your changes is by interacting with the application through its frontend using the Signadot Chrome Extension. This method leverages the existing UI and automatically handles the necessary routing headers.
Step 1: Install the Signadot Chrome Extension
Download from the Chrome Web Store: Signadot Chrome Extension. Install the extension in your Chrome browser.
Step 2: Log In to Signadot
Ensure you're logged into your Signadot account at app.signadot.com. The extension requires authentication to access your organization's sandboxes.
Step 3: Enable the Extension
Click on the Signadot extension icon in Chrome. Select your sandbox from the list. Toggle the extension to the "ON" position.
Step 4: Access the Frontend Application
Navigate to the frontend URL: https://frontend..svc:8080. The Chrome extension injects the necessary routing headers, ensuring your requests are directed to the sandboxed services.
Verifying Database Schema Changes
Regardless of the testing method used, you may want to confirm that the database schema has been updated correctly. You can connect to the temporary MariaDB instance provisioned by the plugin.
You can connect using the MySQL client. Open a new terminal window and connect using the mysql client:
mysql -h <db-host> -P <db-port> -u root -p
- Replace
<db-host>with the value fromprovision.host. - Replace
<db-port>with the value fromprovision.port. - Enter the
root-passwordwhen prompted.
Once connected, select the hotrod database and describe the locations table:
USE hotrod;
DESCRIBE locations;
You should see that the locations table includes the new description column:
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | UNI | NULL | |
| coordinates | varchar(255) | NO | | NULL | |
| description | varchar(255) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
From the above, you can see that:
- The
descriptioncolumn has been successfully added to thelocationstable schema. - The column allows
NULLvalues (YESin theNullcolumn), which aligns with your schema definition. - The table now supports storing additional information for each location through the
descriptionfield.
Deleting the Sandbox
After testing, clean up the resources by deleting the sandbox:
signadot sandbox delete hotrod-schema-change
This command removes the sandbox and triggers the deprovision.sh script to delete the temporary database.
Conclusion
Testing database schema changes in a shared environment can be challenging and risky. By using Signadot Sandboxes and Resource Plugins, you can create isolated databases for testing. This allows you to make schema changes without impacting the shared database or affecting other teams.
In this tutorial, you set up a MariaDB resource plugin to provision a temporary database. You updated the HotRod application's location service by adding a new description field and tested these changes in an isolated sandbox. This approach enables you to safely implement and test schema changes, improving your development workflow.