SQLite

https://www.sqlite.org/
Admin
SQLite is a small, fast, and reliable SQL database engine. It's a self-contained, serverless,...
Tokens:1,548,816
Snippets:9,384
Trust Score:9.7
Update:1 week ago
Show doc for...
Tokens:
Raw
### Creating table and indexes for index selection example

Source: https://www.sqlite.org/optoverview.html

Creates a table ex2 with columns x and y, and two indexes ex2i1 on x and ex2i2 on y. This setup is used to demonstrate how the optimizer chooses between multiple indexes for a query with equality constraints on both columns.

```sql
CREATE TABLE ex2(x,y,z);
CREATE INDEX ex2i1 ON ex2(x);
CREATE INDEX ex2i2 ON ex2(y);
SELECT z FROM ex2 WHERE x=5 AND y=6;
```

--------------------------------

### Configure the new project with fossil ui

Source: https://www.sqlite.org/privatebranch.html

Starts a miniature built-in webserver and launches the web browser pointing to it, allowing project configuration. The repository file is specified as an argument.

```shell
fossil ui private-project.fossil

```

--------------------------------

### Load SQLite Tcl extension with package require

Source: https://www.sqlite.org/tclsqlite.html

Run this command from any tclsh or wish to start using the installed SQLite extension.

```tcl
package require sqlite3

```

--------------------------------

### SQL query for sqlite_get_table example

Source: https://www.sqlite.org/c_interface.html

Query to retrieve employee name, login, and host for users whose login starts with 'd'. Used with sqlite_get_table.

```sql
SELECT employee_name, login, host FROM users WHERE login LIKE 'd%';
```

--------------------------------

### Show usage hints for sqlite3 -A

Source: https://www.sqlite.org/sqlar.html

Run the sqlite3 CLI with the -A option and no additional arguments to display usage hints and a summary of all options.

```bash
sqlite3 -A

```

--------------------------------

### Create FTS5 and regular tables

Source: https://www.sqlite.org/profile.html

Schema setup for the examples: creates an FTS5 virtual table 'ft' and two regular tables 't1' and 't2'.

```sql
CREATE VIRTUAL TABLE ft USING fts5(text);
CREATE TABLE t1(a, b);
CREATE TABLE t2(c INTEGER PRIMARY KEY, d);

```

--------------------------------

### .schema main.* command example

Source: https://www.sqlite.org/cli.html

Shows the .schema command with an argument to restrict output to a single database (here 'main').

```text
SQLite-3.54 ex1.db-> .schema main.*
```

--------------------------------

### Set prompt strings starting with -- using .prompt --

Source: https://www.sqlite.org/cliprompt.html

Use the special option flag '--' to disable option processing, allowing prompt strings that begin with '--'. The example shows setting both PS1 and PS2 prompts to strings starting with '--' and the resulting PS1 prompt.

```shell
SQLite-3.54 _memory_-> .prompt -- "--Main prompt->>"  "--continue..>>"
--Main prompt->>

```

--------------------------------

### .databases command example

Source: https://www.sqlite.org/cli.html

Shows the .databases command, which lists all databases open in the current connection. The output is not shown in the source.

```text
SQLite-3.54 ex1.db-> .databases
```

--------------------------------

### Create table with explicit collations

Source: https://www.sqlite.org/datatype3.html

Defines a table with columns using BINARY, RTRIM, and NOCASE collations, and inserts sample data. This setup is used by the subsequent examples.

```sql
CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* collating sequence BINARY */
    b COLLATE BINARY,  /* collating sequence BINARY */
    c COLLATE RTRIM,   /* collating sequence RTRIM  */
    d COLLATE NOCASE   /* collating sequence NOCASE */
);
                   /* x   a     b     c       d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc');
```

--------------------------------

### highlight() overlapping phrase example

Source: https://www.sqlite.org/fts5.html

Shows how highlight() handles overlapping phrase matches by inserting a single open/close marker for each set of overlapping phrases. Includes table setup and a SELECT query with expected results.

```sql
-- Assuming this:
CREATE VIRTUAL TABLE ft USING fts5(a);
INSERT INTO ft VALUES('a b c x c d e');
INSERT INTO ft VALUES('a b c c d e');
INSERT INTO ft VALUES('a b c d e');

-- The following SELECT statement returns these three rows:
--   '[a b c] x [c d e]'
--   '[a b c] [c d e]'
--   '[a b c d e]'
SELECT highlight(ft, 0, '[', ']') FROM ft WHERE ft MATCH 'a+b+c AND c+d+e';
```

--------------------------------

