sql: Apply recommended cluster settings and sql statements to Docker image via env var

[eddumelendez]

Is your feature request related to a problem? Please describe.
Cockroach documentation makes a recommendation regarding additional cluster settings and sql statements but those have to be applied manually. See https://www.cockroachlabs.com/docs/stable/local-testing#use-a-local-single-node-cluster-with-in-memory-storage

Describe the solution you'd like
It would be nice to have an env var that once set would apply those improvements automatically.

Describe alternatives you've considered
Always let the users declare those improvements.

Additional context
Testcontainers offers a CockroachDb module and
there is a PR in Testcontainers for Go in order to apply those improvements but a more portable solution to other Testcontainers implementation would be the env var.

Jira issue: CRDB-44078

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: sql statement)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[ZhouXing19]

Thanks for filing this issue, @eddumelendez! We've reviewed and triaged this feature request, and we’ll keep you updated with any progress.

As a workaround specifically for Docker containers, you can place the initialization SQL script in a file and mount it to the container. This will ensure the script runs automatically when the single-node server starts. You can find more details on this approach here.

You can optionally mount a directory of initialization scripts into the docker-entrypoint-initdb.d directory within the container. These scripts are run after CockroachDB starts and after the database and user (if specified as environment variables) have been created. The scripts run in the alphanumeric sort order imposed by your locale. The init scripts are run only if the /cockroach/cockroach-data directory within the container is empty.

For example, if you need to apply the recommended cluster settings, you can add the corresponding SQL statements to an SQL script, such as /your/path/init.sql, and mount the directory with:

-v /your/path:/docker-entrypoint-initdb.d

in your docker run command.

Let us know if this helps, and feel free to reach out with further questions!

[stevenh]

Thanks @ZhouXing19 we already have option for init scripts in other modules, so we can investigate how we could leverage that behaviour.

[eddumelendez]

Thanks @ZhouXing19! There was an issue reported related to database initialization in the past in the Testcontainers for Java implementation that I just fixed it testcontainers/testcontainers-java#9505. It works for versions >= 22.1.0 because since that version we can identified when scripts were executed. So, with that users can rely on database initialization provided by the image. We need to apply this to Testcontainers for Go as well. But, would be nice if users can opt-in to this as initially suggested. Thanks again!