Relationals.jl

Simple, fast access to relational data sources. Inspired by Rails ActiveRecord.

Works with MySQL/SQLite/PostgreSQL (any DB APIs listed here). Supports querying, but does not yet fully support writing to data sources.

Basic Usage

using Relationals

struct Flower <: Relational
    sepal_length::Float32
    sepal_width::Float32
    petal_length::Float32
    petal_width::Float32
    species::String
end

# Gets the first Flower record.
julia> first(Flower)
Flower(5.1f0, 3.5f0, 1.4f0, 0.2f0, "Iris-setosa")

# Get all Flower records with "sepal_length=5.9" and limit to 2 records.
julia> all(Flower, :sepal_length=>5.9; limit=2)
3-element Vector{Flower}:
 Flower(5.9f0, 3.0f0, 4.2f0, 1.5f0, "Iris-versicolor")
 Flower(5.9f0, 3.2f0, 4.8f0, 1.8f0, "Iris-versicolor")

Advanced Usage

using Relationals, UUIDs

@source "sqlite.db"

struct User <: Relational
    id::Int
    uuid::UUID
    first_name::String
    last_name::String
    address_id::Int
end
@belongs_to User :address

struct Address <: Relational
    id::Int
    street::String
    city::String
    state::String
    zip::String
end
@col Address :zip=>:zipcode

struct Product <: Relational
    key::Int
    name::String
    manufacturer_id::Int
end
@tablename Product :items
@pk Product :key

struct Manufacturer <: Relational
    id::Int
    name::String
    admin_id::Int
end
@belongs_to Manufacturer :admin=>User
@has_many Manufacturer :products

# Gets the first Manufacturer record.
julia> manufacturer = first(Manufacturer)
Manufacturer(1, "Shire Product Co.", 1)

julia> user = getadmin(manufacturer)
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins", 1)

julia> getaddress(user)
Address(1, "1 Bagshot Row", "Shire", "Middle Earth", "37012")

julia> getproducts(manufacturer)
2-element Vector{User}:
  Product(1, "Frying Pan")
  Product(2, "Pipeweed")

The "sqlite.db" file can be found here:
https://github.com/JuliaGraphQL/Relationals.jl/tree/master/docs

Queries

The primary two functions for querying data are: first and all. These functions return a single record and a collection of records, respectively.

Base.first — Function

first(T::Type{<:Relational}; kwargs...)
first(T::Type{<:Relational}, cond::Int; kwargs...)
first(T::Type{<:Relational}, cond::String; kwargs...)
first(T::Type{<:Relational}, cond::Pair; kwargs...)
first(T::Type{<:Relational}, cond::UUID; kwargs...)
first(T::Type{<:Relational}, conds::Union{Tuple,AbstractArray}; kwargs...)

Gets the first record of type T.

Examples

# Gets the first User record.
julia> first(User)
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

# Gets the first User record with id=1.
julia> first(User, 1)
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

# Gets the first User record where "uuid IS NOT NULL".
> first(User, "uuid IS NOT NULL")
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

# Gets the first User record where last_name="Baggins".
> first(User, :last_name=>"Baggins")
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

# Gets the first User record where first_name="Bilbo".
> first(User, :first_name=>:Bilbo)
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

