sqlmath

SQLite with data-science superpowers. Run statistics, machine learning, and analytics directly in SQL queries — from Python, JavaScript, or the browser.

Status

| Branch | master(v2026.4.31) | beta(Web Demo) | alpha(Development) | |--:|:--:|:--:|:--:| | CI | | | | | Coverage | | | | | Demo | | | | | Artifacts | | | |

Table of Contents

1. Web Demo

2. Why sqlmath?

3. Quickstart

   • Python
   • JavaScript (Node.js)
   • Browser (WebAssembly)

4. Built-in SQL Functions

   • Math
   • Statistics (Aggregate)
   • Statistics (Scalar)
   • Arrays
   • Date/Time
   • Time Series / Signal Processing
   • Compression
   • Type Casting
   • Cryptography

5. Machine Learning with LightGBM

   • Training from a Table
   • Prediction
   • Real-World Example: Credit Card Fraud Detection
   • LightGBM Functions

6. Use Cases

   • Financial Data Analysis
   • Embedded ML Pipelines
   • Data Compression & Hashing

7. Why sqlmath vs Alternatives?

8. Python API Reference

   • db_exec()

9. Node.js API Reference

10. Platform Notes

11. Building from Source

    • Prerequisites
    • Build
    • Run Tests
    • Serve Demo Locally

12. Package Listing

13. Changelog

14. License

15. Devops Instruction

    • python pypi publish
    • sqlite upgrade

Web Demo

• https://sqlmath.github.io/sqlmath/index.html

Why sqlmath?

SQLite is everywhere — it's the most deployed database in the world. But it lacks the statistical and ML functions needed for data-science. sqlmath fixes that.

What you get:

• 20+ built-in functions for math, statistics, arrays, dates, and compression
• LightGBM integration — train and predict ML models directly in SQL
• Zero dependencies — single binary, no external libraries needed
• Multi-platform — Python, Node.js, browser (WebAssembly)

Quickstart

Python

pip install sqlmath

from sqlmath import db_open, db_exec, db_close

# Open an in-memory database
db = db_open(":memory:")

# Create sample data
db_exec(db=db, sql="""
    CREATE TABLE prices (symbol TEXT, price REAL);
    INSERT INTO prices VALUES ('AAPL', 150.0), ('GOOGL', 140.0), ('MSFT', 380.0);
""")

# Use built-in statistical functions
result = db_exec(db=db, sql="""
    SELECT
        AVG(price) AS mean_price,
        MEDIAN2(price) AS median_price,
        STDEV(price) AS stdev_price
    FROM prices
""")
print(result)
# [[{'mean_price': 223.33, 'median_price': 150.0, 'stdev_price': 135.77}]]

db_close(db)

JavaScript (Node.js)

npm install sqlmath

import { dbOpenAsync, dbExecAsync, dbCloseAsync } from "sqlmath";

const db = await dbOpenAsync({ filename: ":memory:" });

await dbExecAsync({
    db,
    sql: `
        CREATE TABLE prices (symbol TEXT, price REAL);
        INSERT INTO prices VALUES ('AAPL', 150.0), ('GOOGL', 140.0), ('MSFT', 380.0);
    `
});

const result = await dbExecAsync({
    db,
    sql: "SELECT AVG(price) AS mean_price FROM prices"
});
console.log(result);

await dbCloseAsync(db);

Browser (WebAssembly)

Try it live: sqlmath.github.io/sqlmath

Built-in SQL Functions

Math

| Function | Description | |----------|-------------| | cot(x) | Cotangent | | coth(x) | Hyperbolic cotangent | | fmod(x, y) | Floating-point modulo | | squared(x) | Square of x | | squaredwithsign(x) | Square preserving sign (negative input → negative output) | | sqrtwithsign(x) | Square root preserving sign | | normalizewithsqrt(x) | Normalize using square root | | normalizewithsquared(x) | Normalize using square |

Statistics (Aggregate)

