TIL: SQLean

Read as Markdown

I was working on LiteCLI to support the fileio extension. These extensions are not part of the Python SQLite3 standard library.

While researching, I came across Sqlean. This project serves as a drop-in replacement for the SQLite3 library and includes several SQLite extensions.

Supported Extensions

When Sqlean is installed, it provides several built-in extensions out of the box:

crypto: hashing, encoding, and decoding data
define: user-defined functions and dynamic SQL
fileio: read and write files
fuzzy: fuzzy string matching and phonetics
ipaddr: IP address manipulation
math: mathematical functions
regexp: regular expressions
stats: statistical computations
text: string functions and Unicode support
time: high-precision date/time operations
uuid: Universally Unique Identifiers (UUID)
vsv: CSV files as virtual tables

Benchmark Code

The following code, from the LiteCLI PR, inserts 10,000 rows into an SQLite3 table: The benchmark results for default Sqlite3

from litecli.main import LiteCli
import timeit


def perf():
    dbname=":memory:"
    cli = LiteCli()
    cli.connect(dbname)
    # Create a table
    cli.run_query("create table test (a text)")
    # Insert 10000 rows
    for i in range(10000):
        cli.run_query(f'insert into test values("{i}")')
    # Delete the table
    cli.run_query("drop table test")


def main():
    # timeit is not needed when using hyperfine
    print(timeit.timeit("perf()", globals=globals(), number=10))


if __name__ == "__main__":
    main()

Benchmarking Performance

I benchmarked the performance of Sqlean against SQLite3 from the standard Python library. The results showed no significant performance degradation.

hyperfine --shell /opt/homebrew/bin/fish  --runs 5 --show-output  'uv run python litecli/performance.py'
Benchmark 1: uv run python litecli/performance.py
3.7350782089924905
3.7475891669892007
3.77843445900362
3.740818833000958
3.749061916998471
  Time (mean ± σ):      3.966 s ±  0.027 s    [User: 3.899 s, System: 0.050 s]
  Range (min … max):    3.944 s …  4.008 s    5 runs

Sqlean

hyperfine --shell /opt/homebrew/bin/fish  --runs 5 --show-output  'uv run python litecli/performance.py'
Benchmark 1: uv run python litecli/performance.py
3.648521625000285
3.724126499990234
3.765500624998822
3.648744167003315
3.666765707996092
 Time (mean ± σ):      3.921 s ±  0.083 s    [User: 3.824 s, System: 0.066 s]
 Range (min … max):    3.854 s …  4.027 s    5 runs

Visualization of Performance Differences

The following chart compares the execution times between the two implementations:

five different runs

Additionally, the chart below illustrates the mean, minimum, maximum, and standard deviation across five different runs:

various comparision

Seamless Integration with Existing SQLite3 Code

If you are already using SQLite3, you can integrate Sqlean with minimal code changes:

try
  import sqlean as sqlite3
except ImportError:
  import sqlite3

This simple modification allows your code to leverage Sqlean while maintaining compatibility with SQLite3.