# Gets the first User record where uuid="c6dacd6f-0023-4aba-bf24-374f4042fc47".
> first(User, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"))
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

# Gets the first User record where id=1 and uuid="c6dacd6f-0023-4aba-bf24-374f4042fc47".
> first(User, (:id=>1, :uuid=>UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47")))
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

# Gets the first User record where last_name="Baggins" and uuid is not null.
> first(User, (:last_name=>:Baggins, "uuid IS NOT NULL"))
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

# Gets the first User record where last_name="Baggins" and first_name is "Frodo" or "Bilbo". 
> first(User, (:last_name=>:Baggins, :first_name=>["Frodo", "Bilbo"]))
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

# Gets the first User record where first_name="Frodo" and (id > 1 OR last_name="Baggins").
> first(User, (:first_name=>:Frodo, "id > 1 OR last_name='Baggins'"))
User(2, UUID("0bb0cdc4-b1d3-43ac-93ad-693a94fc9ceb"), "Frodo", "Baggins")

source

Base.all — Function

all(T::Type{<:Relational}; kwargs...)
all(T::Type{<:Relational}, cond::String; kwargs...)
all(T::Type{<:Relational}, cond::Pair; kwargs...)
all(T::Type{<:Relational}, conds::Union{Tuple,AbstractArray}; kwargs...)

Gets a collection of records of type T.

Examples

julia> all(User)
3-element Vector{User}:
 User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins", 1)
 User(2, UUID("32002bdb-0435-42e4-9c7b-e7dea3162abb"), "Frodo", "Baggins", 1)
 User(3, UUID("fe6fe463-10c0-4bbc-bb39-bbd6f55a9e06"), "Samwise", "Gamgee", 2)

julia> all(User, "uuid IS NOT NULL")
3-element Vector{User}:
 User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins", 1)
 User(2, UUID("32002bdb-0435-42e4-9c7b-e7dea3162abb"), "Frodo", "Baggins", 1)
 User(3, UUID("fe6fe463-10c0-4bbc-bb39-bbd6f55a9e06"), "Samwise", "Gamgee", 2)

julia> all(User, :last_name=>"Baggins")
2-element Vector{User}:
 User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins", 1)
 User(2, UUID("32002bdb-0435-42e4-9c7b-e7dea3162abb"), "Frodo", "Baggins", 1)

julia> all(User, ("uuid IS NOT NULL", :last_name=>"Baggins"); limit=2)
2-element Vector{User}:
 User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins", 1)
 User(2, UUID("32002bdb-0435-42e4-9c7b-e7dea3162abb"), "Frodo", "Baggins", 1)

source

Response Formats

There are several variants of first and all, depending on the format you want.

tfirst returns a NamedTuple (tall returns a vector of NamedTuples)
dfirst returns a Dict (dall returns a vector of Dicts)
ffirst returns a DataFrame with one record (fall returns a DataFrame of records)

Relationals.tfirst — Function

tfirst(T::Type{<:Relational}; kwargs...)
tfirst(T::Type{<:Relational}, cond::Int; kwargs...)
tfirst(T::Type{<:Relational}, cond::String; kwargs...)
tfirst(T::Type{<:Relational}, cond::Pair; kwargs...)
tfirst(T::Type{<:Relational}, cond::UUID; kwargs...)
tfirst(T::Type{<:Relational}, conds::Union{Tuple,AbstractArray}; kwargs...)

Identical to first, but returns the record as a NamedTuple.

Example

julia> tfirst(User)
(id = 1, uuid = UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), first_name = "Bilbo", last_name = "Baggins", address_id = 1)

source

Relationals.tall — Function

tall(T::Type{<:Relational}; kwargs...)
tall(T::Type{<:Relational}, cond::String; kwargs...)
tall(T::Type{<:Relational}, cond::Pair; kwargs...)
tall(T::Type{<:Relational}, conds::Union{Tuple,AbstractArray}; kwargs...)

Identical to all, but returns the records as a vector of NamedTuples.

source

Relationals.dfirst — Function

dfirst(T::Type{<:Relational}; kwargs...)
dfirst(T::Type{<:Relational}, cond::Int; kwargs...)
dfirst(T::Type{<:Relational}, cond::String; kwargs...)
dfirst(T::Type{<:Relational}, cond::Pair; kwargs...)
dfirst(T::Type{<:Relational}, cond::UUID; kwargs...)
dfirst(T::Type{<:Relational}, conds::Union{Tuple,AbstractArray}; kwargs...)

Identical to first, but returns the record as a Dict.

Example

julia> dfirst(User)
Dict{Any, Any} with 5 entries:
  :address_id => 1
  :id         => 1
  :uuid       => UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47")
  :last_name  => "Baggins"
  :first_name => "Bilbo"

source

Relationals.dall — Function

dall(T::Type{<:Relational}; kwargs...)
dall(T::Type{<:Relational}, cond::String; kwargs...)
dall(T::Type{<:Relational}, cond::Pair; kwargs...)
dall(T::Type{<:Relational}, conds::Union{Tuple,AbstractArray}; kwargs...)

Identical to all, but returns the records as a vector of Dicts.

source

Relationals.ffirst — Function

ffirst(T::Type{<:Relational}; kwargs...)
ffirst(T::Type{<:Relational}, cond::Int; kwargs...)
ffirst(T::Type{<:Relational}, cond::String; kwargs...)
ffirst(T::Type{<:Relational}, cond::Pair; kwargs...)
ffirst(T::Type{<:Relational}, cond::UUID; kwargs...)
ffirst(T::Type{<:Relational}, conds::Union{Tuple,AbstractArray}; kwargs...)

Identical to first, but returns the record in a DataFrame.

Example

julia> ffirst(User)
1×5 DataFrame
 Row │ id     uuid                               first_name  last_name  address_id 
     │ Int64  String                             String      String     Int64      
─────┼─────────────────────────────────────────────────────────────────────────────
   1 │     1  c6dacd6f-0023-4aba-bf24-374f4042…  Bilbo       Baggins             1

source

Relationals.fall — Function

fall(T::Type{<:Relational}; kwargs...)
fall(T::Type{<:Relational}, cond::String; kwargs...)
fall(T::Type{<:Relational}, cond::Pair; kwargs...)
fall(T::Type{<:Relational}, conds::Union{Tuple,AbstractArray}; kwargs...)

Identical to all, but returns the records in a DataFrame.

source

Mutations

Relationals.create — Function

create(T::Type{<:Relational}; kwargs...)
create(T::Type{<:Relational}, cond::Pair; kwargs...)
create(T::Type{<:Relational}, conds::Union{Tuple,AbstractArray}; kwargs...)

Gets the first record of type T.

Examples

# Gets the first User record.
julia> create(Manufacturer, :name=>"Sauron Supplies")
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

# Gets the first User record.
julia> create(Manufacturer, (:name=>"Sauron Supplies", :admin_id=>1))
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

source

Relationals.update — Function

update(T::Type{<:Relational}, key::Union{Int,UUID}, value::Pair; kwargs...)
update(T::Type{<:Relational}, key::Union{Int,UUID}, values::Union{Tuple,AbstractArray}; kwargs...)

Updates the record matching the unique key (either :id of type Int or :uuid of type UUID) with value(s)

Examples

# Gets the first User record.
julia> update(Manufacturer, 1, :name=>"Sauron Supplies")
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

# Gets the first User record.
julia> update(Manufacturer, 1, (:name=>"Sauron Supplies", :admin_id=>1))
User(1, update("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

source

Relationals.destroy — Function

destroy(T::Type{<:Relational}, key::Union{Int,UUID}; kwargs...)

Delete the record matching the unique key (either :id of type Int or :uuid of type UUID).

Examples

# Gets the first User record.
julia> destroy(Manufacturer, 1)

source

Data sources

Relationals.jl supports MySQL abd SQLite files as data sources. The @source macro configures the data source connection.

Relationals.@source — Macro

@source(fname::String)
@source(c::Expr)
@source(key::QuoteNode, conn::Expr)
@source(key::QuoteNode, fname::String)

Examples

# Configure a MySQL data source as the default.
@source DBInterface.connect(
    MySQL.Connection,
    "127.0.0.1", 
    "username", 
    "password";
    db="widgets",
    port=3306,
)

# Configure an SQLite data source only for the Product model.
@source Product "sqlite.db"

source

Relationals.tablename — Function

tablename(T::Type{<:Relational})

Returns the table name of type T.

source

Relationals.getconnection — Function

getconnection(source_key)

Gets the database connection for "source_key".

source

getconnection(source_keys::Tuple)

Gets multiple database connections for "source_keys".

source

Relationals.conn — Function

conn(T)

Gets the database connections for type T.

source

Base.count — Function

count(T::Type{<:Relational}; kwargs...)
count(T::Type{<:Relational}, cond::String; kwargs...)
count(T::Type{<:Relational}, cond::Pair; kwargs...)
count(T::Type{<:Relational}, conds::Union{Tuple,AbstractArray}; kwargs...)

Gets the count of matching records.

source

Relationals.updatemany — Function

updatemany(T::Type{<:Relational}, key::Union{Int,UUID}, value::Pair; kwargs...)
updatemany(T::Type{<:Relational}, key::Union{Int,UUID}, values::Union{Tuple,AbstractArray}; kwargs...)

Updates the record matching the unique key (either :id of type Int or :uuid of type UUID) with value(s)

Examples

# Gets the first User record.
julia> update(Manufacturer, 1, :name=>"Sauron Supplies")
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

# Gets the first User record.
julia> update(Manufacturer, 1, (:name=>"Sauron Supplies", :admin_id=>1))
User(1, update("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

source

Relationals.@showsql — Macro

showsql(relational_type::Symbol, field)

showsql macro - todo

source

Relational macros

The @has_many and @belongs_to configure one-to-many and many-to-one relationships, respectively.

Relationals.@has_many — Macro

@has_many(model_type::QuoteNode, attr_name::Expr)

Generates a function named "get{attr_name}" to retrieve a collection of objects that are mapped to the model object.

Examples

# Configure a "getproducts" function for a Manufacturer object.
@has_many Manufacturer :products

# Configure a "getadmins" function for a Manufacturer object.
@has_many Manufacturer :admins=>User

# Gets all products of a manufacturer.
julia> getproducts(first(Manufacturer))
2-element Vector{Product}:
  Product(1, "Frying Pan")
  Product(2, "Pipeweed")

# Gets all admins of manufacturer.
julia> getadmins(first(Manufacturer))
2-element Vector{User}:
  User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")
  User(2, UUID("0bb0cdc4-b1d3-43ac-93ad-693a94fc9ceb"), "Frodo", "Baggins")

source

Relationals.@belongs_to — Macro

@belongs_to(model_type::QuoteNode, attr_name::Expr)

Generates a function named "get{attr_name}" to retrieve a single object that is mapped to the model object.

Examples

# Configure a "getaddress" function for a User object.
@belongs_to User :address

# Configure a "getadmin" function for a Manufacturer object.
@belongs_to Manufacturer :admin=>User

# Gets the address of a user.
julia> getaddress(first(User))
Address(1, "1 Bagshot Row", "Shire", "Middle Earth", "37012")

# Gets the location of a manufacturer.
julia> getadmin(first(Manufacturer, 1))
User(1, UUID("c6dacd6f-0023-4aba-bf24-374f4042fc47"), "Bilbo", "Baggins")

source

Relationals.@pk — Macro

pk(relational_type::Symbol, field)

pk macro - todo

source

Relationals.@col — Macro

col(relational_type::Symbol, field2col::Expr)

col macro - todo

source

API index

Base.all
Base.count
Base.first
Relationals.conn
Relationals.create
Relationals.dall
Relationals.destroy
Relationals.dfirst
Relationals.fall
Relationals.ffirst
Relationals.getconnection
Relationals.tablename
Relationals.tall
Relationals.tfirst
Relationals.update
Relationals.updatemany
Relationals.@belongs_to
Relationals.@col
Relationals.@has_many
Relationals.@pk
Relationals.@showsql
Relationals.@source