SQLite SQL API Reference

`SQL Statements`

4 methods

Core SQLite DDL, DML and PRAGMA statements.

CREATE TABLE [IF NOT EXISTS] tbl (col type [constraint], ..., [table_constraint])

Creates a new table. SQLite uses dynamic typing; common affinities are TEXT, INTEGER, REAL, BLOB, NUMERIC.

Name	Type	Description
tbl	identifier	Table name.
type	affinity	Type affinity such as INTEGER or TEXT.
constraint	constraint	PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE, CHECK, REFERENCES.

void

sqlite

CREATE TABLE IF NOT EXISTS notes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  body TEXT NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO tbl [(cols)] VALUES (vals) | SELECT ... [ON CONFLICT(cols) DO ...]

Inserts rows into a table either by literal values or by selecting from another query.

Name	Type	Description
tbl	identifier	Target table.
cols	column-list	Optional column list.
vals	value-list	Literal values matching cols.

void

sqlite

INSERT INTO notes (body) VALUES ('hello')
ON CONFLICT(id) DO UPDATE SET body = excluded.body;

SELECT cols FROM tbl [WHERE condition] [ORDER BY cols] [LIMIT n]

Reads rows from a table with optional filtering, ordering and limiting.

Name	Type	Description
tbl	identifier	Source table or subquery.
condition	boolean	Filter predicate.

ResultSet

sqlite

SELECT id, body FROM notes WHERE body LIKE '%hello%' ORDER BY created_at DESC LIMIT 10;

PRAGMA name [= value]

Queries or sets SQLite compile-time and runtime options such as foreign_keys, journal_mode, table_info.

Name	Type	Description
name	string	Pragma name, e.g. foreign_keys, journal_mode, table_info(tbl).
value	any	Optional new value for the pragma.

ResultSet | void

sqlite

PRAGMA foreign_keys = ON;
PRAGMA table_info(notes);