| Function | Description | |----------|-------------| | MEDIAN2(x) | Median value (aggregate) | | PERCENTILE(x, p) | Percentile at p (aggregate) | | QUANTILE(x, q) | Quantile at q (aggregate) | | STDEV(x) | Standard deviation (sample, window-capable) |

Statistics (Scalar)

| Function | Description | |----------|-------------| | marginoferror95(p, n) | 95% margin of error: sqrt(p*(1-p)/n) | | random1() | Random float in [0, 1) |

Arrays

| Function | Description | |----------|-------------| | doublearray_array(...) | Create double array from values | | doublearray_extract(arr, i) | Extract element at index | | doublearray_jsonfrom(json) | Create array from JSON | | doublearray_jsonto(arr) | Convert array to JSON |

Date/Time

sqlmath extends SQLite's date functions with integer-format conversions (YYYYMMDDHHMMSS).

Time Series / Signal Processing

| Function | Description | |----------|-------------| | WIN_SINEFIT2(...) | Fit sine wave to data (aggregate window function) | | SINEFIT_REFITLAST(...) | Refit sine wave with new data point |

Compression

| Function | Description | |----------|-------------| | GZIP_COMPRESS(blob) | Compress BLOB with gzip | | GZIP_UNCOMPRESS(blob) | Decompress gzip BLOB |

Note: Input must be BLOB. Use CAST(text AS BLOB) for text data.

Type Casting

| Function | Description | |----------|-------------| | castrealornull(x) | Cast to REAL or NULL | | castrealorzero(x) | Cast to REAL or 0.0 | | casttextorempty(x) | Cast to TEXT or '' | | roundorzero(x, n) | Round or return 0 |

Cryptography

| Function | Description | |----------|-------------| | sha256(data) | SHA-256 hash (returns BLOB) |

SELECT HEX(sha256('hello')) AS hash;
-- 2CF24DBA5FB0A30E26E83B2AC5B9E29E1B161E5C1FA7425E73043362938B9824

Machine Learning with LightGBM

sqlmath embeds LightGBM for gradient boosting directly in SQL queries. Train models on your data without leaving SQL — no data shuffle to Python needed.

Python users: You must call lgbm_dlopen() before using any lgbm_* functions. See Platform Notes for details.

Training from a Table

LGBM_TRAINFROMTABLE is an aggregate function — it consumes rows like SUM() or AVG(), but outputs a trained model BLOB.

-- Create a table to store the model
CREATE TABLE model_store (model BLOB);

-- Train directly from your data table
INSERT INTO model_store(model)
SELECT
    LGBM_TRAINFROMTABLE(
        -- Training parameters
        (
            'objective=binary'
            || ' learning_rate=0.1'
            || ' metric=auc'
            || ' num_leaves=31'
            || ' verbosity=0'
        ),
        50,            -- num_iterations
        10,            -- eval_step (early stopping check interval)
        'max_bin=15',  -- data parameters
        NULL,          -- reference dataset (NULL for training set)
        -- Columns: first column MUST be the label
        label, feature1, feature2, feature3, feature4
    )
FROM training_data;

Prediction

-- Predict on new data using the stored model
SELECT
    id,
    LGBM_PREDICTFORTABLE(
        (SELECT model FROM model_store),
        0,     -- predict_type (0 = normal probability)
        0,     -- start_iteration
        50,    -- num_iterations
        '',    -- prediction parameters
        feature1, feature2, feature3, feature4
    ) AS prediction
FROM test_data;

Real-World Example: Credit Card Fraud Detection

From the Kaggle notebook — training on 284,807 transactions with 0.17% fraud rate:

from sqlmath import db_open, db_exec, db_table_import

db = db_open("fraud.sqlite")

# Import Kaggle credit card dataset
db_table_import(db=db, filename="creditcard.csv", table_name="transactions", mode="csv")

# Split 80/20 train/test
db_exec(db=db, sql="""
    CREATE TABLE train AS SELECT * FROM transactions WHERE RANDOM() % 5 != 0;
    CREATE TABLE test AS SELECT * FROM transactions WHERE RANDOM() % 5 = 0;
""")

