Sqlite

notes on SQLite Embedded database technology

Created: Sep 11, 2018 by Pradeep Gowda Updated: Apr 01, 2024 Tagged: sqlite · databases

I wrote an article about SQLite for DeveloperIQ magazine published in Jan 2005 – Move Over MySQL, SQLite is here!!

Query Parquet files in SQLite | Colin Dellow

Importing csv into sqlite: see this page for extended usage

sqlite> .import myfile.csv mytable

SQLite as a document database using Generated Columns and The JSON1 Extension

Tutorials

SQLite Date & Time - How To Handle Date and Time in SQLite (The site sqlitetutorial.net seems to have handy reference for common tasks with sqlite.)

SQLite as a Collaborative & Multiplayer Application File Format - YouTube

Optimizing SQLite for servers

Tools around SQLite

Sqlite-utils

can do things like:

Running queries and returning JSON, CSV, markdown table
Inserting JSON data: `echo | sqlite-utils insert dogs.db dogs -
Inserting CSV data: sqlite-utils insert dogs.db dogs docs.csv --csv
Insert-replacing data.

Sqlean

nalgeon/sqlean: The ultimate set of SQLite extensions

crypto: hash functions
define: user-defined functions and dynamic sql
fileio: read and write files
fuzzy: fuzzy string matching and phonetics
ipaddr: IP address manipulation
json1: JSON functions
math: math functions
regexp: regular expressions
stats: math statistics
text: string functions
unicode: Unicode support
uuid: Universally Unique IDentifiers
vsv: CSV files as virtual tables

Pocketbase

Pocketbase is an open source backend that can be used to build API applications.

single binary download
written in Go
embedded database (SQLite)
built in user and files managment
built in admin UI
simple REST UI

Sqlsync

orbitinghail/sqlsync SQLSync is a collaborative offline-first wrapper around SQLite. It is designed to synchronize web application state between users, devices, and the edge.

Example use cases

A web app with a structured file oriented data model like Figma. Each file could be a SQLSync database, enabling real-time local first collaboration and presence
Running SQLSync on the edge with high tolerance for unreliable network conditions
Enabling optimistic mutations on SQLite read replicas

Soul

soul | 🕉 A SQLite REST and realtime server, written in node.js; Run soul -d sqlite.db -p 8000 and it’ll start a REST API on http://localhost:8000 and a Websocket server on ws://localhost:8000