What are some good approaches for initializing a Scylladb database with docker compose?

Question

I have a micronaut app that starts up with a Scylla database and I want initialize a keyspace and a columnfamily so that people can run the app locally and have it ready to use.

For my initial approach I created a singleton cqlSession and then I create those things there.

@Singleton
public CqlSession cqlSession(ScyllaConfig scyllaConfig) throws UnknownHostException
{
    CqlSessionBuilder sessionBuilder = CqlSession.builder()
            .addContactPoints(scyllaConfig.contactPoints().stream().map(host -> new InetSocketAddress(host, scyllaConfig.port())).toList())
            .withLocalDatacenter(scyllaConfig.datacenter());

    CqlSession session =  sessionBuilder.build();
    session.execute("CREATE KEYSPACE IF NOT EXISTS " + scyllaConfig.keyspaceName() + " WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 3};");
    session.execute("CREATE COLUMNFAMILY IF NOT EXISTS " + scyllaConfig.keyspaceName() + ".payment_data (id uuid PRIMARY KEY, account_id int, person_id int, payment_id text);");
    return session;
}

This doesn't seem like the best approach. I was hoping there would be someway to plugging a cql file or something like that where I could run a bunch of cql and initialize the database, but I can't find anything on how to do that. If worse comes to worse, I could just right a bash script or something like that. Would I just call that from a docker compose file? Is there a better way?

What approaches have you taken? This seems like a common use case so I wanted to get thoughts on best approaches for doing this.

Filefish

Answer 1

Turns out the best way to find answers about scylladb is to ask you search for answers to Cassandra. A good answer is found here: Init script for Cassandra with docker-compose

Filefish

Answer 2

I came here looking for something equivalent to putting schema scripts in the docker-entrypoint-initdb.d directory that is available on MySql images. Turns out that works on some cassandra images like bitnami/cassandra but doesn't work on the standard scylla image.

I took inspiration from this SO post but it didn't work out of the box for scylla so I figured I'd share what I had to do to get it working here:

The easiest way I have found is to create two containers. The first container will spin up the blank scylla db; the second container will populate the db in th first container. The blank db will take some time to initialize and be ready for cqlsh connections so we feed the second container an initialization script that runs a while loop to check and see if it can connect to the first container with cqlsh. Once it can connect, it executes the .cql files to create the schema and populate the db.

How my local directory is structured:

/app
   |_docker_compose.yml
   |_scylla_scripts
         |_create_schema.cql
         |_populate_db.cql
         |_initdb.sh

docker-compose.yml:

services:
  scylla:
    image: scylladb/scylla
    container_name: scylla
    ports:
      - "9042:9042"

  # This is a temporary service that creates the schema and populates the scylla service and then this one shuts down
  scylla-load-keyspace:
    container_name: scylla-load-keyspace
    image: scylladb/scylla
    volumes:
      - ./scylla_scripts/create_schema.cql:/scylla_scripts/1.cql
      - ./scylla_scripts/populate_db.cql:/scylla_scripts/2.cql
      - ./scylla_scripts/initdb.sh:/scylla_scripts/initdb.sh
    entrypoint: [ "bash", "/scylla_scripts/initdb.sh" ]

create_schema.cql:

CREATE KEYSPACE employee_keyspace WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}  AND durable_writes = true;

USE employee_keyspace;

CREATE TABLE employee_keyspace.employee (
     employee_id int PRIMARY KEY,
     email text,
     first_name text,
     last_name text
    );

populate_db.cql:

INSERT INTO employee_keyspace.employee JSON '{ "employee_id": 12345, "email": "[email protected]", "first_name": "John", "last_name": "Doe" }';
INSERT INTO employee_keyspace.employee JSON '{ "employee_id": 12346, "email": "[email protected]", "first_name": "Jane", "last_name": "Doe" }';

initdb.sh:

#!/bin/bash
echo "######### Starting to execute SH script... #########"

# If you have credentials for your DB uncomment the following two lines
#USER_NAME='user_name'
#PASSWORD='user_password'

echo "######### Sleeping for 25 seconds #########"
sleep 25

# If you have credentials for your DB use: while ! cqlsh scylla -u "${USER_NAME}" -p "${PASSWORD}" -e 'describe cluster' ; do
while ! cqlsh scylla -e 'describe cluster' ; do
     echo "######### Waiting for main instance to be ready... #########"
     sleep 5
done

for cql_file in ./scylla_scripts/*.cql;
do
# If you have credentials on your db use this line cqlsh scylla -u "${USER_NAME}" -p "${PASSWORD}" -f "${cql_file}" ;
  cqlsh scylla -f "${cql_file}" ;
  echo "######### Script ""${cql_file}"" executed!!! #########"
done
echo "######### Execution of SH script is finished! #########"
echo "######### Stopping temporary instance! #########"

Chillon

Answer 3

@LogDog23 Thanks for the great idea.

Here's shell script that runs your docker-compose.yml and cleans up the scylla-load-keyspace container and the volume created.

#!/bin/bash

if [ "$1" == "up" ]; then
  docker-compose up -d

  while [ "$(docker inspect -f '{{.State.Running}}' scylla-load-keyspace)" == "true" ]; do
    sleep 5
  done

  volume_names=$(docker inspect scylla-load-keyspace -f '{{range .Mounts}}{{println .Name}}{{end}}')

  docker rm -f scylla-load-keyspace > /dev/null 2>&1

  for volume_name in $volume_names; do
    docker volume rm $volume_name > /dev/null 2>&1
  done
elif [ "$1" == "down" ]; then
  docker-compose down
else
  echo "usage: $0 up|down"
fi

Coatee

Answer 4

You need a CQL script that creates the keyspaces, for testing purposes this would work

CREATE KEYSPACE my_keyspace
    WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}
    AND durable_writes = true;

Then you need to create a init script to call this script, where $@ is used for passing additional arguments to entrypoint call (if you wish)

#!/bin/bash

while ! cqlsh -f /scylla_init/keyspaces.cql; do
  echo "Unavailable: sleeping"
  sleep 10
done

exec /docker-entrypoint.py "$@"

Finally, you just need to copy the scripts into scylla_scripts and create the image in docker compose like this:

volumes:
  - ./scylla_scripts/:/scylla_init/
entrypoint: "/scylla_init/init.sh"

Uzzi