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

**URL:** https://discourse.julialang.org/t/ann-sqlstore-jl-use-sql-tables-as-persistent-julia-collections/71796
**Category:** Package Announcements
**Created:** [November 19, 2021, 8:14pm UTC](https://discourse.julialang.org/t/ann-sqlstore-jl-use-sql-tables-as-persistent-julia-collections/71796 "2021-11-19T20:14:56Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)
#### Post date: [November 19, 2021, 8:14pm UTC](https://discourse.julialang.org/t/ann-sqlstore-jl-use-sql-tables-as-persistent-julia-collections/71796/1 "2021-11-19T20:14:56Z")

</div>

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-auto
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`.

> **[Alexander Plavin / SQLStore.jl · GitLab](https://gitlab.com/aplavin/SQLStore.jl)**
>
> GitLab.com