### Show SQLite CLI help: sqlite3 --help

Source: https://www.sqlite.org/cli.html

Displays the full list of command-line options for the SQLite CLI. The output includes the default database filename behavior and descriptions of each option. Use this to see all available options.

```bash
user@host:~$ sqlite3 --help
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist. Defaults to :memory:.
OPTIONS include:
   --                   treat no subsequent arguments as options
   -A ARGS...           run ".archive ARGS" and exit
   -append              append the database to the end of the file
   -ascii               set output mode to 'ascii'
   -bail                stop after hitting an error
   -batch               force batch I/O
   -box                 set output mode to 'box'
   -column              set output mode to 'column'
   -cmd COMMAND         run "COMMAND" before reading stdin
   -csv                 set output mode to 'csv'
   -deserialize         open the database using sqlite3_deserialize()
   -echo                print inputs before execution
   -escape T            ctrl-char escape; T is one of: symbol, ascii, off
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -heap SIZE           Size of heap for memsys3 or memsys5
   -help                show this message
   -html                set output mode to HTML
   -ifexists            only open if database already exists
   -interactive         force interactive I/O
   -json                set output mode to 'json'
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -lookaside SIZE N    use N entries of SZ bytes for lookaside memory
   -markdown            set output mode to 'markdown'
   -maxsize N           maximum size for a --deserialize database
   -memtrace            trace all memory allocations and deallocations
   -mmap N              default mmap size set to N
   -newline SEP         set output row separator. Default: '\n'
   -nofollow            refuse to open symbolic links to database files
   -nonce STRING        set the safe-mode escape nonce
   -no-rowid-in-view    Disable rowid-in-view using sqlite3_config()
   -nullvalue TEXT      set text string for NULL values. Default ''
   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory
   -pcachetrace         trace all page cache operations
   -quote               set output mode to 'quote'
   -readonly            open the database read-only
   -safe                enable safe-mode
   -separator SEP       set output column separator. Default: '|'
   -stats               print memory stats before each finalize
   -table               set output mode to 'table'
   -tabs                set output mode to 'tabs'
   -unsafe-testing      allow unsafe commands and modes for testing
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS
   -vfstrace            enable tracing of all VFS calls
   -zip                 open the file as a ZIP Archive
```

--------------------------------

### FTS4 matchinfo example: create table, insert data, and query

Source: https://www.sqlite.org/fts3.html

Complete example showing how to create an FTS4 table with two columns, insert sample rows, and run a matchinfo query without a format string (defaults to 'pcx'). The query returns a single blob of 20 32-bit integers; the comment explains the expected values and how to interpret them.

```sql
-- Create and populate an FTS4 table with two columns:
CREATE VIRTUAL TABLE t1 USING fts4(a, b);
INSERT INTO t1 VALUES('transaction default models default', 'Non transaction reads');
INSERT INTO t1 VALUES('the default transaction', 'these semantics present');
INSERT INTO t1 VALUES('single request', 'default data');

-- In the following query, no format string is specified and so it defaults
-- to "pcx". It therefore returns a single row consisting of a single blob
-- value 80 bytes in size (20 32-bit integers - 1 for "p", 1 for "c" and
-- 3*2*3 for "x"). If each block of 4 bytes in the blob is interpreted
-- as an unsigned integer in machine byte-order, the values will be:
--
--     3 2  1 3 2  0 1 1  1 2 2  0 1 1  0 0 0  1 1 1
--
-- The row returned corresponds to the second entry inserted into table t1.
-- The first two integers in the blob show that the query contained three
-- phrases and the table being queried has two columns. The next block of
-- three integers describes column 0 (in this case column "a") and phrase
-- 0 (in this case "default"). The current row contains 1 hit for "default"
-- in column 0, of a total of 3 hits for "default" that occur in column
-- 0 of any table row. The 3 hits are spread across 2 different rows.
--
-- The next set of three integers (0 1 1) pertain to the hits for "default"
-- in column 1 of the table (0 in this row, 1 in all rows, spread across 
-- 1 rows).
--
SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'default transaction "these semantics"';
```

--------------------------------

### Show usage hints for .mode with .help mode

Source: https://www.sqlite.org/climode.html

Displays usage hints about the .mode command.

```shell
SQLite-3.54 ex1.db-> .help mode                                                             
```

--------------------------------

### Query the completion table for word completions

Source: https://www.sqlite.org/completion.html

Use this query to get suggestions for the whole input word starting with $prefix. The $wholeline parameter provides context and may be NULL or omitted. DISTINCT and ORDER BY ensure unique, lexicographically sorted results.

