[ANN] SQLStore.jl: use SQL tables as persistent Julia collections

Package Announcements
1

This package provides means to treat SQL tables similarly to the simplest Julia tables, Vector{NamesTuple} s. It is not an ORM and doesn’t try to be.

SQLStore.jl specifically focuses on native SQLite datatypes and avoids any Julia-specific serializations. It creates table schemas based on Julia types, adding constraints when necessary. As an example, for a field of Julia type Int , the SQLite column definition is colname int not null check (typeof(colname) = 'integer') . This ensures that only values of proper types can end up in the table, despite the lack of strict typing in SQLite itself.

SQLStore.jl provides push! to insert elements into the collection. Selection and filtering uses Julia functions like collect , filter , only and others. Filtering criteria are transformed to the where SQL clause. Main data modification functions: update! , updateonly! , updatesome! , and similarly with delete! . Values of supported Julia types are automatically converted to/from corresponding SQLite types. See the reference for the full list and function documentation.

Basic Usage

julia> using SQLStore, Dates

julia> db = SQLite.DB();  # in-memory database for the sake of example

julia> create_table(
           db, "table_name",
           @NamedTuple{a::Int, b::String, c::Dict, d::DateTime};
           constraints="PRIMARY KEY (a)"
       );

julia> tbl = table(db, "table_name");

julia> schema(tbl)
Tables.Schema:
 :a  Int64
 :b  String
 :c  Dict
 :d  DateTime

julia> for i in 1:10
           push!(tbl, (a=i, b="xyz $i", c=Dict("key" => "value $i"), d=DateTime(2020, 1, 2, 3, 4, 5)))
       end

julia> length(tbl)
10

julia> only((;a=3), tbl)
(a = 3, b = "xyz 3", c = Dict{Symbol, Any}(:key => "value 3"), d = DateTime("2020-01-02T03:04:05"))

julia> count("a >= 3", tbl)
8

julia> filter((d=DateTime(2020, 1, 2, 3, 4, 5),), tbl)
<... Julia table with 10 rows. Each row is a NamedTuple as above ... >

SQLStore.jl is registered in General.

12 Likes