*** DRAFT ***
In Command Line Interface (or CLI) for SQLite version 3.52.0 (pending), the ".mode" dot-command was enhanced to provide more flexibility in rendering query results. But with flexibiliity comes complexity. For that reason, the operation of the ".mode" command is now broken out into this separate document.
The ".mode" command now takes many new options that control the output format. Some of these new options replace settings that were formerly handled by separate dot-commands. For example, the new "--width" option now replaces the legacy ".width" dot-command. (The legacy ".width" command is retained for compatibility but is no longer documented and is deprecated.)
The default mode for interactive sessions is changed to "qbox" modified with additional options "--quote relaxed", "--limits 5,300,20", "--textjsonb on" and "--sw auto". The legacy "list" mode is still the default for scripts, for backwards compatibility.
New modes "tty" and "batch" set the display mode to the default for interactive sessions and for batch sessions, respectively.
New mode "psql" makes the output look very similar to the output from PostgreSQL's "psql" command-line utility.
There are many new formatting possibilities. See details below.
To see the current output mode, use ".mode" with no arguments.
sqlite> .mode .mode qbox --limits on --quote relaxed --sw auto --textjsonb on sqlite>
Add "-v" for more detail:
sqlite> .mode -v .mode qbox --align "" --border on --blob-quote auto --colsep "" --escape auto --limits on --null "NULL" --quote relaxed --rowsep "" --sw auto --tablename "" --textjsonb on --titles on --widths "" --wordwrap off sqlite>
Use ".mode --list" to see a list of all available output modes:
sqlite> .mode --list available modes: ascii box c column count csv html insert jatom jobject json line list markdown off psql qbox quote table tabs tcl batch tty sqlite>
Change to "column" mode with no limits on the length of individual fields, but squeezing the width of all columns so that the table fits on the screen.
sqlite> .mode column -limits off -sw auto
Set up the display to be ASCII-art boxes (similar to MySQL) with text shown unquoted, with NULL value appearing as "NULL", with BLOB values appearing as hexadecimal, and with length limits set to show only the first 10 lines or first 1000 characters of any value.
sqlite> .mode table -quote off -null NULL -blob hex -limits 10,1000
Use split-column mode ("split") but only for a single SQL input (due to the "--once" option).
sqlite> .mode --once split sqlite> SELECT name FROM pragma_function_list ...> WHERE name GLOB 'json_*' ...> ORDER BY name; json_array json_group_array json_patch json_replace json_valid json_array_length json_group_object json_pretty json_set json_array_length json_insert json_pretty json_type json_error_position json_object json_quote json_type json_extract json_parse json_remove json_valid sqlite3>
Show usage hints about the ".mode" command:
sqlite> .help mode
As of version 3.52.0 (pending) the default format is:
The above is the default when the CLI is connected to a terminal or console window. When run as a batch script (when both standard input and standard output are connected to a file or pipe rather than a keyboard or terminal window) the default format is the same as it always has been for SQLite version 3.51.0 and earlier:
This "batch" mode is for legacy compatibility. You can force batch mode by launching the CLI with the "--batch" command-line option, or by issuing the ".mode batch" dot-command.
To see how the CLI is currently configured for result rendering, you can run the command ".mode" with no arguments, or run ".mode -v" for more detail.
There are many different output "modes". The default for terminals, where individual values are quoted and the whole result is displayed in a matrix composed from Unicode box-drawing characters, is called "qbox". The default for batch mode is called "list". You can get a complete list of all available modes by running the command ".mode --list"
To change output modes, run a command like ".mode NAME" where NAME is one of the built-in modes named in the output of the ".mode --list" command. There are also many options available to fine-tune how results are displayed. You can probably discern many of those options by examining the output of the ".mode -v" command.
The default output mode for scripts (and for all instances of the CLI prior to 3.52.0) is "list". In list mode, each row of a query result is written on one line of output and each column within that row is separated by a specific separator string. The default separator is a pipe symbol ("|"). List mode is especially useful when you are going to send the output of a query to another program (such as AWK) for additional processing.
sqlite> .mode list
sqlite> CREATE TABLE tbl1(one,two);
sqlite> INSERT INTO tbl1 VALUES('hello!',10),('goodbye',20);
sqlite> SELECT * FROM tbl1;
hello!|10
goodbye|20
sqlite>
Use the "--colsep STRING" option on the ".mode" command to select a different column separator string. For example, to change the separator to a comma and a space, you could do this:
sqlite> .mode list --colsep ", " sqlite> select * from tbl1; hello!, 10 goodbye, 20 sqlite>
Changing to a different mode might reset the column separator, or various other settings, back to a default value for the new mode. So you might need to repeat the "--colsep" option whenever you change modes if you want to continue using a non-standard separator.
In "quote" mode, the output is formatted as SQL literals. Strings are enclosed in single-quotes and internal single-quotes are escaped by doubling. Blobs are displayed in hexadecimal blob literal notation (Ex: x'abcd'). Numbers are displayed as ASCII text and NULL values are shown as "NULL". All columns are separated from each other by a comma.
sqlite> .mode quote sqlite> select * from tbl1; 'hello!',10 'goodbye',20 sqlite>
Quote mode is really an alias for list mode with some setting changes. The following two .mode commands give the same end result:
sqlite> .mode quote sqlite> .mode list -quote on -colsep ","
Several output modes are convenience aliases for "list" with alternative values for the "--quote", "--colsep", "--rowsep", and "--null". Here is a complete list:
Mode --quote --colsep --rowsep --null ascii csv "\037" "\036" "" c tcl , "\n" NULL csv csv , "\r\n" "" jatom json , "\n" null list off | "\n" "" quote on , "\n" NULL tables csv "\t" "\n" "" tcl tcl , "\n" ""
In "line" mode, each column in a row of the database is shown on a line by itself. Each line consists of the column name, a colon, and the column data. Successive records are separated by a blank line. Here is an example of line mode output:
sqlite> .mode line sqlite> select * from tbl1; one: hello! two: 10 one: goodbye two: 20 sqlite>
The "line" mode is particularly useful when displaying results that have a large number of columns, too many columns to fit on a single line of your display. Line mode avoids overflowing lines of your display and thus makes the output easier to read.
Before version 3.52.0, the separator was an equal sign rather than a colon. With 3.52.0 and later, the default separator is an equal sign, but you can change it using the "--colsep TEXT" option to ".mode" where TEXT is the desired separator. Hence, to get the legacy formatting, you can say:
sqlite> .mode line --colsep " = "
In tabular modes, each record is shown on a separate line with the data aligned in columns. The "column" mode is an example:
sqlite> .mode column sqlite> select * from tbl1; one two -------- --- hello! 10 goodbye 20 sqlite>
In "column" mode (and also in "box", "psql", "qbox", "table", and "markdown") the width of columns adjusts automatically. You can override this, providing a specified width for each column using the "--width" option to the ".mode" command. The argument to "--width" is a list of space- or comma-separated integers which are the number characters to devote to each column. Negative numbers mean right-justify. A value of 0 means the width of the column automatically adjusts to its content. The default value for "--width" is 0 and 0 is also the default for any column to the right of those that are specified using "--width". Thus:
sqlite> .mode --width 12,-6 sqlite> select * from tbl1; one two ------------ ------ hello! 10 goodbye 20 sqlite>
The command ".mode --width 0" resets all column widths back to zero and hence causes all column widths to be determined automatically.
Here are examples of other tabular modes:
sqlite> .mode box --widths 0 sqlite> select * from tbl1; ╭─────────┬─────╮ │ one │ two │ ╞═════════╪═════╡ │ hello! │ 10 │ │ goodbye │ 20 │ ╰─────────┴─────╯ sqlite> .mode markdown sqlite> select * from tbl1; | one | two | |---------|-----| | hello! | 10 | | goodbye | 20 | sqlite> .mode psql sqlite> select * from tbl1; one | two ---------+----- hello! | 10 goodbye | 20 sqlite> .mode qbox sqlite> select * from tbl1; ╭───────────┬─────╮ │ one │ two │ ╞═══════════╪═════╡ │ 'hello!' │ 10 │ │ 'goodbye' │ 20 │ ╰───────────┴─────╯ sqlite> .mode table sqlite> select * from tbl1; +---------+-----+ | one | two | +---------+-----+ | hello! | 10 | | goodbye | 20 | +---------+-----+ sqlite>
There "qbox" mode is really just a convenience variant of "box" that automatically enables "--quote on". Similarly "psql" (so named because it formats results in a manner similar to the "psql" command line utility of PostgreSQL) is really just "table" mode with the addition of the "--border off" option.
The tabular modes accept some additional options to control formatting. The "--wrap N" option (where N is an integer) causes columns to wrap text that is longer than N characters. Wrapping is disabled if N is zero.
sqlite> insert into tbl1 values('The quick fox jumps over a lazy brown dog.',90);
sqlite> .mode box --wrap 30
sqlite> select * from tbl1 where two>50;
╭────────────────────────────────┬─────╮
│ one │ two │
╞════════════════════════════════╪═════╡
│ The quick fox jumps over a laz │ 90 │
│ y brown dog. │ │
╰────────────────────────────────┴─────╯
sqlite>
Wrapping happens after exactly N characters, which might be in the middle of a word. To wrap at a word boundary, add the "--wordwrap on" option (or just "-ww" for short):
sqlite> .mode box --wrap 30 -ww sqlite> select * from tbl1 where two>50; ╭────────────────────────────────┬─────╮ │ one │ two │ ╞════════════════════════════════╪═════╡ │ The quick fox jumps over a │ 90 │ │ lazy brown dog. │ │ ╰────────────────────────────────┴─────╯ sqlite>
The command ".mode box --wrap 60 --quote on" is so useful for general-purpose database queries that it is given its own alias. Instead of typing out that whole 30-character command, you can just say ".mode qbox".
Another useful output mode is "insert". In insert mode, the output is formatted to look like SQL INSERT statements. Use insert mode to generate text that can later be used to input data into a different database.
When specifying insert mode, you have to give an extra argument which is the name of the table to be inserted into. For example:
sqlite> .mode insert new_table
sqlite> select * from tbl1 where two<50;
INSERT INTO "new_table" VALUES('hello',10);
INSERT INTO "new_table" VALUES('goodbye',20);
sqlite>
If .headers on is active, the INSERT output will include that list of column names:
sqlite> .mode insert mytable sqlite> .headers on sqlite> select 1 a, 2 b, 3 c; INSERT INTO mytable(a,b,c) VALUES(1,2,3); sqlite>
The column-split output mode, called use "split" and invoked using ".mode split", is a special case of the tabular "column" mode. The "split" mode only works on queries that return a single column and when the "--screenwidth" option is not "off". Instead of displaying the results in a single vertical column, the "split" mode tries to wrap that column vertically so that the final output is two or more columns. The CLI creates as many columns as will fit on the screen.
The following example demonstrates using the "split" mode to list all built-in SQL functions whose names begin with "c" on a screen that is 55 characters wide:
sqlite> .mode --once split --sw 55 sqlite> 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>
The CLI uses "split" mode internally to help it implement dot-commands like .tables
The output modes "count" and "off" do not show any actual query results. Instead they show just the number of rows of results, or nothing at all, respectively. These modes are useful for running timing tests on queries that generate a lot of output where you do not care what the output is but only how long it takes to compute.
Two aliases "batch" and "tty" mean the default output mode for when the CLI is started as a script ("batch") and when the CLI is started from a terminal or console ("tty").
The "--tag" option allows new customized output modes to be defined and named. The argument to --tag is the name of a new mode. All settings defined before the --tag are captured in that name and can be restored at a later time using ".mode TAGNAME".
By default (since version 3.50.0), the CLI avoids displaying most control characters in the range of U+0001 through U+001f even when such characters are within content strings that the CLI is trying to output. This is done to avoid problems when the output is viewed on a device that interprets ANSI escape codes. The CLI avoids showing most control characters, but makes the following exceptions: TAB (0x09), LF (0x0a) and CRLF (0x0d,0x0a). The exceptions are passed through into the output unchanged.
The default behavior is for the CLI to display most control characters the same way the "cat -A" command does in Linux: For control character X, display two characters "^Y" where Y is X+0x40. For example, the "ESC" character (0x1b) is rendered as "^[" and the backspace character (0x08) is rendered as "^H".
The rendering of control characters is governed by the "--escape T" command-line option to the CLI, or to the ".mode" dot-command, where T is the control-character handling algorithm. The default T is "ascii". If you change it to "--escape symbol" then control characters are mapped into printable unicode values in the range of U+2401 through U+241f. For example, ESC is rendered as "␛" and backspace becomes "␈". Setting "--escape off" turns off all control character mapping so that the characters output by the CLI are exactly what are in the database.
The default line ending on Windows can be either "\r\n" (CRLF) or "\n" NL. Even though it is closely related to output formatting, the line ending choice is controlled by a completely separate subsystem of the CLI implementation and thus has its own ".crlf" dot-command, separate from ".mode".
Use ".crlf on" to set the CRLF line ending and ".crlf off" for NL. As is traditional for Windows, CRLF is the default. However, this causes some outputs to be different than on non-Windows platforms due to the added "\r" characters. To cause the CLI to output results on Windows that are identical to the results on all other systems, run ".crlf off".
On non-Windows platforms, the ".crlf" command is a no-op and the crlf mode is always "off". For CSV output, the line ending is always "\r\n" regardless of the .crlf setting, due to requirements of RFC-4180.
The mode command works by processing its arguments from left to right. Order can be important. For example, settings to the left of the "--reset" option will likely be rolled back because the reset is processed after those settings. But settings to the right will survive because the settings are changed after the reset.
Use the ".help mode" to get a quick summary of the options available on the ".mode" command. Keep reading below for details.
The alignment value is a string composed of characters 'L', 'C', and 'R' that determine how text is justified in tabular modes. 'L' means left (the default), 'C' means centered, and 'R' means right. Each letter in the string corresponds to a single column in the tabular output. If the output has more columns than there are letters in the alignment string, then the remaining columns are all left-justified.
The --blob-quote option (which can be abbreviated as just "--blob") determines how BLOB values are displayed. The argument can be one of "auto", "text", "sql", "hex", "tcl", "json", or "size".
The default --blob-quote setting is "auto" which means that the system selects an appropriate method of displaying BLOB values based on the current mode and/or the current "--quote" setting. The default is "auto" and that works best in most circumstances.
If ARG is "text", that means that the bytes of the BLOB value are interpreted as UTF-8 text and displayed as text. "sql" means that the BLOB is rendered as an SQL BLOB literal. "hex" means the BLOB value is displayed as hexadecimal. "tcl" means that the BLOB value is rendered as a double-quoted string where each byte is represented by traditional C-language backslash octal notation. For example, a BLOB value of x'424c4f42' would be rendered as "\102\114\117\102". The "json" encoding is similar except that the individual bytes use JSON escape: "\u0042\u004c\u004f\u0042". The "size" encoding does not try to show the BLOB content at all, but instead just shows the size of the BLOB as text, like this: (482-byte blob)
Limit the number of characters to display for a single value in the output. If the output text is longer than N characters, it is truncated and an ellipsis are added at the end. If N is zero, then the character limit is disabled.
The character limit works in all output modes.
See also the "--linelimit" option, the "--limits" option, and the "--titlelimit" option.
The --border setting control whether or not a box appears around the outside of the entire result, or if only the interior divider lines are shown. This setting only affects "box" and "table" modes. The value of this setting is "off" then the border is omitted. Otherwise the border is shown. The default behavior is to show the outer border. The "psql" mode is an alias for the "table" mode but with the --border setting changed to "off".
Set the column-separator to STRING. Use double-quotes and backslash-escapes to use spaces and special characters like tab (\t) as part of the column-separator.
Control how control characters are escaped in all output. Possible values for ESC are "off", "ascii", and "symbol". The "ascii" value is the default and is recommended.
Do not display more than the first N lines of text for any single value. Lines can be split due to the text containing newline characters (U+000a) or due to column wrapping. Either way, this option causes the first N lines to be shown and then shows ellipsis for the N+1-th line. Set N to zero to disable the line limiter.
The line limiter only works for tabular modes and for "line" mode. It is ignored in all other modes.
See also the "--charlimit" option, the "--limits" option, and the "--titlelimit" option
This option is shorthand for "--linelimit L --charlimit C --titlelimit T". In other words, when the argument is three integers L, C, and T separate by commas, then the line limit, the character limit, and the title limit are set to L, C, and T, respectively. If the ",T" is omitted, then only the line limit and character limit are changed and the title limit is unchanged. The argument to --limits can also be "off" which is an alias for "0,0,0" or "on" which sets all limits to reasonable built-in defaults.
The "--list" option takes no arguments. It causes the ".mode" command to print a list of all available modes, including user-defined modes created using the "--tag" option.
The STRING argument becomes the text that the CLI displays for NULL values. Common choices are an empty string (which must be quoted as ""), "null", and "NULL".
The "--once" option means all setting changes to the right are transient and will rollback after the next SQL statement is run.
In the previous sentence, "next SQL statement" means the all SQL statements up to and including the next complete line of SQL input that ends with a semicolon. If two or more SQL queries occur on the same input line, all of them will be run before the mode is rolled back. So if you do (for example) ".mode --once split" then enter two SELECT statements on the next line of input, both of those SELECTs will be processed using "split" mode.
The --quote option determines how text values are quoted prior to rendered. Possible arguments are: "off", "on", "sql", "relaxed", "csv", "html", "tcl", or "json".
When --quote is "off" the text is displayed as it appears in the database with no escapes.
The --quote value of "sql" means to display text as SQL text literals within single-quotes. A value of "relaxed" is similar, except that "relaxed" tries to avoid adding extra quoting syntax in cases where it is not needed. A value of "csv" means to quote text according to the rules specified for CSV file in RFC-4180. A value of "html" means to escape text for display in HTML, so that "<" characters are rendered as "<", and so forth. "tcl" means to put the text inside double-quotes and use backslash escape, as if for TCL, C, Perl, and other common programming languages. "json" is similar to "tcl" except that it uses only backslash escapes that are valid JSON.
The value of "on" is similar to "sql" except that "on" also changes the "--null" option to be "NULL".
The --reset option leaves the current mode unchanged, but it sets all the other various settings (--colsep, --null, --rowsep, and so forth) back to whatever the default is for that particular mode.
Use STRING as the row separator.
This setting tells the CLI that the display device is N characters wide. The CLI then attempts to squeeze tabular and "line" mode outputs to fit within that many characters. If the argument is 0 or "off", that means the screenwidth is ignored an no attempt is made to squeeze the results down to any particular width.
The default value is "auto" which causes the CLI interrogate the display device to determine its actual width before each query is run. With the "auto" value, if you are running the CLI in a terminal or console window and you drag the window wider, the screenwidth setting is adjusted automatically.
The "--sw N" setting is a shorthand for "--screenwidth N".
The screenwidth is only used by tabular modes and by the line mode. Screenwidth limiting is "best effort" in the sense that the CLI works hard to squeeze the output to fit tha specified width, but might exceed the specified screenwidth if there is no way around it. For example, if the --screenwidth is 40 and you attempt to display a result with 50 different columns, there is clearly no way that will fit, and the screenwidth will be exceeded in that case.
Use "NAME" as the name of the table to insert into for "insert" mode.
This option creates a new mode called NAME. After using this tag, you can say ".mode NAME" and all mode settings that were in effect when the --tag option was first executed will be restored.
The order of options is important here. The new mode will consist of all mode settings that are in effect at the moment that the "--tag" option is countered. Hence changes to the left of "--tag" are part of the new mode, but changes to the right of "--tag" are not.
If --testjsonb is enabled, then when displaying a BLOB value, the CLI first checks to see if the value is really a JSONB blob, and if it is, it displays the equivalent text for that JSONB using whatever quoting mechanism is specified by the "--quote" option. If the BLOB to be displayed is not JSONB, then it is rendered using the current "--blob-quote" option.
The --title option determines whether or not to render column headers, and if so what text encoding to use for column headers. Whether or not to show column headers, and if so how to encode them. ARG can be "off", "on", "sql", "csv", "html", "tcl", or "json".
The --titlelimit option specifies a maximum width for the column titles in tabular and "line" modes. (Other modes are not currently affected by --titlelimit, but that could change in future releases.) If a column title is longer than the specified number of characters, then the title is truncated. Also, newline and tab characters within the title or changed into ordinary spaces.
The --titlelimit feature is useful to prevent oversize column titles when entering queries that use subqueries or complex expressions as result columns. Without an AS clause, the titles for such columns default to the complete text of the subquery or expression. That can result in unweldy tabular and/or line-mode output. Setting --titlelimit to a positive number (usually between about 10 or 20) can ensure more reasonable looking output. If --titlelimit is zero, then no limits on the length of the title are imposed.
See also the "--charlimit option, the "--linelimit" option, and the "--limits" option.
If enabled, then the display of the current mode and settings shows every possible setting, even settings that currently hold their default value.
The "-v" option is shorthand for "--verbose".
Set the columns widths for tabular modes. The argument is a list of integers, one for each column. A "0" width means use a dynamic width based on the actual width of data. If there are fewer entries in LIST than columns, "0" is used for the unspecified widths.
When the "--wrap" option requires longer texts to be wrapped, this option determine whether wrapping occurs at a word boundary or in the middle of a word. A value of true means that the CLI attempts to find a suitable word boundary at which to wrap.
For really long words, it might not possible find a word boundry, in which case the wrapping might still occur mid-word.
In tabular modes, this setting prevents any column from growing wider than N characters. If the text to be displayed is wider than N character, then the text wraps. An N value of 0 means there is no limit and table columns can be as wide as necessary to accomodate the text.
The "--ww" option, which does not take an argument, is simply a shorthand notation for "--wordwrap on".
This page was last updated on 2025-12-11 02:12:55Z
*** DRAFT ***