SQLite Reference

Contents

• Behavior
  • booleans
• sqlite3
  • Init File
  • dot-commands (meta-commands)
  • Executing SQL
• Gotchas
  • Why am I getting a non-null constraint on my primary key column on insert?
• Frequently Asked Questions (FAQs)
  • How can I tell what sqlite was compiled with?
  • How can I check whether sqlite was built with fts5?
• Resources

Behavior

booleans

SQLite has no BOOLEAN type. Use INTEGER type instead:

sqlite3

Init File

• if ~/.sqliterc exists, it is processed
• if -init path/to/.myinitrc is passed to sqlite3:
  • ~/.sqliterc is not processed
  • path/to/.myinitrc is processed

Example:

sqlite3 -init .myinitrc foo.sqlite3

dot-commands (meta-commands)

Frequently used:

.headers on       # turns on column headers
.help
.help TOPIC
.mode column      # aligns output to column
.read FILE        # path relative from sqlite3 invocation (not database location)
.schema ?PATTERN? # describe table equivalent

SQLite documentation’s full list of dot-commands. The SQLITE3(1) man page refers to these as “meta-commands”.

Executing SQL

sqlite3 my-db.sqlite3 < path/to/some-file.sql

Gotchas

Why am I getting a non-null constraint on my primary key column on insert?

To get SQLite’s autoincrementing behavior, ensure your schema uses exactly the declaration id_col_name INTEGER NOT NULL PRIMARY KEY, and not, for example id_col_name INT8 NOT NULL PRIMARY KEY,.

Frequently Asked Questions (FAQs)

How can I tell what sqlite was compiled with?

sqlite> pragma compile_options;

How can I check whether sqlite was built with fts5?

Look for ENABLE_FTS5 in this output:

sqlite> pragma compile_options;

Resources

• Official Documentation
  • Programming Interfaces
    • SQL Syntax: SQL As Understood By SQLite
      • CREATE TABLE
      • SELECT
      • UPDATE
      • UPSERT