A supercharged SQLite library for Python

SuperSQLite: a supercharged SQLite library for Python

      
      

A feature-packed Python package and for utilizing SQLite in Python by Plasticity. It is intended to be a drop-in replacement to Python's built-in SQLite API, but without any limitations. It offers unique features like remote streaming over HTTP and bundling of extensions like JSON, R-Trees (geospatial indexing), and Full Text Search. SuperSQLite is also packaged with pre-compiled native binaries for SQLite and all of its extensions for nearly every platform as to avoid any C/C++ compiler errors during install.

Table of Contents

• Installation
• Motivation
• Using the Library
  • Connecting
  • Querying
• Remote Streaming over HTTP
• Other Documentation
• Other Programming Languages
• Contributing
• Roadmap
• Other Notable Projects
• LICENSE and Attribution

Installation

You can install this package with pip:

pip install supersqlite # Python 2.7
pip3 install supersqlite # Python 3

Motivation

SQLite, is a fast, popular embedded database, used by large enterprises. It is the most widely-deployed database and has billions of deployments. It has a built-in binding in Python.

The Python bindings, however, often are compiled against an out-of-date copy of SQLite or may be compiled with limitations set to low levels. Moreover, it is difficult to load extremely useful extensions like JSON1 that adds JSON functionality to SQLite or FTS5 that adds full-text search functionality to SQLite since they must be compiled with a C/C++ compiler on each platform before being loaded.

SuperSQLite aims to solve these problems by packaging a newer version of SQLite natively pre-compiled for every platform along with natively pre-compiled SQLite extensions. SuperSQLite also adds useful unique new features like remote streaming over HTTP to read from a centralized SQLite database.

Moreover, by default, SQLite does not enable some optimizations that can result in speedups. SuperSQLite compiles SQLite with various optimizations and allows you to select your workload at runtime to further automatically configure the connection to be optimized for your workload.

When to use SuperSQLite?

SQLite is extremely reliable and durable for large amounts of data (up to 140TB). It is considered one of the most well-engineered and well-tested software solutions today, with 711x more test code than implementation code.

SQLite is faster than nearly every other database at read-heavy use cases (especially compared to databases that may use a client-server model with network latency like MySQL, PostgreSQL, MongoDB, DynamoDB, etc.). You can also instantiate SQLite completely in-memory to remove disk latency, if your data will fit within RAM. For key/value use cases, you can get comparable or better read/write performance to key/value databases like LevelDB with the LSM1 extension.

When you have a write-heavy workload with multiple servers that need to write concurrently to a shared database (backend to a website), you would probably want to choose something that has a client-server model instead like PostgreSQL, although SQLite can handle processing write requests fast enough that it is sufficient for most concurrent write loads. In fact, Expensify uses SQLite for their entire backend. If you need the database to be automatically replicated or automatically sharded across machines or other distributed features, you probably want to use something else.

See Appropriate Uses For SQLite for more information and Well-Known Users of SQLite for example use cases.

Using the Library

Instead of 'import sqlite3', use:

from supersqlite import sqlite3

This retains compatibility with the sqlite3 package, while adding the various enhancements.

Connecting

Given the above import, connect to a sqlite database file using:

conn = sqlite3.connect('foo.db')

Querying

Remote Streaming over HTTP

Workload Optimizations

Extensions

JSON1

FTS3, FTS4, FTS5

LSM1

R*Tree

Other

Custom

Export SQLite Resources

Optimizations

Other Documentation

SuperSQLite extends the apsw Python SQLite wrapper and adds on to its functionality. You can find the full documentation for that library here, which in turn attempts to implement PEP 249 (DB API). The connection object, cursor object, etc. are all apsw.Connection, apsw.Cursor. Note, however, that some monkey-patching has been done to make the library more in-line and compatible as a drop-in replacement for Python's built-in sqlite3 module.

Other documentation is not available at this time. See the source file directly (it is well commented) if you need more information about a method's arguments or want to see all supported features.

Other Programming Languages

Currently, this library only supports Python. There are no plans to port it to any other languages, but since SQLite has a native C implementation and has bindings in most languages, you can use the export functions to load SuperSQLite's SQLite extensions in the SQLite bindings of other programming languages or link SuperSQLite's version of SQLite to a native binary.

Contributing

The main repository for this project can be found on GitLab. The GitHub repository is only a mirror. Pull requests for more tests, better error-checking, bug fixes, performance improvements, or documentation or adding additional utilties / functionalities are welcome on GitLab.

You can contact us at [email protected].

Roadmap

• Out of the box, "fast-write" configuration option that makes the connection optimized for fast-writing.
• Out of the box, "fast-read" configuration option that makes the conenction optimized for fast-reading.
• Optimize streaming cache behavior

Other Notable Projects

• pysqlite - The built-in sqlite3 module in Python.
• apsw - Powers the main API of SuperSQLite, aims to port all of SQLite's API functionality (like VFSes) to Python, not just the query APIs.
• Magnitude - Another project by Plasticity that uses SuperSQLite's unique features for machine learning embedding models.

LICENSE and Attribution

This repository is licensed under the license found here.

The SQLite "feather" icon is taken from the SQLite project which is released as public domain.

This project is not affiliated with the official SQLite project.