```SQL
SELECT DISTINCT candidate COLLATE nocase
  FROM completion($prefix, $wholeline)
 ORDER BY 1;

```

--------------------------------

### Generate VDBE program with EXPLAIN

Source: https://www.sqlite.org/vdbe.html

Commands to start sqlite, create the table, enable .explain mode, and display the VDBE program for the INSERT statement.

```shell
$ sqlite test_database_1
sqlite> CREATE TABLE examp(one text, two int);
sqlite> .explain
sqlite> EXPLAIN INSERT INTO examp VALUES('Hello, World!',99);
```

--------------------------------

### FTS5 prefix query examples with '*'

Source: https://www.sqlite.org/fts5.html

Shows three FTS5 MATCH queries using prefix tokens. The first two match 'one' followed by 'two' and then any token starting with 'thr'. The third places '*' inside quotes, which may not work as expected because the tokenizer may discard it.

```sql
... MATCH '"one two thr" * '
... MATCH 'one + two + thr*'
... MATCH '"one two thr*"'      **-- May not work as expected!**
```

--------------------------------

### .schema command example

Source: https://www.sqlite.org/cli.html

Shows the output of the .schema command without arguments, displaying the complete schema for the database, and with a table name argument to show only that table's schema.

```text
SQLite-3.54 ex1.db-> .schema
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
);
SQLite-3.54 ex1.db-> .schema tbl2
CREATE TABLE tbl2 (
  f1 varchar(30) primary key,
  f2 text,
  f3 real
);
SQLite-3.54 ex1.db->
```

--------------------------------

### Use .mode split to list SQL functions starting with 'c'

Source: https://www.sqlite.org/climode.html

Invokes the split output mode with a screen width of 55 characters to display a single-column query result in multiple columns. The example lists built-in SQL functions whose names begin with 'c' from the pragma_function_list table. The split mode only works on single-column queries and when --screenwidth is not off.

```sql
SQLite-3.54 ex1.db-> .mode --once split --sw 55
SQLite-3.54 ex1.db-> SELECT name FROM pragma_function_list
   ...> WHERE name GLOB 'c*'
   ...> ORDER BY name;
ceil       coalesce     cosh         current_date
ceiling    concat       count        current_time
changes    concat_ws    count        current_timestamp
char       cos          cume_dist
sqlite3>
```

--------------------------------

### Create the t2 example table

Source: https://www.sqlite.org/windowfunctions.html

Creates example table t2 used by the ranking and ntile() examples in this section, along with the previously defined t1.

```sql
CREATE TABLE t2(a, b);
INSERT INTO t2 VALUES('a', 'one'),
                     ('a', 'two'),
                     ('a', 'three'),
                     ('b', 'four'),
                     ('c', 'five'),
                     ('c', 'six');
```

--------------------------------

### Create a Fossil checkout of SQLite source

Source: https://www.sqlite.org/whynotgit.html

Sets up a local Fossil working checkout of the SQLite source. Run these commands in order in a Unix shell (Windows is similar) after completing the Fossil executable download and PATH setup. The last line leaves you ready to build with './configure; make' (or on Windows with MSVC, 'nmake /f Makefile.msc').

```bash
mkdir ~/fossils
```

```bash
fossil clone https://sqlite.org/src ~/fossils/sqlite.fossil
```

```bash
mkdir ~/sqlite; cd ~/sqlite
```

```bash
fossil open ~/fossils/sqlite.fossil
```

--------------------------------

### Open a persistent database with .open after double-click startup

Source: https://www.sqlite.org/cli.html

Shows the terminal output after double-clicking sqlite3.exe, then the .open ex1.db command to attach a persistent database file. The file is created if it does not exist; use a full pathname with forward slashes (e.g., c:/work/ex1.db) to ensure the correct directory.

```text
SQLite version 3.54.0 2026-10-15 18:36:39
Enter ".help" for usage hints.
Connected to a _transient in-memory database_.
Use ".open FILENAME" to reopen on a persistent database.
SQLite-3.54 _memory_-> .open ex1.db
SQLite-3.54 ex1.db->
```

--------------------------------

### Configure and install SQLite Tcl extension on Unix with --with-tclsh

Source: https://www.sqlite.org/tclsqlite.html

Run these commands from the SQLite source tree to configure and install the Tcl extension for a specific Tcl installation. The --with-tclsh option is used when multiple Tcl versions are installed.

```shell
./configure --with-tclsh=$HOME/tcl/bin/tclsh9.0
make tclextension-install

```
Tokens:
Raw