SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. - Home

• online playground / HN

Basics

• upsert - behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint.

Create Db

$sqlite3 DatabaseName.db
sqlite> .quit

Ruby tutorial

• Ruby
• working with image
• How do I put a Ruby function into a SQLite3 query?

How can I analyse a Sqlite query execution?

Index

rowid / Autoincrement

Rowid Tables are distinguished by the fact that they all have a unique, non-NULL, signed 64-bit integer rowid that is used as the access key for the data in the underlying B-tree storage engine.

• is not a virtual table, and
• is not a WITHOUT ROWID table.

when the rowid table declares an INTEGER PRIMARY KEY. the INTEGER PRIMARY KEY becomes an alias for the rowid.

• if the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY.

• Sqlite primary key on multiple columns

CREATE TABLE something (
  column1, 
  column2, 
  column3, 
  PRIMARY KEY (column1, column2)
);

WITHOUT ROWID

A WITHOUT ROWID table is an optimization that can reduce storage and processing requirements.

The WITHOUT ROWID optimization is likely to be helpful for tables that have non-integer or composite (multi-column) PRIMARY KEYs and that do not store large strings or BLOBs.

In some cases, a WITHOUT ROWID table can use about half the amount of disk space and can operate nearly twice as fast.

SQL Dialect

Replace (/ insert)

• How do I UPDATE a row in a table or INSERT it if it doesn’t exist?

• UPSERT - * I want to update three out of four columns if the record exists, If it does not exists I want to INSERT the record with the default (NUL) value for the fourth column.

Virtual Table

• sqlite-parquet-vtable
  • Query Parquet files in SQLite / HN
  • 1.1 Billion Taxi Rides with SQLite, Parquet & HDFS

The Session Extension / HN

a mechanism for recording changes to some or all of the rowid tables in an SQLite database, and packaging those changes into a “changeset” or “patchset” file that can later be used to apply the same set of changes to another database with the same schema and compatible starting data. A “changeset” may also be inverted and used to “undo” a session.

Tools & Visualizer

Version

In 2004 sqlite migrate to sqlite version 3 with new API and incompatible file format. - sqlite3

API

• Multi-threaded Access to an SQLite3 Database in depth solution review
  • official doc
• sqlite C/C++ Interface
  • c++ wrapper
• sqlite3_exec
• sqlite3_prepare/sqlite3_step/sqlite3_finalize

Typescript/Node.js0

• JoshuaWise/better-sqlite3 / Doc / HN / DefinitelyTyped
  • Help me use SQLite! / Creating good tables
  • Convince me to use better-sqlite3
  • Using better-sqlite3 in a VS Code extension
  • need to rebuild better-sqlite to match electron version used by VSCode `$(npm bin)/electron-rebuild -v 9.3.3 - SO
• sqlite3