SQL

Different modules revolving handle SQL queries.

• SQL::Query to generate SQL queries;
• SQL::InformationSchema to introspect on your database;
• SQL::Migrate to migrate your database.

There might be more modules in the future, for example a tokenizer to lex a SQL query srting into tokens, and maybe a full blown parser.

Status

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

SQL::Query

DSL to generate SQL queries for different database servers.

Goals:

• Simplify writing SQL queries, for example using a NamedTuple or Hash 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.

• Be the foundation for an ORM, Repo or plain SQL queries.

Non Goals:

• Execute queries.
• Become an ORM.

Queries

You can write any query:

require "sql"
require "sql/query/builder/posgresql"

sql = SQL.query("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 : Int32)
    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. In these cases, we can define a SQL schema to target tables and table columns in a much more expressive way.

Schemas

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 these schemas automatically generated from your database.

module MySchemas
  struct Users < SQL::Query::Table
    table_name :users
    column :id
    column :group_id
    column :name
  end

  struct Groups < SQL::Query::Table
    table_name :groups
    column :id
    column :name
  end
end

Then you can:

require "pg"
require "sql"
require "sql/query/builder/postgresql"
require "./schemas"

db = DB.open("postgres://")
sql = SQL.query("postgres://")

# bring schemas and helpers into the current scope:
include SQL::Query::Functions
include SQL::Query::Helpers
include MySchemas

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]}

The SQL is generated as the methods are called, so you must define the sub-query right into the block (as you would in SQL); you can't assign it to a variable and return that variable from the block. For example the following will generate invalid SQL:

sql.format do |q|
  group_ids = q.select(:id).from(:groups).where(Groups.created_at < 1.month.ago)
  q.select(:*).from(Users).where(Users.group_id.in { groups_ids })
end
# => {%(SELECT "groups"."id" FROM "groups" WHERE "groups".created_at < $1SELECT "users"."id", FROM "users" WHERE "users"."group_id" IN (), [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]}

With:

register_function :very_expensive_function

sql.format do |q|
  q.with(:w) { q.select({:key => nil, very_expensive_function(:val) => :f}).from(:some_table) }
    .select(:*)
    .from(column(:w, as: w1))
    .join(column(:w, as: :w2))
    .on(raw("w1.f") == raw("w2.f"))
end
# => {%(WITH "w" AS (SELECT "key", very_expensive_function("val") as "f" FROM "some_table") SELECT * FROM "w" AS "w1" JOIN "w" AS "w2" ON w1.f = w2.f), []}

Lots more is possible! You can see lots of examples in the test folder or by reading the documentation for SQL::Query::Builder::Generic.

SQL::InformationSchema

TODO: missing docs (see see/information_schema.cr).

SQL::Migrate

TODO: missing docs (see src/migrate/cli.cr and bin/migrate.cr).

License

Distributed under the Apache-2.0 license.

Authors

• Julien Portalier

Influences

• diesel.rs
• honeysql