docker: Create default database

[bdarnell]

The docker images for postgres and mysql support environment variables (POSTGRES_DB and MYSQL_DATABASE) that will cause a default database to be created (among other things; looks like a user can be created too). We may want to allow similar automatic configuration for use in testing.

Epic CRDB-1424

[rio]

As I see it, it is usually done through some bash scripts that get run as part of the ENTRYPOINT in docker.
Looks like /cockroachdb/cockroach.sh is a good place to set it up. Not sure how it works in clustered mode though.

Mysql does some sort of double boot of mysql, starts it in the background, does the setup magic against that instance, stops it, and starts the real thing.

[jhayotte]

As there is no clear workaround, I add mine. It allows me to define a COCKROACH_DB or COCKROACH_DATABASE at startup in a docker-compose. Pretty handy when you run your test of integration with docker-compose.

docker-compose

article_cockroachdb:
    image: mycorp/cockroach
    command: start --insecure
    ports:
      - "8080"
      - "26257"
    restart: unless-stopped

  article-cockroachdb-init:
    image: mycorp/cockroach
    entrypoint: /cockroach/init.sh 
    command: article
    depends_on:
      - article_cockroachdb
    restart: on-failure

init.sh

#!/bin/sh

host=$1_cockroachdb

./cockroach sql --insecure --host="$host" --execute="  DROP USER IF EXISTS cockroach; \
                                        DROP DATABASE IF EXISTS $1 CASCADE; \
                                        CREATE DATABASE IF NOT EXISTS $1; \
                                        CREATE USER cockroach WITH PASSWORD 'password'; \
                                        GRANT ALL ON DATABASE $1 TO cockroach;"

Docker file

FROM cockroachdb/cockroach:v1.1.4

COPY init.sh /cockroach 

Either you create your own Docker image with this init.sh or you mount a volume in article-cockroachdb-init.

Hope it will help.

[knz]

cc @dianasaur323 for prioritization

[dianasaur323]

since this is docker, let's have @kannanlakshmi take a look

[a-robinson]

We could make this sort of thing work for the single-node case, but it can't really work for the multi-node case given the inability to create DB/users on an uninitialized cluster.

To the folks that are commenting/upvoting this:

1. Do you still want this or have you all worked around it by now?
2. Are you all using insecure mode?
3. Are you all using a single-node deployment or at least not using cockroach init?
4. Do you want control over anything more than a database, user, and password?

[knz]

Note that we're also shipping #24735 in 2.1 which will provide two predefined dbs in all cases. Maybe this will also help?

[jhayotte]

@knz Your pull request will definitely help! Thx for it!
Just one thing, IMHO you don't cover one important use case:

As a developer I want to run my test of integration in a specific cockroach DB with a docker_compose.

In this scenario, you need to define in your docker-compose a COCKROACH_DB (e.g article)

As mention earlier, this use case occurs when you run a single instance of crdb. When you run your test locally, this is the case :)

[saward]

Not exactly this issue, but I couldn't find a way to initialise cockroach easily with arbitrary SQL commands for purposes of testing. I therefore created a simple script that will run the set of SQL files contained within a specified folder. You can use this to create databases as well as initialise that database with whatever structure and data you wish (source code on github as linked from repo):

https://hub.docker.com/r/episub/cockroach-init/

If there already exists a way to do this, please let me know.

[tim-o]

Zendesk ticket #2296 has been linked to this issue.

[inieves]

The solutions I see here are correct, but they seem heavyweight to me. In general in other people's code I read I also see Docker being used in some heavyweight ways to work around challenges that are created by various images.

In the case of cockroach, I have the same issue... I want to run an init script immediately after db creation. Yes, I realize that in true cluster mode (>1 node) this does not make immediate sense... Because you would not want to run your init script on every node.

I would argue that the developer should be able to init a db or a cluster of db nodes, using the same script or function call. The implementation may change (depending on if single node or multi-node), but the intended outcome remains the same.. The developer needs the db/cluster to have a particular state immediately after creation. This is so clearly necessary, the cockroach start command should have a command line flag to specify a shell script to execute or perhaps an SQL command to execute.

Anyways... here is a somewhat simpler solution requiring only docker-compose:

db_cockroach:
    ......
    command: shell -c '/etc/cockroach/conf/init-user-db.sh & /cockroach/cockroach start --certs-dir=/etc/cockroach/certs;'

You simply fork (using "&") your init script before you launch cockroach... BUT, you must make sure your init script is efficiently polling the port that cockroach will be listening on when it wakes up... so that when cockroach actually wakes up, your init script can run.

I made use of the "nc" program a.k.a. netcat to poll... and my solution (init-user-db.sh) looks like:

#!/bin/bash
set -e
while ! nc -w 1 -z localhost 26257; do sleep 0.1; done;
/cockroach/cockroach sql --certs-dir=/etc/cockroach/certs --user=root --execute="
	CREATE USER IF NOT EXISTS $COCKROACH_USER;
	CREATE DATABASE IF NOT EXISTS $COCKROACH_DATABASE;
	GRANT CREATE, SELECT, DROP, INSERT, DELETE, UPDATE ON DATABASE $COCKROACH_DATABASE TO $COCKROACH_USER;
"

notice nc is used in a loop, whereby it checks to see if any process is listening on the cockroach port, then sleeps for 1/10 of a second.

This solution does not require any new Docker containers to be created... it basically adds one line to your init script, requires you to apt-get install netcat, and adds a little more muck to your docker-compose command.

In my final analysis, to bring cockroach up to speed with mysql/maria/postgres, it should support the ability to create an initial non-root user and an initial database (both with the name of the users choosing). It should also allow the user to supply an init script. There is a best practice for doing this in Docker... see what mysql/maria/postgres did. cockroach could also address the issue for non-Docker situations by allowing the user to supply cockroach start with a command line flag that specifies an init shell script or SQL script. All this can be done in a way that achieves the developers intended result regardless of single-node or multi-node.

It is worth mentioning that this solution is very similar to the classic Docker/nginx/database problem of: "How can I make my webserver (nginx) accept incoming requests only after the db is up and listening for new connections?"
The solution is equivalent (in both beauty and ugliness)... poll with netcat.

Also worth noting, I attempted various other "one line" solutions (one line in docker-compose command), and encountered various problems. I tried to start cockroach in the background (with --background), then run my init script, then kill cockroach, then start cockroach in the foreground... I think there was a timing conflict between the kill and start, or between the init script and the kill. It was not looking like a promising path. But that would have been a slightly more ideal one line solution (not requiring netcat) if only it worked ;)