1

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.

testing123
  • 11,367
  • 10
  • 47
  • 61

2 Answers2

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

testing123
  • 11,367
  • 10
  • 47
  • 61
0

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": "johndoe@example.com", "first_name": "John", "last_name": "Doe" }';
INSERT INTO employee_keyspace.employee JSON '{ "employee_id": 12346, "email": "janedoe@example.com", "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! #########"
LogDog23
  • 236
  • 3
  • 7