A DSL to generate SQL queries for different database servers.
Goals:
-
Simplify writing SQL queries, for example using a
NamedTuple
orHash
instead of ordering your args manually and counting your?
in raw SQL strings; -
Avoid each database server quirks such as Postgres using
$i
for statement placeholders, or MySQL using backticks instead of double quotes for quoting column and table names; -
Feel like writing SQL in plain Crystal.
Non Goals:
- Executing queries.
- Becoming an ORM.
The SQL shard is in preliminary alpha. The basis shouldn't change much anymore, but a lot still has to be evaluated in real life situations.
Supported database server flavors:
- MySQL / MariaDB
- PostgreSQL
- SQLite3
You can write any query:
require "sql"
require "sql/builder/posgres"
sql = SQL.new("postgres://")
sql.format { |q| q.select(:*).from(:users).where(q.column(:group_id) == 1) }
# => {%(SELECT * FROM "users" WHERE "group_id" = $1), [1]}
You can include SQL::Helpers
into the current scope to simplify the access to
the #column
method, along with other helpers (#raw
, #operator
).
class UserRepo
include SQL::Helpers
def get(id)
query, args = sql.format &.select(:id, :name).from(:users).where(column(:id) == id)
db.query_one(query, args: args, as: {Int32, String})
end
end
You can usually use a Symbol to refer to a table or column name, but there are cases where we need an object, for example to build a WHERE or HAVING condition and other cases. In these cases, we can define a SQL schema to target tables and table columns in a much more expressive way.
You can define the schema of your database tables, so you can avoid the use of the column helpers, as well as using aliases more easily. Work is underway to have the schemas automatically generated from your database.
class SQL
module Schemas
struct Users < Table
table_name :users
column :id
column :group_id
column :name
end
struct Groups < Table
table_name :groups
column :id
column :name
end
end
end
Then you can:
require "db"
require "sql"
require "sql/builder/postgres"
require "./schemas"
db = DB.open("postgres://")
sql = SQL.new("postgres://")
# bring schemas and helpers into the current scope:
include SQL::Functions
include SQL::Helpers
include SQL::Schemas
query, args = sql.format do |q|
q.select(Users.id, Users.name)
.from(Users)
.where(Users.group_id == 1)
end
# => {%(SELECT "users"."id", "users.name" FROM "users" WHERE "users"."group_id" = $1), [1]}
db.query_all(query, args: args, as: {Int32, String})
As you can see the WHERE condition is a regular Crystal comparison. Most
operators are supported. See SQL::Operators
for the whole list of available
operators, and see the #operator
helper to use any operator from your database
(albeit in a less expressive way).
Sub-queries:
sql.format do |q|
q.select(:*).from(Users).where(Users.group_id.in {
q.select(:id).from(:groups).where(Groups.created_at < 1.month.ago)
})
end
# => {%(SELECT "users"."id", FROM "users" WHERE "users"."group_id" IN (SELECT "groups"."id" FROM "groups" WHERE "groups".created_at < $1), [1.month.ago]}
Functions:
sql.format do |q|
q.select(:id, count(:*)).from(Users).group_by(Users.group_id).having(count(:*) > 2)
end
# => {%(SELECT "users"."id", count(*) FROM "users" GROUP BY "users"."group_id" HAVING count(*) > $1), [2]}
Aliases:
sql.format do |q|
u = Users[:u]
q.select({u.id => :uid, u.name => nil, length(u.name) => :len}).from(u).where(u.group_id == 5)
end
# => {%(SELECT "u"."id" AS "uid", "u"."name", length("u"."name") AS "len" FROM "users" AS "u" WHERE "u"."group_id" == $1), [5]}
Lots more is possible! You can see lots of examples in the test
folder or by
reading the main src/builder.cr
file.
TODO: missing docs (see see/information_schema.cr
).
Distributed under the Apache-2.0 license.
- Julien Portalier