Dashkit

Why SQLite extensions?

Cat behind a computer and a database at forefront
TLDR SQLite extensions can speed up your development and serve various users. They offer privacy, control, and locality to your data and credentials.

Simply put, SQLite is an extremely useful database. It works on all major platforms and programming languages, can run in-memory, and can handle large amounts of data.

SQLite extensions make this already powerful tool even better by adding extra features that are easily accessible.

Benefits of extensions

You might not think of SQLite extensions when you consider pulling data from APIs, but there are several advantages to using them.

Versatility

You can use the same SQLite extension in different ways: from the command line (using sqlite3), from SQL IDEs (like DBeaver), and in programming languages like Python.

This means both analysts and developers can use the same extension, and you can customize your data queries using SQL.

Speed

While our Rust-based extensions might extract data faster, the real advantage is the time you save. You won't need to read extensive documentation, map responses, or deal with rate limits.

An SQLite extension simplifies things by offering uniform access, consistent behavior, and predictable data models.

Control

You have complete control over what data is retrieved, when it happens, and where it is stored. You don't need to share anything with a cloud service or wait for data syncs.

Integration example

Using an SQLite extension in Python is straightforward. Make sure you use SQLite version 3.45.1 or newer, or you can use a wrapper like sqlean.py.

import sqlite3

# Open data into memory
con = sqlite3.connect(':memory:')

# Enable extension
con.enable_load_extension(True)
con.load_extension('libdashkit_lemonsqueezy.dylib')

# Configure extension
con.executescript(
  f'''select lemon_config('api_key: {my_key}')'''
)

Using SQLite extensions can simplify tasks that would otherwise require multiple HTTP requests, deduplicating and caching. For example the following SQL would probably take bit more code.

res = con.execute('''
  select i.order_id,
         i.product_name,
         i.created_at,
         f.name as file_name,
         f.size,
         f.download_url
  from lemon_order_item i
  left join lemon_file f on i.variant_id = f.variant_id
  where i.order_id = ?''',
  (1234,)
)

Alternatives

There are other similar solutions like JDBC/ODBC drivers, provided by companies like Devart, CData and Progress.

These might be better if you need support for more tools, such as some BI software. However, consider these points:

Page updated