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 yaer, and return Person struct for each.

These structs are generated to be included in your code, so JET.jl can do type-checking and VSCode can show the definition with fields when you hover over them.

Status

• Only supports Integer ids.
• Only supports SQLite.

Walkthroough.

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.

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

AbstractVector maps to IN, AbstractRange and Pair to BETWEEN and AbstractString to LIKE if it contains _ or $. Otherwise it's =.

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 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 get any fields aggregations with sql etc.

You can also query by relations, though contraint ... foreign key... is not supported yet - the column names simply need to match.

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.

For many-to-many relationship you need to have an object for e.g. PersonVisit in this case and do Person[PersonVisit[Visit[...]]].

Additionally, if you use JET then it will pick up some errors, like field name being wrong here:

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

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
• maybe? db.sqlmap for Person -> Person,person,Persons,persons, i.e. multiple gentablenames
• UUIDs, e.g. with PSQL
• get_sqls for dbs other than sqlite
• dates