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

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

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

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.

# 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,)])

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

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