To get SQLite query results as a Markdown table, open the sqlite3 command-line shell (version 3.33.0 or newer), type .mode markdown, and run your query. Or do it in one line from your terminal and write the table straight to a file:
sqlite3 -markdown shop.db "SELECT * FROM customers;" > customers.md
The file customers.md now contains a ready-to-use Markdown table:
| name | age | city | |------|-----|-----------| | Erik | 40 | Amsterdam | | Mary | 53 | London | | Zoë | 29 | Berlin |
The rest of this article shows the interactive way, how to write results to a file from inside the shell, HTML output, and the gotchas I ran into: pipes and newlines in your data, NULL values, and the display limits that newer sqlite3 versions apply to interactive sessions. Everything below is real output from sqlite3 on my machine.
Example database
If you want to follow along, create a small database. Start the shell with sqlite3 shop.db (it creates the file if it doesn’t exist) and paste this:
CREATE TABLE customers (name TEXT, age INTEGER, city TEXT);
INSERT INTO customers VALUES
('Erik', 40, 'Amsterdam'),
('Mary', 53, 'London'),
('Zoë', 29, 'Berlin');
To check which version you have, run sqlite3 --version. I used two versions while writing this: 3.51.0, which ships with macOS, and 3.53.4 from Homebrew. The examples show the 3.53.4 output. Where the versions behave differently, I’ll tell you.
$ sqlite3 --version 3.53.4 2026-07-24 19:02:57 bf7c7f30031888f4e796e429ab3978879485813aaca6f641c7b33e4e09459bcc (64-bit)
A note on the prompt: my examples show the classic sqlite> prompt, which is what 3.53 and older show. According to the SQLite release notes, version 3.54.0 introduced a new default prompt with the version and database name in it, like SQLite-3.54 shop.db->. The commands are the same.
Using SQLite’s Markdown mode
By far the easiest method is to change the output mode to markdown. Enter .mode markdown in the sqlite3 shell, and every query after that prints a Markdown table. This mode was added in SQLite 3.33.0, together with the box, table, and json modes. Dot-commands like this only work in the sqlite3 shell, not in GUI tools like DB Browser for SQLite. Because the output looks so clear, I use it all the time, even when I don’t want to export to Markdown:
sqlite> .mode markdown sqlite> SELECT * FROM customers; | name | age | city | |------|-----|-----------| | Erik | 40 | Amsterdam | | Mary | 53 | London | | Zoë | 29 | Berlin |
You can copy/paste this into a Markdown file as-is. Rendered, it looks like this:
| name | age | city |
|---|---|---|
| Erik | 40 | Amsterdam |
| Mary | 53 | London |
| Zoë | 29 | Berlin |
Two details. First, version 3.53 right-justifies numbers in the text output, while 3.51 and older left-justify them (| Erik | 40 |). That’s only spacing; it makes no difference once the Markdown is rendered. Second, sqlite3 never adds alignment colons to the separator row. If you want a right-aligned number column in the rendered table, change |-----| to |----:| yourself, as explained in aligning columns.
Export to a Markdown file with one command
You don’t have to start an interactive session. The -markdown option sets the output mode, and you pass the query as an argument. Redirect the output to save it:
sqlite3 -markdown shop.db "SELECT * FROM customers;" > customers.md
For a longer query, put it in a file and feed that to sqlite3:
sqlite3 -markdown shop.db < report.sql > report.md
This is my preferred way to export, especially from scripts. When the output goes to a file or a pipe, sqlite3 doesn’t wrap or truncate long values, so you get your data exactly as it is in the database. I tested that with 3.51 and 3.53. It’s not always the case when the output goes to your screen, as you’ll see below.
I stick with the -markdown option here. You can also pass ".mode markdown" as an argument before the query, but when I ran that from a terminal, 3.51 wrapped long values even though the output went to a file.
Write results to a file with .once and .output
Inside the shell, .once FILE sends the output of the next query to a file, and then switches back to the screen:
sqlite> .mode markdown sqlite> .once customers.md sqlite> SELECT * FROM customers;
Nothing is printed, because the table went into customers.md. If you want to collect the results of several queries in one file, use .output FILE. Everything goes to that file until you type .output without a file name. Add an empty line between the tables with .print ""; without it, the second table is glued to the first and becomes part of it when rendered:
sqlite> .output report.md sqlite> SELECT name FROM customers WHERE age > 35; sqlite> .print "" sqlite> SELECT count(*) AS total FROM customers; sqlite> .output
The resulting report.md:
| name | |------| | Erik | | Mary | | total | |-------| | 3 |
Both commands are described in the sqlite3 documentation, which also lists a few handy extras, like .once -e to open the result in your text editor.
Long values get wrapped or cut off
This one surprised me. In an interactive session, sqlite3 formats results for your screen, and that can break your Markdown. The exact behavior depends on the version.
SQLite 3.53 and newer: display limits
Since 3.53.0, an interactive session shows results in a box by default, with display limits: according to the output mode documentation, values are cut off after 5 lines or 300 characters, column titles after 20 characters, and the table is squeezed to fit your terminal width. (The 3.52.0 release was withdrawn, and its changes shipped in 3.53.0.) Run .mode without arguments to see the current settings:
sqlite> .mode .mode qbox --limits on --quote relaxed --sw auto --textjsonb on
The problem: when you switch to .mode markdown, those limits stay on. Here’s a column with a long name:
sqlite> .mode markdown sqlite> SELECT count(*) AS number_of_customers_in_the_shop FROM customers; | number_of_custome... | |----------------------| | 3 | sqlite> .mode markdown --reset sqlite> SELECT count(*) AS number_of_customers_in_the_shop FROM customers; | number_of_customers_in_the_shop | |---------------------------------| | 3 |
The --reset option resets all settings to the defaults for that mode, which for markdown means no limits and no squeezing to the screen width. This matters for files too: .once and .output in an interactive session use the same settings, so in my test a 400-character value ended up wrapped and cut off inside the exported file. So in 3.53 and newer, I always use .mode markdown --reset. The same goes for the one-liner when you let it print to your terminal instead of a file: 3.53 squeezes that table to your screen width too.
SQLite 3.51 and older: wrapping at 60 characters
Older versions don’t truncate, but in the versions I tested (3.50 and 3.51), interactive markdown mode wraps values longer than 60 characters over multiple rows:
sqlite> .mode markdown sqlite> .mode current output mode: markdown --wrap 60 --wordwrap off --noquote --escape ascii
Turn wrapping off with .mode markdown --wrap 0. Or skip the issue entirely and use the one-liner from the previous section, which doesn’t wrap or truncate in any version I tried.
Gotchas: pipes, newlines, and NULL values
sqlite3 writes your values into the table as-is. It doesn’t escape anything for Markdown, which causes trouble with some data. Let’s add a table with problematic values to shop.db:
CREATE TABLE notes (id INTEGER, note TEXT); INSERT INTO notes VALUES (1, 'Use a | to separate cells'), (2, 'Line one' || char(10) || 'Line two'), (3, NULL);
And here’s the Markdown output:
sqlite> .mode markdown sqlite> SELECT * FROM notes; | id | note | |----|---------------------------| | 1 | Use a | to separate cells | | 2 | Line one | | | Line two | | 3 | |
It looks fine in the terminal, but this is what GitHub makes of it:
| id | note |
|---|---|
| 1 | Use a |
| 2 | Line one |
| Line two | |
| 3 |
Three problems:
- Pipe characters start a new cell. GitHub drops the extra cell, so everything after the pipe silently disappears.
- Newlines become a new row, so one value is spread over two rows.
- NULL shows up as an empty cell, so you can’t tell it apart from an empty string.
The fix is to escape pipes as \|, replace newlines with <br>, and set a visible text for NULL with .nullvalue:
sqlite> .nullvalue NULL sqlite> SELECT id, replace(replace(note, '|', '\|'), char(10), '<br>') AS note FROM notes; | id | note | |----|----------------------------| | 1 | Use a \| to separate cells | | 2 | Line one<br>Line two | | 3 | NULL |
Now it renders correctly:
| id | note |
|---|---|
| 1 | Use a | to separate cells |
| 2 | Line one Line two |
| 3 | NULL |
Also keep in mind that HTML and Markdown inside your values pass through untouched. A value like **bold** renders as bold, and <b> is treated as HTML on platforms that allow it. HTML mode, below, does escape < and >.
Using HTML mode
A good alternative is to change the output mode to HTML. Most Markdown renderers accept HTML, and an HTML table gives you more options than a Markdown table, like line breaks and lists inside cells, or merged cells.
To enable HTML output mode, enter .mode html in the sqlite3 shell. In 3.51 and older, you also need .headers on to get a header row; in 3.53 the header row is there either way. Here’s an example:
sqlite> .headers on sqlite> .mode html sqlite> SELECT name, age FROM customers LIMIT 2; <TR> <TH>name <TH>age </TR> <TR> <TD>Erik <TD>40 </TR> <TR> <TD>Mary <TD>53 </TR>
Version 3.51 writes the same rows with closing tags, like <TD>Erik</TD>. Both are valid HTML, since closing <td> and <th> tags are optional. As you can see, you get the rows but not the table itself. Wrap the output in <table> and </table> and you’re done:
| name | age |
|---|---|
| Erik | 40 |
| Mary | 53 |
Two more things I noticed. The display limits from the previous section apply here too, so in 3.53 use .mode html --reset for long values. And in 3.53, HTML mode printed NULL values as null, ignoring .nullvalue. If you want an empty cell instead, use coalesce(column, '') in your query.
Pretty tables in your terminal: table and box mode
If you just want readable output while you work, try .mode table or .mode box. These aren’t meant for Markdown files, but they’re great for looking at your data:
sqlite> .mode table sqlite> SELECT * FROM customers; +------+-----+-----------+ | name | age | city | +------+-----+-----------+ | Erik | 40 | Amsterdam | | Mary | 53 | London | | Zoë | 29 | Berlin | +------+-----+-----------+ sqlite> .mode box sqlite> SELECT * FROM customers; ╭──────┬─────┬───────────╮ │ name │ age │ city │ ╞══════╪═════╪═══════════╡ │ Erik │ 40 │ Amsterdam │ │ Mary │ 53 │ London │ │ Zoë │ 29 │ Berlin │ ╰──────┴─────┴───────────╯
In 3.51, box mode draws square corners instead of rounded ones. In 3.53 and newer, interactive sessions already default to qbox, a box variant that quotes values when needed. Run .mode --list to see all available modes.
Alternative: export with Python
If you’re already in Python, you don’t need the sqlite3 shell. The standard library is enough to write a Markdown table, and this way you control the escaping:
import sqlite3
def cell(value):
if value is None:
return ""
return str(value).replace("|", "\\|").replace("\n", "<br>")
con = sqlite3.connect("shop.db")
cur = con.execute("SELECT * FROM notes")
headers = [col[0] for col in cur.description]
print("| " + " | ".join(headers) + " |")
print("|" + "|".join("---" for _ in headers) + "|")
for row in cur:
print("| " + " | ".join(cell(v) for v in row) + " |")
The output:
| id | note | |---|---| | 1 | Use a \| to separate cells | | 2 | Line one<br>Line two | | 3 | |
If you use pandas, DataFrame.to_markdown() does the formatting for you. It needs the tabulate package, so install both with pip install pandas tabulate; without tabulate you get an ImportError.
import sqlite3
import pandas as pd
con = sqlite3.connect("shop.db")
df = pd.read_sql_query("SELECT * FROM customers", con)
print(df.to_markdown(index=False))
The output, with alignment colons for free:
| name | age | city | |:-------|------:|:----------| | Erik | 40 | Amsterdam | | Mary | 53 | London | | Zoë | 29 | Berlin |
Frequently asked questions
Which SQLite version do I need for Markdown output?
Version 3.33.0 or newer. That release added the markdown, box, table, and json output modes. Check your version with sqlite3 --version. Older versions don’t have the markdown mode, so use HTML mode or the Python example instead.
Does .mode markdown work in DB Browser for SQLite or other GUI tools?
No. Dot-commands like .mode are part of the sqlite3 command-line shell, not of the SQL language. GUI tools have their own export features. If yours doesn’t export Markdown, run the query in the sqlite3 shell or use the Python example above.
How do I install the sqlite3 command-line shell?
macOS comes with sqlite3. On Debian and Ubuntu, install the sqlite3 package with apt. On Windows, download the command-line tools bundle from the SQLite download page.
Why is my output cut off with “…”?
You’re using sqlite3 3.53 or newer, which applies display limits to long values and column names when it prints to your screen. Use .mode markdown --reset, or run sqlite3 -markdown with the output redirected to a file.
Can sqlite3 add alignment to the Markdown table?
Not in the versions I tested. In 3.53, .mode markdown --align LRC only changes the padding in the text output; the separator row stays plain dashes, so it renders the same. Add the colons by hand, or use the pandas example, which adds them automatically.
Conclusion
For Markdown output, use .mode markdown in the sqlite3 shell, or sqlite3 -markdown db "query" > file.md from your terminal. On 3.53 and newer, add --reset in interactive sessions, and watch out for pipes and newlines in your data. If you need more control over the table, HTML mode is a good alternative. To learn more about the tables themselves, read my article on Markdown tables, and keep the Markdown cheat sheet around for the rest of the syntax.