Can I dry run/sandbox sql commands?

Question

I am trying to build a small rails app to teach sql to beginners in a game format. Part of it is a command line where the use tries to solve problems with sql. On the backend I’d like to run their sql command against a test database, and check the result to see if their command was correct.

My current idea is to create sqlite databases for each stage of the lesson, and then create a copy of that database for each user when they reach that stage.

I think that will work but I am concerned about efficiency.

My question is whether there is an efficient way to have users run SQL commands (including drop, alter, etc.), get the result, but not actually make any changes to the db itself, in essence a dry run of arbitrary sql commands. I am familiar with sqlite and postgres but am open to other databases.

Answer 1

In Postgres, you can do much with transactions that are rolled back at the end:

BEGIN;

UPDATE foo ...:
INSERT bar ...;
SELECT baz FROM ...;
CREATE TABLE abc...;   -- even works for DDL statements
DROP   TABLE def...;
ALTER  TABLE ghi ...:

ROLLBACK;   -- !

More in the manual: BEGIN ROLLBACK

Be aware that some things cannot be rolled back, though. For instances sequences don't roll back. Or some special commands like dblink calls.

And some commands cannot be run in a transaction with others. Like CREATE DATABASE or VACUUM.

Also, there may be side effects with concurrent load, like deadlocks. Unlikely, though. You can set the transaction isolation level to your requirements to rule out any side effects (at some cost to performance).

I would not do this with sensible data. The risk of committing by accident is too great. And letting users execute arbitrary code is a risk that hardly containable. But for a training environment, that should be good enough.

Back it up with a template database. If something should go wrong, that's the fastest way to restore a basic state. Example (look at the last chapter):
Truncating all tables in a Postgres database

This can also be used as brute force alternative: to provide a pristine new database for each trainee.

Answer 2

While you can run their code within a transaction and then attempt to check the results within the same transaction before rolling it back, that'll have some challenges.

First, if there's any error in their code, the transaction will abort. You won't be able to tell anything more than "It failed, here's the error message". In particular, you won't be able to check partial results before the error.

Second, if you're doing parallel runs in a single database, locking will bite you. Each session will obtain locks, often exclusive locks on tuples or whole relations. Other sessions will block waiting on the lock. This means you'll have to do your test runs serially. That might not be an issue in your environment, but bears thinking about.

Third, as Erwin notes, you'll have to make your check code insensitive to things like sequence values because they're not reset on rollback.

For all these reasons, I strongly recommend just:

CREATE DATABASE course_username_lesson TEMPLATE course_lesson OWNER username;

... or omit the use of template databases and just let your Rails app set up a blank database using migrations for each user. This may be simpler, as it means you don't have to maintain the template DBs.

A PostgreSQL server is quite happy with many hundreds of databases, at least if it's not expected to perform at peak production loads.

You may want to check out http://sqlfiddle.com/ and a number of other existing web-based SQL editor/training tools, many of which are open source.