SQLITE(1)

📦 version 2.8.17, in fedora 43, fedora 44, ubuntu jammy

💾 SQLITE(1) — the manual page, but make it bestie-coded ✨

okay so welcome to the SQLITE(1) page, aka the General Commands Manual entry for sqlite. Sit down, grab a strawberry 🍓, we're learning a whole terminal database today and honestly? it's giving iconic.

🌸 NAME 🌸

sqlite — a command line interface for SQLite 💅

🦋 SYNOPSIS 🦋

sqlite [options] filename [SQL]

✨ SUMMARY ✨

sqlite is a terminal-based front-end to the SQLite library, no cap. It lets you type queries interactively, send them to SQLite, and see the results come back. OR, if you're feeling efficient (main character energy), you can just hand it SQL code right on the command line. On top of all that, it also ships with a whole lil collection of meta-commands. obsessed.

🎀 DESCRIPTION 🎀

This manual page documents the sqlite command, briefly. Real talk: this page was written for the Debian GNU/Linux distribution because the original program does not have a manual page. So yeah, we made one. You're welcome. 🫶

🌈 GETTING STARTED 🌈

To start the sqlite program, just type "sqlite" followed by the name of the file that holds the SQLite database. If that file does not exist, a new one is created automatically — literally just spawns into being, slay. Then sqlite will prompt you to enter SQL. Type in SQL statements (terminated by a semicolon), press "Enter", and the SQL gets executed. that's it, that's the whole vibe check.

For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:

$ sqlite ex1
SQLite version 2.0.0
Enter ".help" for instructions
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>

look at that output. clean. iconic. 🔥

🧚 SQLITE META-COMMANDS 🧚

Most of the time, sqlite just reads lines of input and passes them straight on to the SQLite library for execution. But — plot twist — if an input line begins with a dot ("."), then that line is intercepted and interpreted by the sqlite program itself. These "dot commands" are typically used to change the output format of queries, or to execute certain prepackaged query statements.

For a listing of the available dot commands, you can enter ".help" at any time. For example:

sqlite> .help
.dump ?TABLE? ...      Dump the database in an text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        "off" will revert to the output mode that was
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        previously in effect
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.indices TABLE         Show names of all indices on TABLE
.mode MODE             Set mode to one of "line(s)", "column(s)",
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        "insert", "list", or "html"
.mode insert TABLE     Generate SQL insert statements for TABLE
.nullvalue STRING      Print STRING instead of nothing for NULL data
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        "sqlite > " and "   ...> "
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        with the strings MAIN and CONTINUE
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        CONTINUE is optional.
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.reindex ?TABLE?       Rebuild indices
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator string for "list" mode
.show                  Show the current values for the following:
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        .echo
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        .explain
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        .mode
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        .nullvalue
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        .output
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        .separator
.if (0 > 2) .    if 0 .      an-style-warn 23 leading space(s) on input line
.br
.nop                        .width
.tables ?PATTERN?      List names of tables matching a pattern
.timeout MS            Try opening locked tables for MS milliseconds
.width NUM NUM ...     Set column widths for "column" mode
sqlite>

no notes. perfection. ✨

💅 OPTIONS 💅

The program has the following options, bestie:

  • -init file — reads in and processes file, which contains "dot commands". You can use this file to initialize display settings.
  • -html — sets output mode to HTML.
  • -list — sets output mode to 'list'.
  • -line — sets output mode to 'line'.
  • -column — sets output mode to 'column'.
  • -separator separator — specifies which output field separator for 'list' mode to use. The default is '|'.
  • -nullvalue string — when a null is encountered, print string. The default is no string.
  • -[no]header — turns headers on or off. The default is off.
  • -echo — prints commands before execution.

👑 OUTPUT MODE 👑

The SQLite program has different output modes, which define the way the output (from queries) is formatted. pick your fighter:

  • In 'list' mode, which is the default, one record per line is output, each field separated by the separator specified with the -separator option or .separator command.
  • In 'line' mode, each column is output on its own line, and records are separated by blank lines.
  • In HTML mode, an XHTML table is generated.
  • In 'column' mode, one record per line is output, aligned neatly in colums.

🍓 INIT FILE 🍓

sqlite can be initialized using resource files. These can be combined with command line arguments to set up sqlite exactly the way you want it. Initialization proceeds as follows:

  1. The defaults of
mode            = LIST
separator       = "|"
main prompt     = "sqlite> "
continue prompt = "   ...> "

are established.

  1. If a file .sqliterc can be found in the user's home directory, it is read and processed. It should only contain "dot commands". If the file is not found or cannot be read, processing continues without notification. (silent treatment, love that for it 🙈)
  2. If a file is specified on the command line with the -init option, it is processed in the same manner as .sqliterc
  3. All other command line options are processed
  4. The database is opened and you are now ready to begin.

⭐ SEE ALSO ⭐

💖 AUTHOR 💖

This manual page was originally written by Andreas Rottmann <rotty@debian.org>, for the Debian GNU/Linux system (but may be used by others). shoutout to the legend 🫶

and that's the page, babes 💜 this one's from Mon Apr 15 23:49:17 2002, filed under SQLITE(1) — go forth and query, you absolute icon 🦋✨

📦 where this page comes from