Coast queries are quite a bit nicer than working with raw sql, there are a few shortcuts you can take that you can't with yesql style sql files.
This guide is an exhaustive showcase of all of the querying abilities that you too can have when you make your website with Coast.
Coast attempts to abstract away database specific syntax so you can write queries in clojure vectors and theoretically switch databases with little to no effort. Think sqlite in development and postgresql in production.
Although, it's taboo to say, but you can easily run a low to medium traffic website (99% of all websites) with sqlite in production.
You can also build incremental queries quite a bit easier instead of mashing SQL strings together.
(let [sql "select * from person"
sql (if condition?
(str sql " where email = ?")
sql)])
...versus
(let [query '[:select * :from person]
query (if condition?
(conj query '[:where [email ?email]])
query)])
Coast uses a few conventions when dealing with databases.
The first thing you'll notice is that every response back from the database uses qualified keywords like this:
{:table/column "value"}
Given this table named person
| column | type |
|---|---|
| id | integer |
| screen_name | text |
| text | |
| password | text |
| updated_at | timestamp |
| created_at | timestamp |
The following query:
(coast/q '[:select * :from person])
... would return something like this
[{:person/id 1 :person/screen-name "sean" :person/email "sean@example.com" :person/password "hashed"}
{:person/id 2 :person/screen-name "sean1" :person/email "sean1@example.com" :person/password "hashed"}]
The second thing you'll notice is that column names are automatically converted between kebab-case to camel_case and back again in the response.
So screen_name in the database becomes :screen-name in your code.
The third thing is that each table on creation uses "id" as it's primary key. This makes generating joins easier.
See Migrations for more details.
Below is a basic example of a query
(coast/q '[:select *
:from person
:where [screen-name ?screen-name]
:limit 1]
{:screen-name "@sean"})
You can either select all of the columns in a given table with *, use idents or qualified-idents.
'[:select id screen-name
:from person]
; => [{:person/id 123 :person/screen-name "@sean"}]
NOTE: All responses from the database return qualified keywords in the format of table/column
You can also qualify the columns like this:
'[:select person/id person/screen-name
:from person]
There are a few options for building up where clauses
:where'[:select *
:from person
:where [id ?id]]
The way clojure symbols work, you don't have to put ?id and pass in the params separately.
This also works:
[:select :*
:from :person
:where [:id 1]]
Notice that every element of every vector is a keyword, not a mix of symbols and keywords.
You can also pass in various operators to the where clause:
'[:select *
:from person
:where [age > 21]]
All of the following work as well:
>, !=, <=, =>, <, like
Coast queries attempt to match up the value with the correct sql operator:
(coast/q '[:select *
:from person
:where [id ?id]]
{:id nil})
; => select * from person where id is null
(coast/q '[:select *
:from person
:where [id != ?id]]
{:id nil})
; => select * from person where id is not null
(coast/q '[:select *
:from person
:where [id like ?screen-name]]
{:screen-name "%ean"})
; => "select * from person where screen_name like ?", '%ean'
You can also pass in vectors to the where clause and it will automatically output an "in" statement
(coast/q '[:select *
:from person
:where [id ?ids]]
{:ids [1 2 3]})
; => "select * from person where id in (?, ?, ?)", 1, 2, 3
If all else fails, you can pass a sql vector to the where clause as well:
(coast/q '[:select *
:from person
:where ["id not in (?, ?, ?)" 1 2 3]])
; => "select * from person where id not in (?, ?, ?)", 1, 2, 3
This can be used to write subqueries, exists, between, or anything else your SQL loving heart desires.
[:select *
:from person
:join todo]
; => "select * from person join todo on todo.person = person.id"
This is made easy by using coast's database conventions where every primary key is named "id" and every foreign key column is named after the table it references.
:left-join, :right-join, :left-outer-join, :right-outer-join, :outer-join, :full-outer-join, :full-join and :cross-join all work similarly.
You can construct the join yourself as well:
[:select *
:from person
:join [todo person/id todo/person-id]]
Feel free to pass strings to :join as well:
[:select *
:from person
:join "todo on todo.person_id = person.id"
"tag on tag.todo = todo.id"]
'[:select :distinct age pet
:from person]
; => select distinct age, pet from person
'[:select age
:from person
:group-by age]
; or
'[:select age
:from person
:group age]
'[:select *
:from person
:order age desc name asc]
'[:select age
:from person
:group age
:having age > 21]
'[:select *
:from person
:offset 11
:limit 10]
(coast/insert {:person/email "test@example.com" :person/screen-name "test"})
You can also insert multiple records at once
(coast/insert [{:person/email "test1@test.com" :person/screen-name "test1"}
{:person/email "test2@test.com" :person/screen-name "test2"}])
Feel free to not use the helper and just use execute! instead (which is similar to q)
(coast/execute! [:insert person/email person/screen-name
:values [["test1@test.com" "test1"]
["test2@test.com" "test2"]]])
; => (2)
NOTE: execute! returns a list of the number of rows inserted, to get the actual number try first on the result
(coast/update {:person/id 1 :person/last-name "Appleseed" :person/first-name "Johnny"})
update requires an :id key
It can also take a list of maps
(coast/update [{:person/id 1 :person/last-name "Appleseed"}
{:person/id 2 :person/last-name "Newton"}])
execute! works here too
(coast/execute! [:update person
:set [person/first-name "Isaac"]
[person/last-name "Newt"]
:where [person/last-name "Newton"]])
Delete only deletes rows by primary key :id
(coast/delete {:person/id 1})
execute! works here too!
(coast/execute! [:delete
:from person
:where [person/last-name "Newton"]])
pluck takes a query and returns the first result, which is kind of weird, but that's what it's called
(coast/pluck [:select * :from person :where [id 1]])
fetch returns a given row by primary key
(coast/fetch :person 1) ; => {:person/first-name "Johnny" :person/last-name "Appleseed"}
Returns the columns for a given table
(coast/cols :person)
In Coast there are two ways to pass in plain old sql queries
defqdefq works by creating a .sql file in resources/sql and then calling that files from clojure with defq and instantly having access to all of that files sql bits.
Here's some SQL in a sql file: resources/sql/posts.sql
-- name: find-by-id
-- fn: first!
select *
from posts
where posts.id = :id
limit 1
-- name: all
select *
from posts
-- name: insert
insert into posts (
title,
body
) values (
:title,
:body
)
-- name: update
update posts
set title = :title,
body = :body
where posts.id = :id
-- name: delete
delete from posts where id = :id
Here's a clojure file named posts.clj inside of the db folder with the namespace db.posts:
(ns db.posts
(:require [coast]))
(coast/defq "sql/posts.sql")
This generates functions find-by-id, insert, update and delete in the db.posts namespace at compile time.
Which means now this will work:
(db.posts/insert {:title "title" :body "body"})
and this:
(db.posts/find-by-id {:id 1}) ; => {:id 1 :title "title" :body "body"}
Each generated function takes a single map and returns a list of maps from the database.
NOTE: The maps and the returned rows as maps, do NOT have qualified keywords.
qq also takes a sql vector with plain old sql like so:
(coast/q ["select * from person where id = ?" 1])
This will return:
[{:first-name "Johnny" :last-name "Appleseed" :id 1}]
Again, not namespace qualified.