Codespaces with databases

With GitHub Codespaces you can set up a cloud-hosted, containerized VS Code environment. You can then connect to a codespace through the browser or through VS Code.

The main question we are trying to answer now is:

Can we have virtualized environments with databases created from backups or scripts, not just empty databases, or fake data sets?

Codespaces lets you:

• Set up your instant dev environment
• Run your app from a codespace container
• Use VS Code or your browser to connect and work against your codespace environment
• Use lifecycle steps to install the dependencies you need to run your app
• Make and commit changes from your codespace

With codespaces you can have databases by running them in docker containers next to your application. This is a good first start but you won't be able bring any volume of data, and this reduces the amount of realistic testing you can do.

Let's now explore how we could use Codespaces with databases and data.

Spawn is a cloud-hosted service that quickly provides databases for dev and test. You can spin up full database instances in seconds with GBs of data and it also allows you to do operations such as reset with no extra time waiting for restores to finish. Spawn can fit nicely with a codespaces setup, in a similar way to the dev containers set up we've discussed in the past.

The code for this article is available here: https://github.com/red-gate/spawn-demo, you will need to have a spawn account as well.

Architecture

Our demo app is a React frontend application, that connects to a dotnet API. The API uses 2 different databases, a SQL Server database for the information about the user accounts and second PostgreSQL database for holding the information about the todo tasks. And here is the diagram of how things will fit in with codespaces:

Setup your codespace

Our .devcontainer/devcontainer.json will look like this to start:

{

"name": "Spawn Demo app codespaces",

"build": {

"dockerfile": "Dockerfile"

},

"extensions": [

"ms-dotnettools.csharp"

],

"forwardPorts": [

5050,

3000

],

"remoteEnv": {

"SPAWNCTL_ACCESS_TOKEN": "${localEnv:SPAWNCTL_ACCESS_TOKEN}"

},

"initializeCommand": "",

"onCreateCommand": [

"${containerWorkspaceFolder}/codespaces-start.sh"

],

"updateContentCommand": [

"${containerWorkspaceFolder}/codespaces-build.sh"

],

"postCreateCommand": "",

"postStartCommand": "",

"postAttachCommand": "",

}

The main components to pick up here are:

• a reference to our own .devcontainer/Dockerfile
• 2 ports opens, one for our frontend and one for the API
• a secret token for connecting with spawn
• 2 commands codespaces-start.sh and codespaces-build.sh to prepare our environment

Installing dependencies

As shown in the diagram, through .devcontainer/Dockerfile , we'll install flyway and spawnctl to manage our database migrations as well as creating databases on the fly for running our app.

# Install spawnctl

RUN curl https://run.spawn.cc/install | sh && ln -s $HOME/.spawnctl/bin/spawnctl /usr/local/bin

# Install Flyway

RUN wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/7.0.2/flyway-commandline-7.0.2-linux-x64.tar.gz | \

tar xvz && sudo ln -s `pwd`/flyway-7.0.2/flyway /usr/local/bin

We will also set up databases and start them up and update API appsettings:

if [[ -z $NEW_CONTAINERS ]] && [[ $doContainersExist == 0 ]]; then

logSpawnMessage "Containers found - reusing existing Spawn containers"

else

logSpawnMessage "No containers found - creating new Spawn containers"

echo

spawnctl create data-container --image $SPAWN_TODO_IMAGE_NAME --name "$todoContainerName" -q > /dev/null

echo

spawnctl create data-container --image $SPAWN_ACCOUNT_IMAGE_NAME --name "$accountContainerName" -q > /dev/null

fi

updateDatabaseAppSettings "$todoContainerName" "$accountContainerName"

Setting up secrets

Follow the Github docs on: Managing encrypted secrets for your codespaces and set up your secrets, in our case we need to set up a Spawn token named SPAWNCTL_ACCESS_TOKEN this will then be available as an environment variable in your codespace environment.

Troubleshooting codespaces

When starting your codespace you can see the logs by running the View Creation Log command

Which will show you all logs and commands defined under .devcontainer/devcontainer.json

Your codespace is ready

Once your Codespace is running, all dependencies and code are ready to be used. spawnctl is already installed and hooked up with the SPAWNCTL_ACCESS_TOKEN so you don't even need to auth!

spawnctl get dc

ID Name Engine

120511 demo-todo-codespaces PostgreSQL:11.0

120512 demo-account-codespaces MSSQL:2017-CU20-ubuntu-16.04

Notice our 2 databases (PostgreSQL and MSSQL) needed to run the demo-todo app are there. Also, if we check our API appsettings, it has been populated with the necessary connection strings for connecting to these 2 databases.

Under api/Spawn.Demo.WebApi/appsettings.Development.Database.json You'll see

{

"TodoDatabaseConnectionString": "Host=instances.spawn.cc;Port=....;Database=spawndemotodo;User Id=spawn_admin_ZZZ;Password=.....;",

"AccountDatabaseConnectionString": "Server=instances.spawn.cc,....;Database=spawndemoaccount;User Id=sa;Password=.....;"

}

You'll notice 2 ports are open and ready but no processes are running yet.

Just hit F5 - Running the spawn-demo

Once inside your codespace we can now hit F5 to run. This will prepare the databases (in this case as databases already exist only migrations need to be run). In this scenario migrations are up to date so none will be applied.

🛸 Checking if Spawn containers already exist

🛸 Containers found - reusing existing Spawn containers

🛸 Updating '/workspaces/spawn-demo/api/Spawn.Demo.WebApi/appsettings.Development.Database.json' with data container connection strings

🛸 '/workspaces/spawn-demo/api/Spawn.Demo.WebApi/appsettings.Development.Database.json' successfully updated with data container connection string

🛸 Successfully provisioned Spawn containers. Ready to start app

📝 Starting migration of 'Account' database

Flyway Community Edition 7.0.2 by Redgate

Database: jdbc:sqlserver://instances.spawn.cc:...

Successfully validated 2 migrations (execution time 00:00.124s)

Current version of schema [dbo]: 1.002

Schema [dbo] is up to date. No migration necessary.

📝 Starting migration of 'Todo' database

Flyway Community Edition 7.0.2 by Redgate

Database: jdbc:postgresql://instances.spawn.cc:...

Successfully validated 3 migrations (execution time 00:00.070s)

Current version of schema "public": 1.003

Schema "public" is up to date. No migration necessary.

📝 Finished migrating both databases

After this the backend and frontend will start up and you should see 2 processes running on each of our ports

And if we browse to our frontend, our app is there, hitting the API and the different databases.

Testing Spawn superpowers

I created a new todo item from codespaces which will be present in the database as well. We can now test our reset feature of Spawn, to get back to the defaul data that comes with the image.

spawn-demo (codespaces) $ spawnctl get dc

ID Name Engine

120511 demo-todo-codespaces PostgreSQL:11.0

120512 demo-account-codespaces MSSQL:2017-CU20-ubuntu-16.04

spawn-demo (codespaces) $

spawn-demo (codespaces) $ spawnctl reset dc demo-todo-codespaces

Successfully reset data container 'demo-todo-codespaces'

spawn-demo (codespaces) $

After performing our reset operation, notice our item is now gone and we are back to the default state of the database.

Conclusion

Codespaces gives you live environments that you can customize depending on your application needs. And Spawn bridges the gap and brings databases along with it. It brings dedicated, isolated database instances with the data you define. With this setup, you can add data, modify the database schema without impacting your team and your codespace environment. Have a look at our Github repo https://github.com/red-gate/spawn-demo to see how we set up our project with Codespaces and Spawn. You can start using Spawn for free today, and request early access to Codespaces to give it a try.