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 user 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;   -- !

Just don't COMMIT!
Read the manual about BEGIN and ROLLBACK.

Be aware that some things cannot be rolled back, though. For instance, 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 sensitive data. The risk of committing by accident is too great. And letting users execute arbitrary code is a risk that is 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.

Answer 3

This is not quite what you're asking for, but might still be interesting:

SQLite supports the EXPLAIN and EXPLAIN QUERY PLAN statements, which return information about how the SQL statement would operate.

For example:

sqlite> EXPLAIN QUERY PLAN SELECT name FROM sqlite_master;
QUERY PLAN
`--SCAN sqlite_master

sqlite> EXPLAIN SELECT name FROM sqlite_master;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     8     0                    0   Start at 8
1     OpenRead       0     1     0     2              0   root=1 iDb=0; sqlite_master
2     Explain        2     0     0     SCAN sqlite_master  0
3     Rewind         0     7     0                    0
4       Column         0     1     1                    0   r[1]=sqlite_master.name
5       ResultRow      1     1     0                    0   output=r[1]
6     Next           0     4     0                    1
7     Halt           0     0     0                    0
8     Transaction    0     0     0     0              1   usesStmtJournal=0
9     Goto           0     1     0                    0