# Write a table into SQLite and DuckDB in efficient manner

**URL:** <https://discourse.julialang.org/t/write-a-table-into-sqlite-and-duckdb-in-efficient-manner/93417>\
**Category:** Performance\
**Tags:** help-database, sqlite, sql, database, duckdb\
**Created:** [January 23, 2023, 5:58pm UTC](https://discourse.julialang.org/t/write-a-table-into-sqlite-and-duckdb-in-efficient-manner/93417 "2023-01-23T17:58:45Z")\
**Posts on this page:** 5\
**Page:** 2

<div class="post-metadata">

**Author:** ![ToucheSir](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/touchesir/32/14411_2.png) [@ToucheSir](https://discourse.julialang.org/u/ToucheSir)\
**Post date:** [January 26, 2023, 11:05pm UTC](https://discourse.julialang.org/t/write-a-table-into-sqlite-and-duckdb-in-efficient-manner/93417/21 "2023-01-26T23:05:17Z")

</div>

I’m not sure how important having contiguous or low-latency access to elements in the vector would be. Probably some `AbstractVector`s would not qualify. The code lives in [duckdb/data\_frame\_scan.jl at master · duckdb/duckdb · GitHub](https://github.com/duckdb/duckdb/blob/master/tools/juliapkg/src/data_frame_scan.jl), so people more familiar with the data ecosystem than I can determine what would be needed for broader Tables.jl support.

---

<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:** [January 27, 2023, 12:34am UTC](https://discourse.julialang.org/t/write-a-table-into-sqlite-and-duckdb-in-efficient-manner/93417/22 "2023-01-27T00:34:25Z")

</div>

A cursory look suggests converting that file from dataframes to arbitrary tables (through `columntable`) is pretty easy. However, I cannot simply run `DuckDB.jl` tests successfully, all of these fail for me:

- `]dev DuckDB` + `]test DuckDB`
- clone duckdb, go to `juliapkg` dir, do `]test`
- clone duckdb, go to `juliapkg` dir, do `./test.sh`

Also, their contribution rules aren’t clear: do you know if they even accept outside PRs?

---

<div class="post-metadata">

**Author:** ![chris-b1](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/chris-b1/32/14165_2.png) [@chris-b1](https://discourse.julialang.org/u/chris-b1)\
**Post date:** [February 21, 2023, 9:58pm UTC](https://discourse.julialang.org/t/write-a-table-into-sqlite-and-duckdb-in-efficient-manner/93417/23 "2023-02-21T21:58:58Z")

</div>

Opened a feature request at DuckDB here - [Feature Request: juliapkg - Tables.jl integration · Discussion #6407 · duckdb/duckdb · GitHub](https://github.com/duckdb/duckdb/discussions/6407)

---

<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:** [June 18, 2023, 11:56pm UTC](https://discourse.julialang.org/t/write-a-table-into-sqlite-and-duckdb-in-efficient-manner/93417/24 "2023-06-18T23:56:45Z")

</div>

Tried once again, and now could run DuckDB tests successfully.  
Converting it from dataframes to builtin tables was laborious but straightforward: [integrate with julia tables by aplavin · Pull Request #7984 · duckdb/duckdb · GitHub](https://github.com/duckdb/duckdb/pull/7984).  
This PR makes DuckDB.jl both more general (support any table), and much lighter (# deps is halved). Hope it will be accepted.

---

<div class="post-metadata">

**Author:** ![rdavis120](https://avatars.discourse-cdn.com/v4/letter/r/b5a626/32.png) [@rdavis120](https://discourse.julialang.org/u/rdavis120)\
**Post date:** [December 8, 2023, 3:25am UTC](https://discourse.julialang.org/t/write-a-table-into-sqlite-and-duckdb-in-efficient-manner/93417/25 "2023-12-08T03:25:25Z")

</div>

If you need to append data to an existing table, you can also use the appender api to write data rather than executemany queries. In my test, that improved the performance significantly to be better than sqlite.

SQLite (2nd run):

```julia
str = join(repeat('?', length(table_data)), ',')
write_query = DBInterface.prepare(con_sqlite, "INSERT INTO data VALUES($str)")
@time DBInterface.executemany(write_query, table_data) 
# second run: 0.009210 seconds (159.03 k allocations: 7.922 MiB)

```

  

Duckdb appender:

```julia
using DataFrames
db = DuckDB.DB("test1.duckdb")
appender = DuckDB.Appender(db, "data")
table_frame = rowtable(table_data) |> DataFrames.DataFrame;
@time begin
for i in eachrow(table_frame)
    for j in i
        DuckDB.append(appender, j)
    end
    DuckDB.end_row(appender)
end
DuckDB.flush(appender)
# second run: 0.005218 seconds (117.96 k allocations: 3.021 MiB)

```

[Previous page](https://discourse.julialang.org/t/write-a-table-into-sqlite-and-duckdb-in-efficient-manner/93417.md?page=1)