I don’t know if you remember, but I wrote a post about my love-hate relationship with Clojure and Postgres. Well, I’ve been working on some things since then, and I’ve started to go down the dark path of DSLs. Before you sock me right in the kisser and judge me for my SQL hating ways, hear me out.

Putting the abstract in abstraction

If I was going to abstract away SQL, I was going to do it with style and panache, the way grand pappy would want it done. I started not with a query, or a clever way to do upserts, no, I started with the humble schema migration. One of the problems I have very frequently is which bit of SQL syntax adds a column with a unique index or a references and is it on cascade delete or on delete cascade? What began as an exercise in shortening what I had to type in for database migrations soon turned into a whole higher level conceptual understanding of how a relational database and an application can work together (shamelessly stolen from Datomic’s look and feel). Without further ado, I give you Coast Schema.

When I finally got down to the quintessence of what a database really is, I came up with a zero table solution. That’s right, tables are the last thing you need to think about when you make a schema in Coast. What you do instead is think about the three building blocks of your application: identities, relationships and columns.

Identities

These are unique columns that can’t be null and they represent some identifiable information in your web application on top of the ubiquitous primary key. These are useful for url friendly identifiers like slugs for blog post titles or short alphanumeric sequences that people can use to reference things in the system without having to know the sequential primary keys.

Relationships

What would a relational database be without relations? It would be nothing. That’s what. NOTHING. Actually, it would be an excel spreadsheet 🤔. Coast has upgraded relations to a first class concept, with VOSS water and foie gras on demand. You define your relations with a name and define how they relate to other things in the database and that’s it. No worrying about foreign keys, indices or on delete cascade syntax ever again! Did I mention they have names like :author/posts and not just join x on x.id = x.y_id ?

Columns

These pretty much stayed the same. Nothing going on here besides friendship between edn and sql ddt syntax. Places to dump your application’s data are always useful.

Queries

Here’s how it looks now to query things in coast:

One thing that’s missing, well a lot of things that are missing, SQL functions, like count and distinct which will probably get added in future versions 🤞, but aren’t super important because you can still write plain SQL files and wire them up to Clojure functions with defq ! That hasn’t changed and won’t ever change.

Pull

With the three concepts of Coast Schema combined querying your database is now easy and most importantly fun! Here’s one great thing about all of this new quasi-SQL stuff that makes it all worth it. Pull. Pull is like a select and a group by but the results come out shaped like a bird watcher would expect them to, nested.

If you only want to pull from one row and get the things related to it, you can do that too

There are a few things missing here that might be useful. Ordering is one of them and limiting the number of nested things that come back are another. You can do that too! Pull is just chock full o’ goodies.

✍️ Inserts, Updates and Deletes

Inserts, and updates have been reduced to one function! Inserting is pretty straightforward, you call one function, transact and give it some data without an ident. Which will make a new row in the database.

Updating is the exact same but with an ident. Transact is kind of like .save from the rails world except with less objects, in fact no objects.

One other cool thing you can do is reference other things when you call transact with an ident, you don’t necessarily need a primary key here!

Deleting is kind of boring but I put it here just to be complete

Right now you can delete only by one column, anything else you’ll have to guess what? That’s right, fallback to defq and regular old SQL.

Friends in the end

Coast only seeks to abstract the common case, not everything. If you know what you want out of an abstraction, that abstraction probably shouldn’t offer it. DSLs over SQL should be a convenience, not a replacement.

It took me quite a while to figure out a way to get SQL into Clojure code without it looking like a huge mess of ((parens)) , :or :a :keyword :fest No offense to korma or honeysql 😬

Now that Clojure and SQL integration has been solved once and for all, and with style it’s time you gave Coast a try 🚀