# Train with automatic class imbalance handling
db_exec(db=db, sql="""
    CREATE TABLE model_store (model BLOB);

    INSERT INTO model_store(model)
    SELECT
        LGBM_TRAINFROMTABLE(
            (
                'objective=binary'
                || ' learning_rate=0.05'
                || ' metric=auc'
                || ' is_unbalance=true'  -- Auto-weight minority class
                || ' num_leaves=31'
                || ' verbosity=0'
            ),
            100,           -- num_iterations
            10,            -- eval_step
            'max_bin=255', -- data_params
            NULL,          -- reference
            -- First column = label, rest = features (V1-V28 + Amount)
            Class, V1, V2, V3, V4, V5, V6, V7, V8, V9, V10,
            V11, V12, V13, V14, V15, V16, V17, V18, V19, V20,
            V21, V22, V23, V24, V25, V26, V27, V28, Amount
        )
    FROM train;
""")

LightGBM Functions

| Function | Type | Description | |----------|------|-------------| | lgbm_dlopen(path) | scalar | Load LightGBM library (required in Python) | | LGBM_TRAINFROMTABLE(params, n_iter, eval, data_params, ref, label, ...) | aggregate | Train model from table columns | | LGBM_TRAINFROMFILE(params, n_iter, eval, train_file, data_params, test_file) | scalar | Train from LibSVM file | | LGBM_PREDICTFORTABLE(model, type, start, n, params, ...) | window | Predict per row | | LGBM_PREDICTFORFILE(model, type, start, n, params, file, header, out) | scalar | Predict and save to file | | lgbm_extract(result, key) | scalar | Extract value from result |

Complete Example:

Full notebook with fraud detection, intraday trading signals, and model persistence.

Kaggle Environment:

• Linux x64, Python 3.12, Node.js 24
• Datasets: Credit Card Fraud (284K transactions), SPY intraday OHLCV
• sqlmath installs via pip install sqlmath==2026.4.31

Use Cases

Financial Data Analysis

from sqlmath import db_open, db_exec, db_close, db_table_import

db = db_open(":memory:")

# Import OHLCV data
db_table_import(db=db, table_name="prices", filename="prices.csv", mode="csv")

# Calculate rolling statistics in SQL
result = db_exec(db=db, sql="""
    SELECT
        date,
        close,
        AVG(close) OVER (ORDER BY date ROWS 20 PRECEDING) AS sma_20,
        STDEV(close) OVER (ORDER BY date ROWS 20 PRECEDING) AS volatility,
        PERCENTILE(volume, 50) OVER (ORDER BY date ROWS 5 PRECEDING) AS median_volume
    FROM prices
    ORDER BY date DESC
    LIMIT 10
""")

db_close(db)

Embedded ML Pipelines

Train and deploy models without data movement:

# 1. Load data into SQLite
db_table_import(db=db, table_name="features", filename="training_data.csv", mode="csv")

# 2. Train model in-database
db_exec(db=db, sql="""
    CREATE TABLE models AS
    SELECT LGBM_TRAINFROMTABLE(...) AS model FROM features
""")

# 3. Score new data in-database
db_exec(db=db, sql="""
    SELECT id, LGBM_PREDICTFORTABLE(model, ...) AS score
    FROM new_data, models
""")

# 4. Export results
db_file_save(db=db, filename="scored_data.sqlite")

Data Compression & Hashing

-- Compress large text fields
UPDATE documents SET
    content_compressed = GZIP_COMPRESS(CAST(content AS BLOB));

-- Generate content hashes for deduplication
SELECT HEX(sha256(content)) AS content_hash, COUNT(*)
FROM documents
GROUP BY content_hash
HAVING COUNT(*) > 1;

Why sqlmath vs Alternatives?

| Feature | sqlmath | pandas | DuckDB | sqlite3 | |---------|---------|--------|--------|---------| | In-database ML | ✅ LightGBM | ❌ | ❌ | ❌ | | Zero data shuffle | ✅ | ❌ | ✅ | ✅ | | Statistical functions | ✅ 20+ | ✅ | ✅ | ❌ | | Browser support | ✅ WASM | ❌ | ❌ | ❌ | | Single file deployment | ✅ | ❌ | ✅ | ✅ | | Memory efficiency | ✅ Streaming | ❌ In-memory | ✅ | ✅ |

