DOCSEARCHbyAlgolia
Submit Docs

sqlite-lines

repository·main·Indexed 18 days ago

https://github.com/asg017/sqlite-lines

A SQLite extension for high-performance reading and processing of line-oriented datasets, such as NDJSON or JSON Lines, directly within SQL queries. It features high throughput and memory efficiency, allowing the processing of datasets larger than the 1GB SQLite blob limit. The project provides bindings and plugins for Node.js (better-sqlite3, node-sqlite3), Python, Ruby, Deno, Datasette, and sqlite-utils.

Tokens
6.3K
Snippets
27
Records
38
Agent score
61%

What's inside sqlite-lines

  1. Choose the correct `sqlite-lines` Python package

    main

    The sqlite-lines project provides two distinct Python packages depending on your integration needs:

    1. sqlite-lines: The core package for general Python usage. Use this if you want to use the sqlite-lines functionality directly in your own Python scripts or applications.
    2. datasette-sqlite-lines: A Datasette plugin. Use this if you are using Datasette and want to add sqlite-lines capabilities to your Datasette instance.

  4. Performance characteristics of sqlite-lines

    main

    The sqlite-lines extension is designed for high-performance NDJSON and line-oriented data processing. Key performance characteristics include:

    • High Throughput: It leverages SQLite's JSON support and a light wrapper around getdelim() to achieve high processing speeds, comparable to specialized tools like DuckDB.
    • Memory Efficiency: The lines_read() function does not read the entire file into memory. This allows sqlite-lines to process datasets significantly larger than the 1GB SQLite blob limit, making it suitable for multi-gigabyte NDJSON files.
    • Versatility: It can be used for both read-only analytical queries (calculating sums, filtering, etc.) and for data transformation (inserting NDJSON objects into flat SQLite tables).

    Note: While cat | grep may be faster for simple text filtering on local files, sqlite-lines provides a more complete SQL-based interface for complex data manipulation.

  6. Install sqlite-lines

    main

    You can install sqlite-lines across various environments using the following package managers:

    • Python: pip install sqlite-lines
    • Datasette: datasette install datasette-sqlite-lines
    • Node.js: npm install sqlite-lines
    • Deno: deno.land/x/sqlite_lines
    • Ruby: gem install sqlite-lines

    Pre-built binaries for Linux amd64 and MacOS (amd64, no arm) are available on the GitHub Releases page.

    pip install sqlite-lines

  7. Load sqlite-lines as a runtime extension

    main

    To use sqlite-lines as a loadable extension, download the lines0.dylib (MacOS) or lines0.so (Linux) file from the releases page. The 0 in the filename represents the major version.

    Note: Windows is not currently supported.

    -- In SQLite CLI
.load ./lines0
select lines_version();
    # In Python (sqlite3)
import sqlite3
con = sqlite3.connect(":memory:")
con.enable_load_extension(True)
con.load_extension("./lines0")
print(con.execute("select lines_version()").fetchone())
    // In Node.js (better-sqlite3)
const Database = require("better-sqlite3");
const db = new Database(":memory:");
db.loadExtension("./lines0");
console.log(db.prepare("select lines_version()").get());
    # In Datasette
datasette data.db --load-extension ./lines_nofs0

  8. Install sqlite-lines via npm

    main

    Node.js developers can install sqlite-lines using npm. The project also provides autogenerated platform-specific packages (e.g., sqlite-lines-darwin-x64, sqlite-lines-windows-x64) for optimized deployment on specific architectures. Refer to the main sqlite-lines package documentation for specific installation instructions and supported platforms.

    npm install sqlite-lines

  9. Build sqlite-lines from source

    main

    To build sqlite-lines for a specific architecture or to statically link it into your own application, you must build it from source.

    Requirements:

    • A gcc compiler.
    • A MacOS or Linux machine (Windows is not supported).
    • python3 (required for running tests).
    • emscripten (required only if building the WASM sql.js version).

    Setup:

    git clone git@github.com:asg017/sqlite-lines.git
cd sqlite-lines

  10. Run sqlite_lines with Deno permissions

    main

    Because x/sqlite_lines needs to download and cache the pre-compiled SQLite extension for your specific machine, it requires network and filesystem permissions. Since it also relies on FFI (Foreign Function Interface), you must use the --allow-ffi and --unstable flags.

    It is recommended to use the -A or --allow-all flag for convenience.

    deno run -A --unstable <file>

  11. Install sqlite-lines for macOS (darwin-arm64)

    main

    The sqlite-lines-darwin-arm64 package provides the pre-compiled SQLite extension specifically for macOS systems running on ARM64 architecture (e.g., Apple Silicon).

    When you install the main sqlite-lines package, it automatically resolves to this platform-specific package if the host environment is darwin with arm64 architecture. This package contains the pre-compiled extension located at lib/lines0.dylib and is designed for use with better-sqlite3 or node-sqlite3 drivers.

  12. Integrate sqlite-lines into a C/C++ application

    main

    To bundle sqlite-lines into your own C/C++ application, you have two primary methods:

    1. Source Copying: Copy sqlite-lines.h and sqlite-lines.c directly into your project source.
    2. Object Linking: Run make dist/lines0.o to generate an object file, then link this file to your application.

    To automatically load sqlite-lines functions/tables into every SQLite connection created by your application, use the sqlite3_auto_extension() API in conjunction with the SQLITE_EXTRA_INIT compile-time option.