ORM package that you can use with FunSQL

Motivating example

FunSQL.jl allows you to build better queries than, say SQLAlchemy, but it doesn't provide an Object-Relational Mapping. This package does, so that you're able to write:

julia> let f(x) = x |> Join(:new => x |> Group(Get.gender_concept_id) |> Select(Agg.max(Get.year_of_birth), Get.gender_concept_id), Fun.and(Get.gender_concept_id .== Get.new.gender_concept_id, Get.year_of_birth .== Get.new.max)) 
       db[Person, f]  
       end

Which for each gender will pick the youngest people by year, and return Person struct for each.

These structs are generated to be included in your code, so VSCode can show the definition with fields when you hover over them. And JET.jl can do type-checking, e.g. picking up the typo here:

db[Person[month_of_birth=[2, 4]]][1].year_if_birth

Status

Only supports Integer ids.
Only supports SQLite.
Errors with Transducers.jl

Walkthrough

We start with the example DB that FunSQL provides:

using FunnyORM, SQLite
download("https://github.com/MechanicalRabbit/ohdsi-synpuf-demo/releases/download/20210412/synpuf-10p.sqlite", "db.sqlite")
db = FunnyORM.DB{SQLite.DB}("db.sqlite")

First we need the object-relational mapping. It's easiest to generate it by specifying the db, object name, and table name.

FunnyORM.generate_file(db, :Person, tablename=:person)
include("models/person.jl")
Person

After you run this, you VSCode should show you what Person is, and what fields it has, when you hover over it.

About defaults

If a field can be Missing, the generated class will contain default missing for it. For the rest no default is set, so you may wish to edit the generated file.

It will try to link to tablename, which by default is lowercase, pluralised model name.

Now we can query the db.

using DataFrames
db[Person[month_of_birth=[2, 4], person_source_value="%F%", year_of_birth=1900:1930]]

If you know SQL a Vector is IN, AbstractRange and Pair BETWEEN and AbstractString to either LIKE if it contains _ or $, or =.

Also a named tuple in arguments is treated as an OR, so in this case the following are equivalent:

Person[month_of_birth=[2, 4]]
Person[(month_of_birth=2, month_of_birth=4)]

Under the hood it's converted to SQL queries. You can add a second argument to getindex and it will pass your query into it.

using FunSQL: Order, Get
db[Person[month_of_birth=[2, 4]], Order(Get.year_of_birth)]

In the examples above we create a vector of objects and convert to DataFrame for printing. To skip creation of objects you can replace , with |>:

using FunSQL: Order, Get
db[Person[month_of_birth=[2, 4]] |> Order(Get.year_of_birth)] |> DataFrame

And be able to use FunSQL to further, e.g:

only select a subset fields,
join tables
aggregate

You can also query by relations, though the column names simply need to match. contraint ... foreign key... is not supported yet. Here's an example:

FunnyORM.generate_file(db, :Visit, tablename=:visit_occurrence)
include("models/visit_occurrence.jl")

db[Person[Visit[visit_end_date="" => "2008-04-13"]]]

This will give you people who had visits that ended before 13th Apr 2008 (inclusive).

For many-to-many relations you need to have an object for e.g. PersonVisit in this case and do Person[PersonVisit[Visit[...]]]. Also if you already had a vis::Visit then vis == db[vis] so you can write Person[PersonVisit[vis]] to get people that went on that visit.

Mutating:

Creating new objects:

# single insert - returns new Person
Person(db)(gender_concept_id=8532, month_of_birth=11)
# bulk insert - returns Vector{Person}
Person(db)([(gender_concept_id=8532, month_of_birth=11), (gender_concept_id=1111,)])

Updating objects

Here you can use a macro:

# grab the latest insert
example = db[Person[year_of_birth=1940]] |> first
@update db[example] day_of_birth = 10 month_of_birth = 3
example.day_of_birth == 10  # true

# Warning! It only updates the reference you call it with, i.e:
old = example
@update db[example] day_of_birth = 15
example.day_of_birth == 15, example.day_of_birth == 10  # both true

Or using db[model](kwargs) syntax:

updated = db[example](year_of_birth=1941)
example.year_of_birth == 1940, updated.year_of_birth == 1941  # both true

still TODO:

db.sqlmap for relationships
UUIDs, e.g. with PSQL
get_sqls for dbs other than sqlite
dates
pagination

asjir/FunnyORM.jl