gregopet/jooq-web-repl

A web REPL for trying out jOOQ commands

Online jOOQ REPL

This project contains a web REPL to perform jOOQ queries. It is currently a work in progress so expect papercuts. I currently have no idea where this project is going, all the current features either serve my needs or were fun to program :P

Quickstart with Docker Compose

Clone this directory to a machine that has Docker Compose installed and run this command in the project’s root directory:

docker-compose up

(when you’re running this for the first time, it may take a while while Docker and Gradle download all the dependencies)

Then, point your browser to http://127.0.0.1:8000/ and try executing some queries. Try jooq.selectFrom(FILM).fetch() for example. Take a look at the Sakila schema to get more ideas about queries to try. Look up jOOQ’s documentation if you can’t get a query to work.

You can also try playing with the docker-compose.yaml file if you want to add additional RDBMS engines - MySQL should be simple. As for supporting database schemas other than Sakila, you can either provide your own jOOQ model or generate one on the fly.

Note that if you can’t get your changes to show up despite restarting docker-compose, the nuclear approach seems to be docker-compose up -V --build.

Can I bring my own database?

Yes! Just read on - in fact, one of the main reasons I built this project was to have a jOOQ REPL I could easily attach to my projects using docker-compose (which I use to set up all my development databases these days).

The web REPL

The web executor accepts Java scripts and makes it easy to run jOOQ scripts on them. A jooq variable is available for use that contains a connected DSLContext.

Setup

The following environment variables are available:

REPL_PORT

The port to which the web application will bind, defaults to 8080

REPL_COMPILE_DEPENDENCIES

Whitespace-delimited list of Gradle package coordinates to use when building the REPL

REPL_JOOQ_VERSION

The jOOQ version to include into the REPL, defaults to 3.11.9

REPL_CSRF_SECRET

If given, it will be used as a secret seed for CSRF protection tokens - note that you have to use 127.0.0.1 instead of localhost when running a local container with CSRF protection! If not given, no CSRF protection will be applied.

Generating the jOOQ model during build

If you don’t have a jOOQ model built yet, one can be built during startup. Set the GENERATE_JOOQ_PACKAGE property (see the section about configuring databases) to have the build system make a jOOQ model for you. Also make sure that your database driver is on the classpath by copying its JAR to the extra-libs directory (you need it for running queries anyway).

You can even copy the generated model and use it in your own project, though you should know that it will be built with very generic settings. jOOQ’s schema generator has a wide array of settings with which you can tweak your model and this project doesn’t support them all (even the schema is currently locked to public). Consider Modifying the build.gradle file manually if you don’t want to include the model generation into your own build.

Extending the classpath

The folder extra-libs will be loaded into the same classpath as the snippets. Your existing jOOQ models and database drivers are excellent candidates - copy their JARs into this folder or mount them in your docker-compose files.

Adding your own source files

Any source files copied into the extra-source-files will be compiled together with the REPL source files. A good use of this would be to make your jOOQ source models available even if you don’t compile them into a separate JAR.

Make sure your directory structure continues to mirror Java’s directory-per-package convention. For example, to include source files from the org.example.model package only, the corresponding docker-compose volume instruction would be ./src/main/java/org/example/model:/usr/src/webapp/extra-source-files/org/example/model.

Adding your own dependencies

If your source files require additional dependencies, it is possible to include them using the REPL_COMPILE_DEPENDENCIES environment variable. It will separate individual dependencies by splitting them any whitespace character - newlines work pretty good in a docker compose YAML file.

Sandboxing

You can sandbox the REPL by running the evaluations under Java’s security manager with whitelisted rules only including the permissions strictly necessary to run database queries. This will disallow access to the underlying filesystem, OS and connecting to any network host not specifically allowed.

To use this feature provide a USE_JAVA_SANDBOX environment variable (and don’t forget allow access to each database’s host and port, see the SANDBOXING_HOST_AND_PORT database property below).

The sandboxing feature is, together with other precautions, intended for publicly accessed sites and not something an ordinary user would have a use for - you should probably just restrict access to the entire REPL and ignore the sandboxing feature.

Database connections

Setup

The application must be told about the databases at its disposal using environment variables. On startup, the application will look for any environment variables of the form DATABASE_<unique_database_key>_URL (where unique_database_key is any valid environment variable name).

This property must contain a connection string that can be used by JDBC. In addition to it several more properties can be defined using the same prefix but replacing URL another key. These keys are:

URL

the connection string to use when connecting to the database

DESCRIPTION

the text to be shown for this connection on the user interface

USER

the username to use when connecting to this database

PASSWORD

the password to use when connecting to this database

GENERATE_JOOQ_PACKAGE

if present and not blank, a jOOQ schema will be generated with this value as its package name

SCRIPT_PREFIX

text to automatically inject before any user scripts, use it to provide common imports or anything else that will make the users' life easier. By default, import org.jooq.impl.DSL and import static org.jooq.impl.DSL.*; will be prefixed;

SANDBOXING_HOST_AND_PORT

if present and not blank and sandboxing is being used, permission will be given to Java to connect to this host and port. The format for this property is the same as used in the SocketPermission class (e.g. 127.0.0.1:5432, wildcards are allowed). Note that if sandboxing is in use and this property is not given, connecting to this database will probably not work - this is because arbitrary connection strings are hard to parse reliably so this project currently isn’t even trying to.

For example, to define a database using the key 'OLD_POSTGRES' one could use environment variables named DATABASE_OLD_POSTGRES_DESCRIPTION, DATABASE_OLD_POSTGRES_URL, DATABASE_OLD_POSTGRES_USER, DATABASE_OLD_POSTGRES_PASSWORD.

Customizing the UI

The UI is a TypeScript application packaged via Webpack. To build it run npm install and npm run build in the repl/src/main/webpack directory. The build process will copy the CSS and JS files to the application’s web root (the latest builds are commited into git to keep things simple). To get a dev build run npm run build:dev.