Choose sqlmath when:

• You need ML training/inference inside the database
• Your data is already in SQLite
• You want browser-based analytics (WebAssembly)
• You need statistical functions beyond basic SQL

Choose pandas when:

• You need complex data transformations
• Interactive exploration with rich visualization
• Your workflow is Python-centric

Choose DuckDB when:

• You need fast analytical queries on large datasets
• You want pandas DataFrame interop
• OLAP workloads are primary

Python API Reference

from sqlmath import (
    db_open,           # Open database
    db_close,          # Close database
    db_exec,           # Execute SQL, return results
    db_exec_and_return_lastblob,  # Execute SQL, return last blob
    db_file_load,      # Load database from file
    db_file_save,      # Save database to file
    db_table_import,   # Import data into table
    db_noop,           # No-op for testing
)

db_exec()

result = db_exec(
    db=db,                    # Database connection
    sql="SELECT ...",         # SQL statement(s)
    bind_list=[...],          # Bind parameters (list or dict)
    response_type=None,       # None (default) returns list-of-dicts
                              # "list" | "lastblob" | "arraybuffer"
)
# Default: [[{'col1': val1, 'col2': val2}, ...]]

Node.js API Reference

• https://sqlmath.github.io/sqlmath/apidoc.html

Platform Notes

The JavaScript (Node.js) binding is more mature than Python. Key differences:

| Feature | Node.js | Python | |---------|---------|--------| | Async vs sync api | async only: result = await dbExecAsync({...}) | sync only: result = db_exec(...) | | Connection pooling | ✅ db = await dbOpenAsync({threadCount: 4, ...}) | ❌ db = db_open(...) | | Type hints | N/A | ❌ Not yet | | Context manager | N/A | ❌ with db_open() not supported |

Note: The Python wrapper is a work in progress. Contributions welcome!

Building from Source

Prerequisites

• Node.js 24+
• Python 3.12+
• C compiler (gcc, clang, or MSVC)

Build

#!/bin/sh

# git clone sqlmath repo
git clone https://github.com/sqlmath/sqlmath --branch=beta --single-branch
cd sqlmath

# Build native binary
npm run test2

# Build WebAssembly (optional)
sh jslint_ci.sh shCiBuildWasm

Run Tests

# Full test suite (includes full clean-build)
npm run test2

# Full test suite (includes partial re-build of modified files)
npm run test

# Quick test (skip build)
npm run test --fast

Serve Demo Locally

PORT=8080 sh jslint_ci.sh shHttpFileServer
# Open http://localhost:8080/index.html

Package Listing

Changelog

• Full CHANGELOG.md

License

• sqlite is under public domain.
• jslint is under Unlicense License.
• zlib is under zlib License.
• cpplint.py is under 3-Clause BSD License.
• indent.exe is under GPLv3 License.
• Everything else is under MIT License.

Devops Instruction

python pypi publish

python -m build
#
twine upload --repository testpypi dist/sqlmath-2026.4.31*
py -m pip install --index-url https://test.pypi.org/simple/ sqlmath==2026.4.31
#
twine upload dist/sqlmath-2026.4.31*
pip install sqlmath==2026.4.31

sqlite upgrade

• goto https://www.sqlite.org/changes.html

    (set -e
    #
    # lgbm
    sh jslint_ci.sh shRollupUpgrade "v4.5.0" "v4.6.0" ".ci.sh sqlmath_base.h"
    #
    # sqlite
    sh jslint_ci.sh shRollupUpgrade "3.50.3" "3.50.4" ".ci.sh sqlmath_external_sqlite.c"
    sh jslint_ci.sh shRollupUpgrade "3500300" "3500400" ".ci.sh sqlmath_external_sqlite.c"
    #
    # shSqlmathUpdate
    read -p "Press Enter to shSqlmathUpdate:"
    sh jslint_ci.sh shSqlmathUpdate
    )