Databases and DevOps: How to Use SingleStore With GitHub Actions

It's very important to test your application with the same database that you run in production. In this blog post, we will explain how to set up SingleStoreDB to run on your machine and within your Github Actions workflows.

This post is for developers that want to learn best practices for integrating databases into your development and deployment process. We will also walk through how to set up SingleStoreDB to run within your GitHub Actions workflows.

What Are Technical Best Practices for Databases and DevOps?

First of all, let’s cover best practices for databases and DevOps:

Test!

This is a no brainer. You should be testing your database schema and queries every time you make changes. You need to make sure the components that house your data, will not compromise or lose any data by not testing your application thoroughly. I have seen databases neglected when it comes to testing — and often, it comes down to the job of a single developer who manually tests and deploys each build. It doesn’t need to be like that… Test!

Developers need a way to easily create local databases

Right off the bat, it needs to be easy for everyone on the team to set up databases either locally, in a cloud sandbox environment or both! Here’s where containers come to the rescue. Containers are a good way to practice, they’re easy and cheap to set up, and most importantly, if something goes wrong you can throw everything out and start over again. Your team needs to easily develop in a non-shared environment to ensure everything is working correctly.

The database schema — including all indexes — needs to be in source control

If developers need to create local builds of the database, that also means that all components that shape the database or control how it performs business logic need to be maintained using source control. Maintaining these changes can be simplified by making sure all changes are performed using migrations.

Practice in a production-like environment

Everyone on the team should be able to develop and test out database code in a production-like database environment before pushing out changes. Trust me, you would rather have one of your developers topple over a staging environment than the production environment. This environment should also be simple to take down, and set up again.

You need to test a change before applying it to a production environment. If the table data is huge — so huge that it would be costly to replicate it in a different environment from production — make sure you can at least simulate the change with a significant set of data. This will help ensure the change won’t take forever, and you won’t be blocking a table for a long period of time.

Be sure to monitor database systems for performance and compliance

Did you know you can automate this? Like any good CI/CD pipeline, all the important business logic should be thoroughly tested and automated. This ensures that any changes you make to your database environment won’t break the build, your user's trust or the law. Be sure that you are taking into account regional differences and regulatory requirements.

Microservices are a good way to decouple the database

The only way other microservices interact with the data is by using the exposed methods from the service, rather than going directly to the database — even if it’s possible and “easier” to do it that way. This can make testing and deploying dangerous database changes (like alters) easier, since only one service needs to be directly tested against the database.

Let’s get into the code

Now that we've discussed why you should integrate databases into your development and deployment process, let's talk about how to do it with SingleStoreDB.

Running SingleStoreDB locally

SingleStoreDB can be run locally using Docker or any other docker-compatible container platform. For local development, we recommend using the singlestoredb-dev-image which you can find on github here.

To use this image, just follow these steps:

Get a license key for SingleStoreDB. You can sign up for a license here and then get your license key from Customer Portal.
Define environment variables for your license key and the password you want to use to login to SingleStore

SINGLESTORE_LICENSE="YOUR LICENSE KEY"
SINGLESTORE_PASSWORD="PASSWORD"

3. Run the Docker container

docker run \
-d --name singlestoredb-dev \
    -e SINGLESTORE_LICENSE="${SINGLESTORE_LICENSE} " \
    -e ROOT_PASSWORD="${SINGLESTORE_PASSWORD} " \
    --platform linux/amd64 \
    -p 3306:3306 -p 8080:8080 -p 9000:9000 \
    ghcr.io/singlestore-labs/singlestoredb-dev:latest

Assuming nothing went wrong, SingleStoreDB will now be running on your machine. You can check the status of the container by running:

docker ps --filter name=singlestoredb-dev

You can check the logs of the container by running:

docker logs singlestoredb-dev

To learn how to query SingleStoreDB and access the included SingleStore Studio UI, please read the documentation here.

Running SingleStoreDB in Github Actions

After setting up SingleStoreDB locally and getting it working with your application, it's time to also get it running in Github Actions to ensure that you are building, testing and deploying with the same database technology throughout. Using the same Docker image we used above, we can easily run it in Github Actions via these steps:

Get a license key for SingleStoreDB. You can sign up for a license here and get your license key from Customer Portal. If you have a license key for local development, you can re-use that license key here.
Next, you need to create an encrypted secret in Github Actions called SINGLESTORE_LICENSE which contains your license key as the value.
Finally, you simply need to add a service container definition to your Github Actions workflow. As an example, here is a simple Github Actions workflow which runs SingleStore and then runs a simple query against it (select 1):

name: my-workflow
on: push
jobs:
  my-job:
    runs-on: ubuntu-latest
    needs: build-image

    services:
      singlestoredb:
        image: ghcr.io/singlestore-labs/singlestoredb-dev
        ports:
          - 3306:3306
          - 8080:8080
          - 9000:9000
        env:
          ROOT_PASSWORD: test
          SINGLESTORE_LICENSE: ${{secrets.SINGLESTORE_LICENSE} } steps:
      - name: sanity check using mysql client
        run: |
          mysql -u root -ptest -e "SELECT 1" -h 127.0.0.1

And that is it! Now you can relax and commit your code and migrations as your CI/CD workflows are carried out automatically.