btbytes.com

Sqlite

2 min read

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

60K RPS out of sqlite on a $5 VPS

Django

Tools around SQLite

sqlpkg

The list of packages are here - https://sqlpkg.org.

TODO SQLPkg is written in go. install it manually.

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

See also: duckdb, sqlite-writable_schema, sqlite-csv-loading-using-virtual-table-extension

Graph View