Welcome to Seaquell (Draft)

Unfortunately, a number of SQL keywords clash with the names of Clojure core functions. But for the DSL to be intuitive, you need to use the most natural function names, so that’s what I did.

For symbols you refer, you only have two choices with respect to conflicting symbols: exclude them or rename them. I prefer to exclude conflicting symbols and qualify them with a short namespace alias.

So that leaves us with four choices when requiring seaquell.

I’ll describe each one briefly.

seaquell has full support for all SQLite (version 3.32.3) statements.

The Statement is the primary abstraction. At its heart, seaquell gives you a syntax for creating instances of SQL statements as simple values. Statements are represented as one of the following types:

To create a statement, simply call the function named for it. You can mix in execution options such as db and params or supply them later.

Calling select doesn’t actually execute a SELECT query, it creates an internal representation of one. Once the statement exists you can do something useful with it like render it as a string or execute it.

Here are some articles explaining how to write simple and compound select statements and a handy reference that groups seaquell functions by statement.

Of course, one of the main reasons for treating SQL statements as data is so that you can manipulate them over time.

Here’s how to compose a select statement in seaquell.

As you can see, it’s very natural to add or replace clauses. But what if you want to modify existing ones? Check out the Composing Statements page for more thoughts on this subject. Functions in the diesel.edit namespace provide many useful functions for manipulating nested data structures. Finally, you can use functions in clojure.walk or clojure.zip or core functions like update-in, assoc-in, and dissoc-in to modify statements. See Representing Statements below to learn more about their structure.

You can call up to three functions to execute a statement: sql!, do-sql, or <stmt>! (e.g., select!). The first two work for all statements. The third is specific to one type of statement created with the core DSL. Regardless of which function you use, execution requires a database spec or connection and possibly parameter values. You may also want to supply additional execution options such as row-fn. As we saw above, the execution-related properties can be mixed in when Creating Statements. They don’t affect how the statement renders, which is nice when working interactively. Here is a pattern I often use from the REPL.

For all these examples, assume an existing database connection named c.

Only one character changes between function invocations, which makes it very easy to use at the REPL, and very easy to remember. Depending on your workflow, you might try executing a statement first and showing it as a string or just data if there’s a problem. For more complicated queries, I generally render as a string while I compose the statement and execute it when it looks right. Use steps 1 and 2 for inspecting or troubleshooting, and step 3 for doing. Switch between the steps as needed.

Here’s the same example with SQL tokens and raw SQL strings. Again, only one character changes between function calls.

Sometimes, you def a SQL statement and then execute it later. In that case, you can supply the execution properties later as well. In the examples below, we’ve associated the connection with the query definition, but supplied the parameter at execution time.

We only used the db and params properties in the previous examples, but there are others available. You can read more about the execution engine and its other options.

Seaquell doesn’t automatically parameterize your query. To do so yourself, just use :? or any parameter placeholder instead of a literal value. Then use params to supply the parameter values when you execute the statement.

SQLite supports multiple parameter placeholders.

You can use numbered parameters:

You can use named parameters:

If your really need to, you can use raw to create the other flavors of named parameters:

Sometimes it’s useful to see what SQL string your statement expands to or maybe you need the SQL string to pass it to another library. There are generally three ways to render a statement:

Here’s a small example that uses all three techniques and then executes the statement.

The previous example defined a query and then rendered it in a separate call. You can define a statement and render it in one step.

Every statement is just a map with a :sql-stmt key. Each kind of statement will have its own set of keys used to define it and rules for what values are permissible.

When I first started this project, clojure.spec did not exist. So I wrote a few articles to explain the representation of these statements:

I’m starting to use specs to document the internal structure of statements and clauses.

An excellent way to learn a new library is to see it in action. The seaquell.zoo namespace contains a number of solutions to various SQL Zoo tutorials.

Since seaquell heavily targets SQLite, its documentation can actually help you, especially the syntax diagrams. Also, the tests described below offer many, many examples of seaquell usage. Check out the statement index for useful links related to each type of statement.

To check your installation or if you choose to contribute, you may want to execute the units tests. Sea-quell uses the excellent midje library for this purpose. Just type lein midje from the command prompt to run the tests. They should all pass.

You can also run the test-all task.

Finally, there’s the check-all task.

Looking at the tests is a great way to learn what sea-quell can and can’t do.

At some point, you may want to look more closely at the source code. Perhaps you just want to understand a particular function better. Maybe you want to change or extend what the library does. This article gives an overview of how the code is organized.

Here are various thoughts regarding seaquell design rationale and direction. Mostly, these are notes to my future self so I can remember certain ideas. You may or may not find them interesting.

There’s still a lot of work to do. For a glimpse of possible future directions, check out the road map.