I needed to have a Redis server available as part of the GitLab CI pipeline for this blog (simply because I wanted to use the {rredis}
package). After fiddling around for some time trying to install the redis-server
package using apt
I discovered that GitLab CI actually provides Redis as a service, which makes the process remarkably easy.
Some details of the “standard” services (Redis, PostgreSQL and MySQL) supported by GitLab CI can be found here:
Below are simple examples of using each service.
However, as we’ll see, you are not limited to these services. You can easily specify another process as a service too.
Redis
Here’s the .gitlab-ci.yml
setup. The base image is rocker/tidyverse
and the latest Redis image is specifed as a service.
image: rocker/tidyverse:4.0.3
services:
- redis:latest
variables:
REDIS_HOST: redis
before_script:
- R -e "install.packages('rredis')"
test:
script:
- Rscript test-redis.R
Now in the test-redis.R
script we connect to the service, insert a value for the key "r_version"
and then retrieve that value.
library(rredis)
REDIS_HOST <- Sys.getenv("REDIS_HOST", "localhost")
redisConnect(host = REDIS_HOST)
redisSet("r_version", R.version$nickname)
redisGet("r_version")
Check out the example repository here.
PosgreSQL
What about a PosgreSQL service? Here’s the .gitlab-ci.yml
setup. This time we’re using a Python base image and giving the latest PosgreSQL image as a service.
image: python:3.8.7
services:
- postgres:latest
variables:
POSTGRES_DB: db
POSTGRES_USER: postgres
POSTGRES_PASSWORD: password
before_script:
- pip install psycopg2
test:
script:
- python test-postgres.py
In the test-postgres.py
script we connect to the service, create a table, insert a couple of records and then retrieve them.
import os
import psycopg2
POSTGRES_HOST = os.environ.get('POSTGRES_HOST', 'postgres')
POSTGRES_DB = os.environ['POSTGRES_DB']
POSTGRES_USER = os.environ['POSTGRES_USER']
POSTGRES_PASSWORD = os.environ['POSTGRES_PASSWORD']
# Connect to the database.
#
db = psycopg2.connect(
host=POSTGRES_HOST,
database=POSTGRES_DB,
user=POSTGRES_USER,
password=POSTGRES_PASSWORD
)
# Create a cursor.
#
cursor = db.cursor()
# Create a table.
SQL = """
CREATE TABLE vendors (
vendor_id SERIAL PRIMARY KEY,
vendor_name VARCHAR(255) NOT NULL
)
"""
cursor.execute(SQL)
# Insert data.
SQL = "INSERT INTO vendors (vendor_name) VALUES (%s);"
cursor.execute(SQL, ('Red Hat',))
cursor.execute(SQL, ('Canonical',))
# Query data.
SQL = "SELECT * from vendors ORDER BY vendor_name;"
cursor.execute(SQL)
print("Number of results: ", cursor.rowcount)
row = cursor.fetchone()
#
while row is not None:
print(row)
row = cursor.fetchone()
# Close the cursor and the connection.
#
cursor.close()
db.close()
Check out the example repository here.
MySQL
Perhaps you need MySQL rather than PostgreSQL? Here’s the .gitlab-ci.yml
setup. We’re back to the rocker/tidyverse
image again.
image: rocker/tidyverse:4.0.3
services:
- mysql:latest
variables:
MYSQL_DATABASE: db
MYSQL_ROOT_PASSWORD: password
before_script:
- R -e "install.packages(c('dbplyr', 'RMariaDB', 'nycflights13'))"
test:
script:
- Rscript test-mysql.R
In the test-mysql.R
script we connect to the database service, create a table and populate it with data, then retrieve a subset of columns from the table.
library(dplyr)
MYSQL_HOST <- Sys.getenv("MYSQL_HOST", "mysql")
MYSQL_DATABASE <- Sys.getenv("MYSQL_DATABASE")
MYSQL_USER <- Sys.getenv("MYSQL_USER", "root")
MYSQL_PASSWORD <- Sys.getenv("MYSQL_ROOT_PASSWORD")
db <- DBI::dbConnect(
RMariaDB::MariaDB(),
host = MYSQL_HOST,
dbname = MYSQL_DATABASE,
user = MYSQL_USER,
password = MYSQL_PASSWORD
)
# Create a table and populate with data.
#
copy_to(
db,
nycflights13::flights,
"flights",
temporary = FALSE,
indexes = list(
c("year", "month", "day"),
"carrier",
"tailnum",
"dest"
)
)
# Extract a subset of columns from the table.
#
tbl(db, "flights") %>% select(year:day, dep_delay, arr_delay)
DBI::dbDisconnect(db)
Check out the example repository here.
Mongo
What if you need some other service, like MongoDB? No problem: just specify the image that you need in the services
section and you’re ready to roll.
Here we use the mongo:latest
image to provide a MongoDB service.
image: rocker/tidyverse:4.0.3
services:
- mongo:latest
before_script:
- R -e "install.packages('mongolite')"
test:
script:
- Rscript test-mongo.R
And here’s the contents of test-mongo.R
, which connects to the MongoDB service and performs some simple operations.
library(mongolite)
db <- mongo(url = "mongodb://mongo")
# Wipe existing data.
#
if(db$count() > 0) db$drop()
# Insert data.
#
db$insert(mtcars)
stopifnot(db$count() == nrow(mtcars))
# Query data.
#
cars <- db$find()
stopifnot(all.equal(cars, mtcars))
db$drop()
rm(db)
Check out the example repository here.
Conclusion
The ability to provide services makes it really easy to incorporate a database into your GitLab CI pipeline. Also, although each of the examples above has used only a single service, there’s no reason that you should not attach more than one service at the same time.