QueryBuilder

You can use QueryBuilder to create and execute a query in a simple and fast way.

using Wasabi
using SQLite

mutable struct User <: Wasabi.Model
    id::Int
    name::String
end

configuration = SQLiteConnectionConfiguration("test.db")
conn = Wasabi.connect(configuration)

query = QueryBuilder.from(User) |> QueryBuilder.select() |> QueryBuilder.limit(1)
users = Wasabi.df2model(User, Wasabi.execute_query(conn, user))

QueryBuilder supports select, join, where, limit, offset, group by and order by.

Select can be expressed as vector of symbols or using SelectExpr object. If no arguments are passed to select then all fields of the model are selected. You can also use alias and select function like count, sum, avg and so on.

QueryBuilder.from(User) |> QueryBuilder.select() # Select all fields from user model
QueryBuilder.from(User) |> QueryBuilder.select([:id, :name]) # SELECT user_alias.id, user_alias.name FROM user user_alias
QueryBuilder.from(User) |> QueryBuilder.select(User, :id, :total, :count) # SELECT COUNT(user_alias.id) AS total FROM user user_alias

Join are expressed using source, target, type (inner, outer ...), on, select. Here's an example to join User with UserProfile using an INNER JOIN and selecting the "bio" column from UserProfile .

query = QueryBuilder.from(User) |> QueryBuilder.select() |> QueryBuilder.join(User, UserProfile, :inner, (:id, :user_id), [:bio])

# SELECT user.id, user.name, user_profile.bio FROM "user" user INNER JOIN "user_profile" user_profile ON user.id = user_profile.user_id

Where conditions are expressed as Julia Expr object where you can nest and/or conditions. The condition needs to be expressed as (Model, fieldname, function, params).

query = QueryBuilder.from(User) |> QueryBuilder.select() |> QueryBuilder.where(:(or, (User, name, like, "%mattia%"), (User, id, in, [1, 2, 3]))) |> QueryBuilder.limit(1)
sql, params = QueryBuilder.build(query)

# println(sql.value) SELECT user.id, user.name FROM "user" user WHERE (user.name LIKE $1 OR user.id IN $2) LIMIT 1
# println(params) # 2-element Vector{Any}: "%mattia%" [1, 2, 3]

• Wasabi.QueryBuilder.Join
• Wasabi.QueryBuilder.Query
• Wasabi.QueryBuilder.SelectExpr
• Wasabi.QueryBuilder.build
• Wasabi.QueryBuilder.from
• Wasabi.QueryBuilder.groupby
• Wasabi.QueryBuilder.join
• Wasabi.QueryBuilder.limit
• Wasabi.QueryBuilder.offset
• Wasabi.QueryBuilder.orderby
• Wasabi.QueryBuilder.select
• Wasabi.QueryBuilder.select
• Wasabi.QueryBuilder.select
• Wasabi.QueryBuilder.where

Join{T<:Wasabi.Model}
Represents a join for the given model.

Query{T<:Wasabi.Model}
Represents a query for the given model.

SelectExpr{T<:Wasabi.Model}
Represents a select expression for the given model.

build(q::Query)::Tuple{RawQuery, Vector{Any}}
Builds the query and returns a tuple of the query string and the parameters.

from(source::Type{T})::Query where {T <: Model}
Creates a new query for the given model.

groupby(groupby::Vector{Symbol})
Sets the group by clause for the given query.

join(m::Type{T}, type::Symbol, on::Tuple{Symbol, Symbol}, select_fields::Vector{Symbol} = Symbol[]) where {T <: Model}
Joins the given model to the query.

limit(limit::Int)
Sets the limit for the given query.

offset(offset::Int)
Sets the offset for the given query.

orderby(orderby::Vector{Symbol})
Sets the order by clause for the given query.

select(select::Union{Vector{Symbol}, Nothing} = nothing)
Sets the selected columns for the given query. If no columns are specified, all columns are selected.

selectselect(source::Type{T}, field::Symbol, alias::Union{Symbol, Nothing} = nothing, fn::Union{Symbol, Nothing} = nothing) where {T<:Wasabi.Model}
Sets the selected columns for the given query.

select(source::Type{T}, select::Vector{Symbol})
Sets the selected columns for the given query.

where(expr::Expr)
Sets the where clause for the given query.