<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Meet Gor on Medium]]></title>
        <description><![CDATA[Stories by Meet Gor on Medium]]></description>
        <link>https://medium.com/@meet-gor?source=rss-14f575a241a9------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*dxZE4tAXvA9BhK2j.jpg</url>
            <title>Stories by Meet Gor on Medium</title>
            <link>https://medium.com/@meet-gor?source=rss-14f575a241a9------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 22:30:39 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@meet-gor/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[SQLite dot commands: header command]]></title>
            <link>https://meet-gor.medium.com/sqlite-dot-commands-header-command-8d4bfa1bb240?source=rss-14f575a241a9------2</link>
            <guid isPermaLink="false">https://medium.com/p/8d4bfa1bb240</guid>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sqlite]]></category>
            <category><![CDATA[relational-databases]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Meet Gor]]></dc:creator>
            <pubDate>Sat, 20 Sep 2025 12:04:23 GMT</pubDate>
            <atom:updated>2025-09-20T12:04:23.662Z</atom:updated>
            <content:encoded><![CDATA[<p>The .headers command is crucial as might effect the way the result set is displayed. The first row would be set to the name of the columns in the output of the relevant commands like .output, .once, or even your simple standard out queries if it is set on.</p><pre>.headers on</pre><pre>OR</pre><pre>.header on</pre><p>Some how either of them work. You need to set it to on to enable the headers in the output of the result set. And you can turn off with .headers off.</p><p>Which modes are effected with this command if set on or off?</p><p>Well we need to think about how effected means</p><ol><li>Only added in the first row</li><li>Added in each row</li><li>No effect</li><li>Only added in the first row</li></ol><ul><li>ascii</li><li>column</li><li>csv</li><li>html</li><li>list</li><li>quote</li><li>tabs</li><li>tcl</li></ul><p>2. Added in each row</p><ul><li>insert</li></ul><p>3. No effect</p><ul><li>box</li><li>json</li><li>line</li><li>markdown</li><li>table</li></ul><p>Reference table</p><pre>CREATE TABLE IF NOT EXISTS books(<br>    id INTEGER PRIMARY KEY AUTOINCREMENT,<br>    title TEXT NOT NULL,<br>    author TEXT NOT NULL,<br>    pages INTEGER NOT NULL,<br>    release_date TEXT NOT NULL,<br>    price REAL NOT NULL<br>);</pre><pre>INSERT INTO books(title, author, pages, release_date, price)<br>VALUES<br>(&#39;The Hobbit&#39;, &#39;J.R.R. Tolkien&#39;, 310, &#39;1937-09-21&#39;, 39.99),<br>(&#39;The Fellowship of the Ring&#39;, &#39;J.R.R. Tolkien&#39;, 423, &#39;1954-07-29&#39;, 49.99),<br>(&#39;The Two Towers&#39;, &#39;J.R.R. Tolkien&#39;, 352, &#39;1954-11-11&#39;, 49.99), <br>(&#39;The Return of the King&#39;, &#39;J.R.R. Tolkien&#39;, 416, &#39;1955-10-20&#39;, 49.99);</pre><pre>SELECT * FROM books;</pre><p>Below are the outputs of each command</p><h3>Added on the first row</h3><p>In the below modes, the headers command if set will add the column name as the header in the first row.</p><ol><li>ASCII</li></ol><pre>.mode ascii<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode ascii<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>idtitleauthorpagesrelease_dateprice1The HobbitJ.R.R. Tolkien3101937-09-2139.992The Fellowship of the RingJ.R.R. Tolkien4231954-07-2949.993The Two TowersJ.R.R. Tolkien3521954-11-1149.994The Return of the KingJ.R.R. Tolkien4161955-10-2049.99sqlite&gt;<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>1The HobbitJ.R.R. Tolkien3101937-09-2139.992The Fellowship of the RingJ.R.R. Tolkien4231954-07-2949.993The Two TowersJ.R.R. Tolkien3521954-11-1149.994The Return of the KingJ.R.R. Tolkien4161955-10-2049.99sqlite&gt;<br>sqlite&gt;</pre><ol><li>Column</li></ol><pre>.mode column<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output</p><pre>sqlite&gt; .mode column<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>id  title                       author          pages  release_date  price<br>--  --------------------------  --------------  -----  ------------  -----<br>1   The Hobbit                  J.R.R. Tolkien  310    1937-09-21    39.99<br>2   The Fellowship of the Ring  J.R.R. Tolkien  423    1954-07-29    49.99<br>3   The Two Towers              J.R.R. Tolkien  352    1954-11-11    49.99<br>4   The Return of the King      J.R.R. Tolkien  416    1955-10-20    49.99<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>1   The Hobbit                  J.R.R. Tolkien  310    1937-09-21    39.99<br>2   The Fellowship of the Ring  J.R.R. Tolkien  423    1954-07-29    49.99<br>3   The Two Towers              J.R.R. Tolkien  352    1954-11-11    49.99<br>4   The Return of the King      J.R.R. Tolkien  416    1955-10-20    49.99<br>sqlite&gt;</pre><ol><li>CSV</li></ol><pre>.mode csv<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode csv<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>id,title,author,pages,release_date,price<br>1,&quot;The Hobbit&quot;,&quot;J.R.R. Tolkien&quot;,310,1937-09-21,39.99<br>2,&quot;The Fellowship of the Ring&quot;,&quot;J.R.R. Tolkien&quot;,423,1954-07-29,49.99<br>3,&quot;The Two Towers&quot;,&quot;J.R.R. Tolkien&quot;,352,1954-11-11,49.99<br>4,&quot;The Return of the King&quot;,&quot;J.R.R. Tolkien&quot;,416,1955-10-20,49.99<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>1,&quot;The Hobbit&quot;,&quot;J.R.R. Tolkien&quot;,310,1937-09-21,39.99<br>2,&quot;The Fellowship of the Ring&quot;,&quot;J.R.R. Tolkien&quot;,423,1954-07-29,49.99<br>3,&quot;The Two Towers&quot;,&quot;J.R.R. Tolkien&quot;,352,1954-11-11,49.99<br>4,&quot;The Return of the King&quot;,&quot;J.R.R. Tolkien&quot;,416,1955-10-20,49.99<br>sqlite&gt;</pre><ol><li>HTML</li></ol><pre>.mode html<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode html<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>&lt;TR&gt;&lt;TH&gt;id&lt;/TH&gt;<br>&lt;TH&gt;title&lt;/TH&gt;<br>&lt;TH&gt;author&lt;/TH&gt;<br>&lt;TH&gt;pages&lt;/TH&gt;<br>&lt;TH&gt;release_date&lt;/TH&gt;<br>&lt;TH&gt;price&lt;/TH&gt;<br>&lt;/TR&gt;<br>&lt;TR&gt;&lt;TD&gt;1&lt;/TD&gt;<br>&lt;TD&gt;The Hobbit&lt;/TD&gt;<br>&lt;TD&gt;J.R.R. Tolkien&lt;/TD&gt;<br>&lt;TD&gt;310&lt;/TD&gt;<br>&lt;TD&gt;1937-09-21&lt;/TD&gt;<br>&lt;TD&gt;39.99&lt;/TD&gt;<br>&lt;/TR&gt;<br>&lt;TR&gt;&lt;TD&gt;2&lt;/TD&gt;<br>&lt;TD&gt;The Fellowship of the Ring&lt;/TD&gt;<br>&lt;TD&gt;J.R.R. Tolkien&lt;/TD&gt;<br>&lt;TD&gt;423&lt;/TD&gt;<br>&lt;TD&gt;1954-07-29&lt;/TD&gt;<br>&lt;TD&gt;49.99&lt;/TD&gt;<br>&lt;/TR&gt;<br>&lt;TR&gt;&lt;TD&gt;3&lt;/TD&gt;<br>&lt;TD&gt;The Two Towers&lt;/TD&gt;<br>&lt;TD&gt;J.R.R. Tolkien&lt;/TD&gt;<br>&lt;TD&gt;352&lt;/TD&gt;<br>&lt;TD&gt;1954-11-11&lt;/TD&gt;<br>&lt;TD&gt;49.99&lt;/TD&gt;<br>&lt;/TR&gt;<br>&lt;TR&gt;&lt;TD&gt;4&lt;/TD&gt;<br>&lt;TD&gt;The Return of the King&lt;/TD&gt;<br>&lt;TD&gt;J.R.R. Tolkien&lt;/TD&gt;<br>&lt;TD&gt;416&lt;/TD&gt;<br>&lt;TD&gt;1955-10-20&lt;/TD&gt;<br>&lt;TD&gt;49.99&lt;/TD&gt;<br>&lt;/TR&gt;<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>&lt;TR&gt;&lt;TD&gt;1&lt;/TD&gt;<br>&lt;TD&gt;The Hobbit&lt;/TD&gt;<br>&lt;TD&gt;J.R.R. Tolkien&lt;/TD&gt;<br>&lt;TD&gt;310&lt;/TD&gt;<br>&lt;TD&gt;1937-09-21&lt;/TD&gt;<br>&lt;TD&gt;39.99&lt;/TD&gt;<br>&lt;/TR&gt;<br>&lt;TR&gt;&lt;TD&gt;2&lt;/TD&gt;<br>&lt;TD&gt;The Fellowship of the Ring&lt;/TD&gt;<br>&lt;TD&gt;J.R.R. Tolkien&lt;/TD&gt;<br>&lt;TD&gt;423&lt;/TD&gt;<br>&lt;TD&gt;1954-07-29&lt;/TD&gt;<br>&lt;TD&gt;49.99&lt;/TD&gt;<br>&lt;/TR&gt;<br>&lt;TR&gt;&lt;TD&gt;3&lt;/TD&gt;<br>&lt;TD&gt;The Two Towers&lt;/TD&gt;<br>&lt;TD&gt;J.R.R. Tolkien&lt;/TD&gt;<br>&lt;TD&gt;352&lt;/TD&gt;<br>&lt;TD&gt;1954-11-11&lt;/TD&gt;<br>&lt;TD&gt;49.99&lt;/TD&gt;<br>&lt;/TR&gt;<br>&lt;TR&gt;&lt;TD&gt;4&lt;/TD&gt;<br>&lt;TD&gt;The Return of the King&lt;/TD&gt;<br>&lt;TD&gt;J.R.R. Tolkien&lt;/TD&gt;<br>&lt;TD&gt;416&lt;/TD&gt;<br>&lt;TD&gt;1955-10-20&lt;/TD&gt;<br>&lt;TD&gt;49.99&lt;/TD&gt;<br>&lt;/TR&gt;<br>sqlite&gt;</pre><ol><li>List</li></ol><pre>.mode list<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode list<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>id|title|author|pages|release_date|price<br>1|The Hobbit|J.R.R. Tolkien|310|1937-09-21|39.99<br>2|The Fellowship of the Ring|J.R.R. Tolkien|423|1954-07-29|49.99<br>3|The Two Towers|J.R.R. Tolkien|352|1954-11-11|49.99<br>4|The Return of the King|J.R.R. Tolkien|416|1955-10-20|49.99<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>1|The Hobbit|J.R.R. Tolkien|310|1937-09-21|39.99<br>2|The Fellowship of the Ring|J.R.R. Tolkien|423|1954-07-29|49.99<br>3|The Two Towers|J.R.R. Tolkien|352|1954-11-11|49.99<br>4|The Return of the King|J.R.R. Tolkien|416|1955-10-20|49.99<br>sqlite&gt;</pre><ol><li>Quote</li></ol><pre>.mode quote<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode quote<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>&#39;id&#39;,&#39;title&#39;,&#39;author&#39;,&#39;pages&#39;,&#39;release_date&#39;,&#39;price&#39;<br>1,&#39;The Hobbit&#39;,&#39;J.R.R. Tolkien&#39;,310,&#39;1937-09-21&#39;,39.99000000000000198<br>2,&#39;The Fellowship of the Ring&#39;,&#39;J.R.R. Tolkien&#39;,423,&#39;1954-07-29&#39;,49.99000000000000198<br>3,&#39;The Two Towers&#39;,&#39;J.R.R. Tolkien&#39;,352,&#39;1954-11-11&#39;,49.99000000000000198<br>4,&#39;The Return of the King&#39;,&#39;J.R.R. Tolkien&#39;,416,&#39;1955-10-20&#39;,49.99000000000000198<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>1,&#39;The Hobbit&#39;,&#39;J.R.R. Tolkien&#39;,310,&#39;1937-09-21&#39;,39.99000000000000198<br>2,&#39;The Fellowship of the Ring&#39;,&#39;J.R.R. Tolkien&#39;,423,&#39;1954-07-29&#39;,49.99000000000000198<br>3,&#39;The Two Towers&#39;,&#39;J.R.R. Tolkien&#39;,352,&#39;1954-11-11&#39;,49.99000000000000198<br>4,&#39;The Return of the King&#39;,&#39;J.R.R. Tolkien&#39;,416,&#39;1955-10-20&#39;,49.99000000000000198<br>sqlite&gt;</pre><ol><li>Tabs</li></ol><pre>.mode tabs<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode tabs<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>id      title   author  pages   release_date    price<br>1       The Hobbit      J.R.R. Tolkien  310     1937-09-21      39.99<br>2       The Fellowship of the Ring      J.R.R. Tolkien  423     1954-07-29      49.99<br>3       The Two Towers  J.R.R. Tolkien  352     1954-11-11      49.99<br>4       The Return of the King  J.R.R. Tolkien  416     1955-10-20      49.99<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>1       The Hobbit      J.R.R. Tolkien  310     1937-09-21      39.99<br>2       The Fellowship of the Ring      J.R.R. Tolkien  423     1954-07-29      49.99<br>3       The Two Towers  J.R.R. Tolkien  352     1954-11-11      49.99<br>4       The Return of the King  J.R.R. Tolkien  416     1955-10-20      49.99<br>sqlite&gt;</pre><ol><li>TCL</li></ol><pre>.mode tcl<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode tcl<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>&quot;id&quot; &quot;title&quot; &quot;author&quot; &quot;pages&quot; &quot;release_date&quot; &quot;price&quot;<br>&quot;1&quot; &quot;The Hobbit&quot; &quot;J.R.R. Tolkien&quot; &quot;310&quot; &quot;1937-09-21&quot; &quot;39.99&quot;<br>&quot;2&quot; &quot;The Fellowship of the Ring&quot; &quot;J.R.R. Tolkien&quot; &quot;423&quot; &quot;1954-07-29&quot; &quot;49.99&quot;<br>&quot;3&quot; &quot;The Two Towers&quot; &quot;J.R.R. Tolkien&quot; &quot;352&quot; &quot;1954-11-11&quot; &quot;49.99&quot;<br>&quot;4&quot; &quot;The Return of the King&quot; &quot;J.R.R. Tolkien&quot; &quot;416&quot; &quot;1955-10-20&quot; &quot;49.99&quot;<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>&quot;1&quot; &quot;The Hobbit&quot; &quot;J.R.R. Tolkien&quot; &quot;310&quot; &quot;1937-09-21&quot; &quot;39.99&quot;<br>&quot;2&quot; &quot;The Fellowship of the Ring&quot; &quot;J.R.R. Tolkien&quot; &quot;423&quot; &quot;1954-07-29&quot; &quot;49.99&quot;<br>&quot;3&quot; &quot;The Two Towers&quot; &quot;J.R.R. Tolkien&quot; &quot;352&quot; &quot;1954-11-11&quot; &quot;49.99&quot;<br>&quot;4&quot; &quot;The Return of the King&quot; &quot;J.R.R. Tolkien&quot; &quot;416&quot; &quot;1955-10-20&quot; &quot;49.99&quot;<br>sqlite&gt;</pre><h3>Added in each row</h3><p>In this type, each row has the column name as some form of the row.</p><ol><li>Insert</li></ol><pre>.mode insert<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode insert<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>INSERT INTO &quot;table&quot;(id,title,author,pages,release_date,price) VALUES(1,&#39;The Hobbit&#39;,&#39;J.R.R. Tolkien&#39;,310,&#39;1937-09-21&#39;,39.99000000000000198);<br>INSERT INTO &quot;table&quot;(id,title,author,pages,release_date,price) VALUES(2,&#39;The Fellowship of the Ring&#39;,&#39;J.R.R. Tolkien&#39;,423,&#39;1954-07-29&#39;,49.99000000000000198);<br>INSERT INTO &quot;table&quot;(id,title,author,pages,release_date,price) VALUES(3,&#39;The Two Towers&#39;,&#39;J.R.R. Tolkien&#39;,352,&#39;1954-11-11&#39;,49.99000000000000198);<br>INSERT INTO &quot;table&quot;(id,title,author,pages,release_date,price) VALUES(4,&#39;The Return of the King&#39;,&#39;J.R.R. Tolkien&#39;,416,&#39;1955-10-20&#39;,49.99000000000000198);<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>INSERT INTO &quot;table&quot; VALUES(1,&#39;The Hobbit&#39;,&#39;J.R.R. Tolkien&#39;,310,&#39;1937-09-21&#39;,39.99000000000000198);<br>INSERT INTO &quot;table&quot; VALUES(2,&#39;The Fellowship of the Ring&#39;,&#39;J.R.R. Tolkien&#39;,423,&#39;1954-07-29&#39;,49.99000000000000198);<br>INSERT INTO &quot;table&quot; VALUES(3,&#39;The Two Towers&#39;,&#39;J.R.R. Tolkien&#39;,352,&#39;1954-11-11&#39;,49.99000000000000198);<br>INSERT INTO &quot;table&quot; VALUES(4,&#39;The Return of the King&#39;,&#39;J.R.R. Tolkien&#39;,416,&#39;1955-10-20&#39;,49.99000000000000198);<br>sqlite&gt;</pre><h3>No effect</h3><p>These modes show the headers or column names irrespective of the .headers flag, as for these modes the headers form quite a bit of structure to the output they are showing.</p><ol><li>Box</li></ol><pre>.mode box<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode box<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>┌────┬────────────────────────────┬────────────────┬───────┬──────────────┬───────┐<br>│ id │           title            │     author     │ pages │ release_date │ price │<br>├────┼────────────────────────────┼────────────────┼───────┼──────────────┼───────┤<br>│ 1  │ The Hobbit                 │ J.R.R. Tolkien │ 310   │ 1937-09-21   │ 39.99 │<br>│ 2  │ The Fellowship of the Ring │ J.R.R. Tolkien │ 423   │ 1954-07-29   │ 49.99 │<br>│ 3  │ The Two Towers             │ J.R.R. Tolkien │ 352   │ 1954-11-11   │ 49.99 │<br>│ 4  │ The Return of the King     │ J.R.R. Tolkien │ 416   │ 1955-10-20   │ 49.99 │<br>└────┴────────────────────────────┴────────────────┴───────┴──────────────┴───────┘<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>┌────┬────────────────────────────┬────────────────┬───────┬──────────────┬───────┐<br>│ id │           title            │     author     │ pages │ release_date │ price │<br>├────┼────────────────────────────┼────────────────┼───────┼──────────────┼───────┤<br>│ 1  │ The Hobbit                 │ J.R.R. Tolkien │ 310   │ 1937-09-21   │ 39.99 │<br>│ 2  │ The Fellowship of the Ring │ J.R.R. Tolkien │ 423   │ 1954-07-29   │ 49.99 │<br>│ 3  │ The Two Towers             │ J.R.R. Tolkien │ 352   │ 1954-11-11   │ 49.99 │<br>│ 4  │ The Return of the King     │ J.R.R. Tolkien │ 416   │ 1955-10-20   │ 49.99 │<br>└────┴────────────────────────────┴────────────────┴───────┴──────────────┴───────┘<br>sqlite&gt;</pre><ol><li>JSON</li></ol><pre>.mode json<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode json<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>[{&quot;id&quot;:1,&quot;title&quot;:&quot;The Hobbit&quot;,&quot;author&quot;:&quot;J.R.R. Tolkien&quot;,&quot;pages&quot;:310,&quot;release_date&quot;:&quot;1937-09-21&quot;,&quot;price&quot;:39.99000000000000198},<br>{&quot;id&quot;:2,&quot;title&quot;:&quot;The Fellowship of the Ring&quot;,&quot;author&quot;:&quot;J.R.R. Tolkien&quot;,&quot;pages&quot;:423,&quot;release_date&quot;:&quot;1954-07-29&quot;,&quot;price&quot;:49.99000000000000198},<br>{&quot;id&quot;:3,&quot;title&quot;:&quot;The Two Towers&quot;,&quot;author&quot;:&quot;J.R.R. Tolkien&quot;,&quot;pages&quot;:352,&quot;release_date&quot;:&quot;1954-11-11&quot;,&quot;price&quot;:49.99000000000000198},<br>{&quot;id&quot;:4,&quot;title&quot;:&quot;The Return of the King&quot;,&quot;author&quot;:&quot;J.R.R. Tolkien&quot;,&quot;pages&quot;:416,&quot;release_date&quot;:&quot;1955-10-20&quot;,&quot;price&quot;:49.99000000000000198}]<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>[{&quot;id&quot;:1,&quot;title&quot;:&quot;The Hobbit&quot;,&quot;author&quot;:&quot;J.R.R. Tolkien&quot;,&quot;pages&quot;:310,&quot;release_date&quot;:&quot;1937-09-21&quot;,&quot;price&quot;:39.99000000000000198},<br>{&quot;id&quot;:2,&quot;title&quot;:&quot;The Fellowship of the Ring&quot;,&quot;author&quot;:&quot;J.R.R. Tolkien&quot;,&quot;pages&quot;:423,&quot;release_date&quot;:&quot;1954-07-29&quot;,&quot;price&quot;:49.99000000000000198},<br>{&quot;id&quot;:3,&quot;title&quot;:&quot;The Two Towers&quot;,&quot;author&quot;:&quot;J.R.R. Tolkien&quot;,&quot;pages&quot;:352,&quot;release_date&quot;:&quot;1954-11-11&quot;,&quot;price&quot;:49.99000000000000198},<br>{&quot;id&quot;:4,&quot;title&quot;:&quot;The Return of the King&quot;,&quot;author&quot;:&quot;J.R.R. Tolkien&quot;,&quot;pages&quot;:416,&quot;release_date&quot;:&quot;1955-10-20&quot;,&quot;price&quot;:49.99000000000000198}]<br>sqlite&gt;</pre><ol><li>Line</li></ol><pre>.mode line<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode line<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>          id = 1<br>       title = The Hobbit<br>      author = J.R.R. Tolkien<br>       pages = 310<br>release_date = 1937-09-21<br>       price = 39.99</pre><pre>          id = 2<br>       title = The Fellowship of the Ring<br>      author = J.R.R. Tolkien<br>       pages = 423<br>release_date = 1954-07-29<br>       price = 49.99</pre><pre>          id = 3<br>       title = The Two Towers<br>      author = J.R.R. Tolkien<br>       pages = 352<br>release_date = 1954-11-11<br>       price = 49.99</pre><pre>          id = 4<br>       title = The Return of the King<br>      author = J.R.R. Tolkien<br>       pages = 416<br>release_date = 1955-10-20<br>       price = 49.99<br></pre><pre>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>          id = 1<br>       title = The Hobbit<br>      author = J.R.R. Tolkien<br>       pages = 310<br>release_date = 1937-09-21<br>       price = 39.99</pre><pre>          id = 2<br>       title = The Fellowship of the Ring<br>      author = J.R.R. Tolkien<br>       pages = 423<br>release_date = 1954-07-29<br>       price = 49.99</pre><pre>          id = 3<br>       title = The Two Towers<br>      author = J.R.R. Tolkien<br>       pages = 352<br>release_date = 1954-11-11<br>       price = 49.99</pre><pre>          id = 4<br>       title = The Return of the King<br>      author = J.R.R. Tolkien<br>       pages = 416<br>release_date = 1955-10-20<br>       price = 49.99<br>sqlite&gt;<br></pre><ol><li>Markdown</li></ol><pre>.mode markdown<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode markdown<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>| id |           title            |     author     | pages | release_date | price |<br>|----|----------------------------|----------------|-------|--------------|-------|<br>| 1  | The Hobbit                 | J.R.R. Tolkien | 310   | 1937-09-21   | 39.99 |<br>| 2  | The Fellowship of the Ring | J.R.R. Tolkien | 423   | 1954-07-29   | 49.99 |<br>| 3  | The Two Towers             | J.R.R. Tolkien | 352   | 1954-11-11   | 49.99 |<br>| 4  | The Return of the King     | J.R.R. Tolkien | 416   | 1955-10-20   | 49.99 |<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>| id |           title            |     author     | pages | release_date | price |<br>|----|----------------------------|----------------|-------|--------------|-------|<br>| 1  | The Hobbit                 | J.R.R. Tolkien | 310   | 1937-09-21   | 39.99 |<br>| 2  | The Fellowship of the Ring | J.R.R. Tolkien | 423   | 1954-07-29   | 49.99 |<br>| 3  | The Two Towers             | J.R.R. Tolkien | 352   | 1954-11-11   | 49.99 |<br>| 4  | The Return of the King     | J.R.R. Tolkien | 416   | 1955-10-20   | 49.99 |<br>sqlite&gt;</pre><ol><li>Table</li></ol><pre>.mode table<br>.headers on<br>select * from books;<br>.headers off<br>select * from books;</pre><p>Output:</p><pre>sqlite&gt; .mode table<br>sqlite&gt; .headers on<br>sqlite&gt; select * from books;<br>+----+----------------------------+----------------+-------+--------------+-------+<br>| id |           title            |     author     | pages | release_date | price |<br>+----+----------------------------+----------------+-------+--------------+-------+<br>| 1  | The Hobbit                 | J.R.R. Tolkien | 310   | 1937-09-21   | 39.99 |<br>| 2  | The Fellowship of the Ring | J.R.R. Tolkien | 423   | 1954-07-29   | 49.99 |<br>| 3  | The Two Towers             | J.R.R. Tolkien | 352   | 1954-11-11   | 49.99 |<br>| 4  | The Return of the King     | J.R.R. Tolkien | 416   | 1955-10-20   | 49.99 |<br>+----+----------------------------+----------------+-------+--------------+-------+<br>sqlite&gt; .headers off<br>sqlite&gt; select * from books;<br>+----+----------------------------+----------------+-------+--------------+-------+<br>| id |           title            |     author     | pages | release_date | price |<br>+----+----------------------------+----------------+-------+--------------+-------+<br>| 1  | The Hobbit                 | J.R.R. Tolkien | 310   | 1937-09-21   | 39.99 |<br>| 2  | The Fellowship of the Ring | J.R.R. Tolkien | 423   | 1954-07-29   | 49.99 |<br>| 3  | The Two Towers             | J.R.R. Tolkien | 352   | 1954-11-11   | 49.99 |<br>| 4  | The Return of the King     | J.R.R. Tolkien | 416   | 1955-10-20   | 49.99 |<br>+----+----------------------------+----------------+-------+--------------+-------+<br>sqlite&gt;</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8d4bfa1bb240" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQLite dot commands: Output mode separator command]]></title>
            <link>https://meet-gor.medium.com/sqlite-dot-commands-output-mode-separator-command-a61e4ccfcd5b?source=rss-14f575a241a9------2</link>
            <guid isPermaLink="false">https://medium.com/p/a61e4ccfcd5b</guid>
            <category><![CDATA[sqlite]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[relational-databases]]></category>
            <category><![CDATA[database-administration]]></category>
            <dc:creator><![CDATA[Meet Gor]]></dc:creator>
            <pubDate>Fri, 19 Sep 2025 16:21:09 GMT</pubDate>
            <atom:updated>2025-09-19T16:21:09.284Z</atom:updated>
            <content:encoded><![CDATA[<h3>Using the separator for the output</h3><p>If you wanted to use a specific separator for columns and rows while displaying the result set / table, you can use the .separator dot command which can take 2 arguments, first as the separator for the column and the second for the row.</p><p>So, if we set use .separator &quot;|&quot; &quot;---&quot; then it will split the columns with | and each row with ---.</p><pre>1|The Hobbit|J.R.R. Tolkien|310|1937-09-21|39.99---2|The Fellowship of the Ring|J.R.R. Tolkien|423|1954-07-29|49.99---3|The Two Towers|J.R.R. Tolkien|352|1954-11-11|49.99---4|The Return of the King|J.R.R. Tolkien|416|1955-10-20|49.99---</pre><p>The output looks wired but I was giving a example.</p><p>The row separator is by default a \n character or \r\n on windows, which is for the list or any other mode. However if you want to add those, you need to specify it in the string like below:</p><pre>.separator &quot;|&quot; &quot;\n---&quot;</pre><pre>&gt;sqlite&gt;.separator &quot;|&quot; &quot;\n---&quot;<br>sqlite&gt; select * from books;<br>1|The Hobbit|J.R.R. Tolkien|310|1937-09-21|39.99<br>---2|The Fellowship of the Ring|J.R.R. Tolkien|423|1954-07-29|49.99<br>---3|The Two Towers|J.R.R. Tolkien|352|1954-11-11|49.99<br>---4|The Return of the King|J.R.R. Tolkien|416|1955-10-20|49.99<br>---sqlite&gt;</pre><p>The \n--- will add it at the end of each row, so the first row won&#39;t have it if you are seeing the above output and confused, then that&#39;s the expected result.</p><p>You can also use \r or \t for the row separator, and for the column separator you can use \n or \t or \r.</p><p>The modes that will respect the separator setting:</p><ul><li>list</li><li>quote</li></ul><p>Sadly only two, since for changing the row separator you need to pass the column separator first, so that defeats the purpose of the change in the row separator, you’ll have to override the column separator first and then the row.</p><p>So, for the mode tabs, if you want to keep the columns separator as is, but change the row separator then you can do something like this:</p><pre>.mode tabs<br>.separator &quot;\t&quot; &quot;\n\n&quot;</pre><p>This will keep the column separator as is and change the row separator to \n\n</p><pre>sqlite&gt; .mode tabs<br>sqlite&gt; .separator &quot;\t&quot; &quot;\n\n&quot;<br>sqlite&gt; select * from books;<br>id      title   author  pages   release_date    price</pre><pre>1       The Hobbit      J.R.R. Tolkien  310     1937-09-21      39.99</pre><pre>2       The Fellowship of the Ring      J.R.R. Tolkien  423     1954-07-29      49.99</pre><pre>3       The Two Towers  J.R.R. Tolkien  352     1954-11-11      49.99</pre><pre>4       The Return of the King  J.R.R. Tolkien  416     1955-10-20      49.99</pre><pre>sqlite&gt;</pre><p>Similarly for csv you would keep the separator as , and then pass the row separator</p><p>Nice tricks to know.</p><p>References:</p><ul><li><a href="https://www.sqlite.org/cli.html#changing_output_formats">SQLite dot commands: Changing output formats</a></li></ul><p>Read more with interactive SQL codeblocks and playground <a href="https://www.meetgor.com/sqlog/sqlite-mode-dot-command-separators/">here</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a61e4ccfcd5b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQLite dot commands: Output mode]]></title>
            <link>https://meet-gor.medium.com/sqlite-dot-commands-output-mode-d404277daf70?source=rss-14f575a241a9------2</link>
            <guid isPermaLink="false">https://medium.com/p/d404277daf70</guid>
            <category><![CDATA[sqlite3]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[relational-databases]]></category>
            <category><![CDATA[sqlite]]></category>
            <category><![CDATA[database-administration]]></category>
            <dc:creator><![CDATA[Meet Gor]]></dc:creator>
            <pubDate>Thu, 18 Sep 2025 18:20:44 GMT</pubDate>
            <atom:updated>2025-09-18T18:20:44.434Z</atom:updated>
            <content:encoded><![CDATA[<h3>Output mode dot command</h3><p>The SQLite shell is a great way to test out and run bunch of queries, but the output might be squished or cluttered. You might need to view the output in a specific way, and the creators of SQLite were already hearing your screams.</p><p>They created not 5, not 10, but 14 different output modes, and to extend it even further, you can even use any delimiter/separator as well.</p><p>The .mode will give you the currently configured/set output mode with some options which are default for certain type of modes.</p><p>The default mode is list, with the option of escape with the ascii character set.</p><pre>current output mode: list --escape ascii</pre><p>The list mode defined in the <a href="https://www.sqlite.org/cli.html#changing_output_formats">docs</a> as:</p><blockquote><em>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.</em></blockquote><p>So, in short, the list output mode prints each row on a line with a | as the separator.</p><p>You can get all the information you need with the .help mode command</p><pre>.mode ?MODE? ?OPTIONS?   Set output mode<br>   MODE is one of:<br>     ascii       Columns/rows delimited by 0x1F and 0x1E<br>     box         Tables using unicode box-drawing characters<br>     csv         Comma-separated values<br>     column      Output in columns.  (See .width)<br>     html        HTML \&lt;table\&gt; code<br>     insert      SQL insert statements for TABLE<br>     json        Results in a JSON array<br>     line        One value per line<br>     list        Values delimited by &quot;|&quot;<br>     markdown    Markdown table format<br>     qbox        Shorthand for &quot;box --wrap 60 --quote&quot;<br>     quote       Escape answers as for SQL<br>     table       ASCII-art table<br>     tabs        Tab-separated values<br>     tcl         TCL list elements<br>   OPTIONS: (for columnar modes or insert mode):<br>     --escape T     ctrl-char escape; T is one of: symbol, ascii, off<br>     --wrap N       Wrap output lines to no longer than N characters<br>     --wordwrap B   Wrap or not at word boundaries per B (on/off)<br>     --ww           Shorthand for &quot;--wordwrap 1&quot;<br>     --quote        Quote output text as SQL literals<br>     --noquote      Do not quote output text<br>     TABLE          The name of SQL table used for &quot;insert&quot; mode</pre><p>Let’s set up with some sample data</p><pre>CREATE TABLE IF NOT EXISTS books(<br>    id INTEGER PRIMARY KEY AUTOINCREMENT,<br>    title TEXT NOT NULL,<br>    author TEXT NOT NULL,<br>    pages INTEGER NOT NULL,<br>    release_date TEXT NOT NULL,<br>    price REAL NOT NULL<br>);</pre><pre>INSERT INTO books(title, author, pages, release_date, price)<br>VALUES<br>(&#39;The Hobbit&#39;, &#39;J.R.R. Tolkien&#39;, 310, &#39;1937-09-21&#39;, 39.99),<br>(&#39;The Fellowship of the Ring&#39;, &#39;J.R.R. Tolkien&#39;, 423, &#39;1954-07-29&#39;, 49.99),<br>(&#39;The Two Towers&#39;, &#39;J.R.R. Tolkien&#39;, 352, &#39;1954-11-11&#39;, 49.99), <br>(&#39;The Return of the King&#39;, &#39;J.R.R. Tolkien&#39;, 416, &#39;1955-10-20&#39;, 49.99);</pre><pre>SELECT * FROM books;</pre><pre>1|The Hobbit|J.R.R. Tolkien|310|1937-09-21|39.99<br>2|The Fellowship of the Ring|J.R.R. Tolkien|423|1954-07-29|49.99<br>3|The Two Towers|J.R.R. Tolkien|352|1954-11-11|49.99<br>4|The Return of the King|J.R.R. Tolkien|416|1955-10-20|49.99</pre><p>Not the best way to look at the data, but handy in some cases. We can change it, we have 14 different modes to choose from. Its like choosing a taste flovour for an ice-cream, you want something different each time, or you stick to the simple vanilla one. Which one are you? I am a vanilla guy (I like the table mode, but use csv heavily too).</p><p>Let’s change it to a table format with .mode table This will set the mode as table</p><blockquote><em>table ASCII-art table</em></blockquote><p>This will show the result sets in a ascii-like table structure with the +++ and --- to separate the rows and columns.</p><pre>SELECT * FROM books;</pre><pre>+----+----------------------------+----------------+-------+--------------+-------+<br>| id |           title            |     author     | pages | release_date | price |<br>+----+----------------------------+----------------+-------+--------------+-------+<br>| 1  | The Hobbit                 | J.R.R. Tolkien | 310   | 1937-09-21   | 39.99 |<br>| 2  | The Fellowship of the Ring | J.R.R. Tolkien | 423   | 1954-07-29   | 49.99 |<br>| 3  | The Two Towers             | J.R.R. Tolkien | 352   | 1954-11-11   | 49.99 |<br>| 4  | The Return of the King     | J.R.R. Tolkien | 416   | 1955-10-20   | 49.99 |<br>+----+----------------------------+----------------+-------+--------------+-------+</pre><p>That is soo nice looking! Crystal clear.</p><p>You can even see the current set mode precisely with the .mode command</p><p>Since I changed the mode to table, let’s see what the mode is now set as?</p><pre>sqlite&gt; .mode<br>current output mode: table --wrap 60 --wordwrap off --noquote --escape ascii</pre><p>It has a bunch of options like wrap, wordwrap, noquote, and escape etc. You can take a look at the help and tweak them as per your liking, I usually don&#39;t change the options, but it might be very rare to switch from the defaults.</p><h3>Output modes</h3><h3>ASCII</h3><p>This mode separates the columns with by 0x1F and rows with 0x1E.</p><pre>.mode ascii<br>SELECT * FROM books;</pre><pre>idtitleauthorpagesrelease_dateprice1The HobbitJ.R.R. Tolkien3101937-09-2139.992The Fellowship of the RingJ.R.R. Tolkien4231954-07-2949.993The Two TowersJ.R.R. Tolkien3521954-11-1149.994The Return of the KingJ.R.R. Tolkien4161955-10-2049.99</pre><h3>Box</h3><p>The box mode renders the result set using unicode box-drawing characters.</p><pre>.mode box<br>SELECT * FROM books;</pre><pre>┌────┬────────────────────────────┬────────────────┬───────┬──────────────┬───────┐<br>│ id │           title            │     author     │ pages │ release_date │ price │<br>├────┼────────────────────────────┼────────────────┼───────┼──────────────┼───────┤<br>│ 1  │ The Hobbit                 │ J.R.R. Tolkien │ 310   │ 1937-09-21   │ 39.99 │<br>│ 2  │ The Fellowship of the Ring │ J.R.R. Tolkien │ 423   │ 1954-07-29   │ 49.99 │<br>│ 3  │ The Two Towers             │ J.R.R. Tolkien │ 352   │ 1954-11-11   │ 49.99 │<br>│ 4  │ The Return of the King     │ J.R.R. Tolkien │ 416   │ 1955-10-20   │ 49.99 │<br>└────┴────────────────────────────┴────────────────┴───────┴──────────────┴───────┘</pre><h3>CSV</h3><p>The csv mode simply outputs the result in the comma-separated values for columns and newline character for rows.</p><pre>.mode csv<br>SELECT * FROM books;</pre><pre>1,&quot;The Hobbit&quot;,&quot;J.R.R. Tolkien&quot;,310,1937-09-21,39.99<br>2,&quot;The Fellowship of the Ring&quot;,&quot;J.R.R. Tolkien&quot;,423,1954-07-29,49.99<br>3,&quot;The Two Towers&quot;,&quot;J.R.R. Tolkien&quot;,352,1954-11-11,49.99<br>4,&quot;The Return of the King&quot;,&quot;J.R.R. Tolkien&quot;,416,1955-10-20,49.99</pre><h3>Column</h3><p>The column mode simply outputs the result in columns with certain width. We can set the option .width to change the width.</p><pre>.mode column<br>SELECT * FROM books;</pre><pre>id  title                       author          pages  release_date  price<br>--  --------------------------  --------------  -----  ------------  -----<br>1   The Hobbit                  J.R.R. Tolkien  310    1937-09-21    39.99<br>2   The Fellowship of the Ring  J.R.R. Tolkien  423    1954-07-29    49.99<br>3   The Two Towers              J.R.R. Tolkien  352    1954-11-11    49.99<br>4   The Return of the King      J.R.R. Tolkien  416    1955-10-20    49.99</pre><h3>HTML</h3><p>The html output mode simply renders the result set / tables into an table element in html.</p><pre>.mode html<br>SELECT * FROM books;</pre><pre>&lt;TR&gt;&lt;TH&gt;id&lt;/TH&gt;<br>&lt;TH&gt;title&lt;/TH&gt;<br>&lt;TH&gt;author&lt;/TH&gt;<br>&lt;TH&gt;pages&lt;/TH&gt;<br>&lt;TH&gt;release_date&lt;/TH&gt;<br>&lt;TH&gt;price&lt;/TH&gt;<br>&lt;/TR&gt;<br>&lt;TR&gt;&lt;TD&gt;1&lt;/TD&gt;<br>&lt;TD&gt;The Hobbit&lt;/TD&gt;<br>&lt;TD&gt;J.R.R. Tolkien&lt;/TD&gt;<br>&lt;TD&gt;310&lt;/TD&gt;<br>&lt;TD&gt;1937-09-21&lt;/TD&gt;<br>&lt;TD&gt;39.99&lt;/TD&gt;<br>&lt;/TR&gt;<br>&lt;TR&gt;&lt;TD&gt;2&lt;/TD&gt;<br>&lt;TD&gt;The Fellowship of the Ring&lt;/TD&gt;<br>&lt;TD&gt;J.R.R. Tolkien&lt;/TD&gt;<br>&lt;TD&gt;423&lt;/TD&gt;<br>&lt;TD&gt;1954-07-29&lt;/TD&gt;<br>&lt;TD&gt;49.99&lt;/TD&gt;<br>&lt;/TR&gt;<br>&lt;TR&gt;&lt;TD&gt;3&lt;/TD&gt;<br>&lt;TD&gt;The Two Towers&lt;/TD&gt;<br>&lt;TD&gt;J.R.R. Tolkien&lt;/TD&gt;<br>&lt;TD&gt;352&lt;/TD&gt;<br>&lt;TD&gt;1954-11-11&lt;/TD&gt;<br>&lt;TD&gt;49.99&lt;/TD&gt;<br>&lt;/TR&gt;<br>&lt;TR&gt;&lt;TD&gt;4&lt;/TD&gt;<br>&lt;TD&gt;The Return of the King&lt;/TD&gt;<br>&lt;TD&gt;J.R.R. Tolkien&lt;/TD&gt;<br>&lt;TD&gt;416&lt;/TD&gt;<br>&lt;TD&gt;1955-10-20&lt;/TD&gt;<br>&lt;TD&gt;49.99&lt;/TD&gt;<br>&lt;/TR&gt;</pre><h3>Insert</h3><p>The insert mode simply outputs the result in SQL insert statements. This is really handy for bulk inserting or migrating data across databases or tables.</p><pre>.mode insert<br>SELECT * FROM books;</pre><pre>INSERT INTO &quot;table&quot;(id,title,author,pages,release_date,price) VALUES(1,&#39;The Hobbit&#39;,&#39;J.R.R. Tolkien&#39;,310,&#39;1937-09-21&#39;,39.99000000000000198);<br>INSERT INTO &quot;table&quot;(id,title,author,pages,release_date,price) VALUES(2,&#39;The Fellowship of the Ring&#39;,&#39;J.R.R. Tolkien&#39;,423,&#39;1954-07-29&#39;,49.99000000000000198);<br>INSERT INTO &quot;table&quot;(id,title,author,pages,release_date,price) VALUES(3,&#39;The Two Towers&#39;,&#39;J.R.R. Tolkien&#39;,352,&#39;1954-11-11&#39;,49.99000000000000198);<br>INSERT INTO &quot;table&quot;(id,title,author,pages,release_date,price) VALUES(4,&#39;The Return of the King&#39;,&#39;J.R.R. Tolkien&#39;,416,&#39;1955-10-20&#39;,49.99000000000000198);</pre><h3>JSON</h3><p>The json mode simply outputs the result in json format.</p><pre>.mode json<br>SELECT * FROM books;</pre><pre>[<br>    {&quot;id&quot;:1,&quot;title&quot;:&quot;The Hobbit&quot;,&quot;author&quot;:&quot;J.R.R. Tolkien&quot;,&quot;pages&quot;:310,&quot;release_date&quot;:&quot;1937-09-21&quot;,&quot;price&quot;:39.99000000000000198},<br>    {&quot;id&quot;:2,&quot;title&quot;:&quot;The Fellowship of the Ring&quot;,&quot;author&quot;:&quot;J.R.R. Tolkien&quot;,&quot;pages&quot;:423,&quot;release_date&quot;:&quot;1954-07-29&quot;,&quot;price&quot;:49.99000000000000198},<br>    {&quot;id&quot;:3,&quot;title&quot;:&quot;The Two Towers&quot;,&quot;author&quot;:&quot;J.R.R. Tolkien&quot;,&quot;pages&quot;:352,&quot;release_date&quot;:&quot;1954-11-11&quot;,&quot;price&quot;:49.99000000000000198},<br>    {&quot;id&quot;:4,&quot;title&quot;:&quot;The Return of the King&quot;,&quot;author&quot;:&quot;J.R.R. Tolkien&quot;,&quot;pages&quot;:416,&quot;release_date&quot;:&quot;1955-10-20&quot;,&quot;price&quot;:49.99000000000000198}<br>]</pre><h3>List (The default)</h3><p>As we already know, the list mode is the default mode.</p><pre>.mode list<br>SELECT * FROM books;</pre><pre>id|title|author|pages|release_date|price<br>1|The Hobbit|J.R.R. Tolkien|310|1937-09-21|39.99<br>2|The Fellowship of the Ring|J.R.R. Tolkien|423|1954-07-29|49.99<br>3|The Two Towers|J.R.R. Tolkien|352|1954-11-11|49.99<br>4|The Return of the King|J.R.R. Tolkien|416|1955-10-20|49.99</pre><h3>Markdown</h3><p>The markdown mode simply outputs the result in markdown format. We can use this output in a markdown file and it will nicely draw the tables out.</p><pre>.mode markdown<br>SELECT * FROM books;</pre><pre>| id |           title            |     author     | pages | release_date | price |<br>|----|----------------------------|----------------|-------|--------------|-------|<br>| 1  | The Hobbit                 | J.R.R. Tolkien | 310   | 1937-09-21   | 39.99 |<br>| 2  | The Fellowship of the Ring | J.R.R. Tolkien | 423   | 1954-07-29   | 49.99 |<br>| 3  | The Two Towers             | J.R.R. Tolkien | 352   | 1954-11-11   | 49.99 |<br>| 4  | The Return of the King     | J.R.R. Tolkien | 416   | 1955-10-20   | 49.99 |</pre><h3>Qbox</h3><p>The qbox mode simply outputs the result in box format with the option as --wrap 60 --quote.</p><pre>.mode qbox<br>SELECT * FROM books;</pre><pre>┌────┬──────────────────────────────┬──────────────────┬───────┬──────────────┬───────┐<br>│ id │            title             │      author      │ pages │ release_date │ price │<br>├────┼──────────────────────────────┼──────────────────┼───────┼──────────────┼───────┤<br>│ 1  │ &#39;The Hobbit&#39;                 │ &#39;J.R.R. Tolkien&#39; │ 310   │ &#39;1937-09-21&#39; │ 39.99 │<br>│ 2  │ &#39;The Fellowship of the Ring&#39; │ &#39;J.R.R. Tolkien&#39; │ 423   │ &#39;1954-07-29&#39; │ 49.99 │<br>│ 3  │ &#39;The Two Towers&#39;             │ &#39;J.R.R. Tolkien&#39; │ 352   │ &#39;1954-11-11&#39; │ 49.99 │<br>│ 4  │ &#39;The Return of the King&#39;     │ &#39;J.R.R. Tolkien&#39; │ 416   │ &#39;1955-10-20&#39; │ 49.99 │<br>└────┴──────────────────────────────┴──────────────────┴───────┴──────────────┴───────┘</pre><h3>Quote</h3><p>The quote mode simply outputs the result in SQL-string quote format.</p><pre>.mode quote<br>SELECT * FROM books;</pre><pre>&#39;id&#39;,&#39;title&#39;,&#39;author&#39;,&#39;pages&#39;,&#39;release_date&#39;,&#39;price&#39;<br>1,&#39;The Hobbit&#39;,&#39;J.R.R. Tolkien&#39;,310,&#39;1937-09-21&#39;,39.99000000000000198<br>2,&#39;The Fellowship of the Ring&#39;,&#39;J.R.R. Tolkien&#39;,423,&#39;1954-07-29&#39;,49.99000000000000198<br>3,&#39;The Two Towers&#39;,&#39;J.R.R. Tolkien&#39;,352,&#39;1954-11-11&#39;,49.99000000000000198<br>4,&#39;The Return of the King&#39;,&#39;J.R.R. Tolkien&#39;,416,&#39;1955-10-20&#39;,49.99000000000000198</pre><h3>Table</h3><p>The table mode simply outputs the result in table format. We saw this mode in the previous example.</p><pre>.mode table<br>SELECT * FROM books;</pre><pre>+----+----------------------------+----------------+-------+--------------+-------+<br>| id |           title            |     author     | pages | release_date | price |<br>+----+----------------------------+----------------+-------+--------------+-------+<br>| 1  | The Hobbit                 | J.R.R. Tolkien | 310   | 1937-09-21   | 39.99 |<br>| 2  | The Fellowship of the Ring | J.R.R. Tolkien | 423   | 1954-07-29   | 49.99 |<br>| 3  | The Two Towers             | J.R.R. Tolkien | 352   | 1954-11-11   | 49.99 |<br>| 4  | The Return of the King     | J.R.R. Tolkien | 416   | 1955-10-20   | 49.99 |<br>+----+----------------------------+----------------+-------+--------------+-------+</pre><h3>Tabs</h3><p>The tabs mode simply outputs the result in tab-separated values.</p><pre>.mode tabs<br>SELECT * FROM books;</pre><pre>id      title   author  pages   release_date    price<br>1       The Hobbit      J.R.R. Tolkien  310     1937-09-21      39.99<br>2       The Fellowship of the Ring      J.R.R. Tolkien  423     1954-07-29      49.99<br>3       The Two Towers  J.R.R. Tolkien  352     1954-11-11      49.99<br>4       The Return of the King  J.R.R. Tolkien  416     1955-10-20      49.99</pre><h3>Tcl</h3><p>The Tcl mode simply outputs the result in Tcl format. Tcl or tool command language is a configuration friendly format.</p><pre>.mode tcl<br>SELECT * FROM books;</pre><pre>&quot;id&quot; &quot;title&quot; &quot;author&quot; &quot;pages&quot; &quot;release_date&quot; &quot;price&quot;<br>&quot;1&quot; &quot;The Hobbit&quot; &quot;J.R.R. Tolkien&quot; &quot;310&quot; &quot;1937-09-21&quot; &quot;39.99&quot;<br>&quot;2&quot; &quot;The Fellowship of the Ring&quot; &quot;J.R.R. Tolkien&quot; &quot;423&quot; &quot;1954-07-29&quot; &quot;49.99&quot;<br>&quot;3&quot; &quot;The Two Towers&quot; &quot;J.R.R. Tolkien&quot; &quot;352&quot; &quot;1954-11-11&quot; &quot;49.99&quot;<br>&quot;4&quot; &quot;The Return of the King&quot; &quot;J.R.R. Tolkien&quot; &quot;416&quot; &quot;1955-10-20&quot; &quot;49.99&quot;</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d404277daf70" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQLite dot commands: change directory command]]></title>
            <link>https://meet-gor.medium.com/sqlite-dot-commands-change-directory-command-6654b13be3c6?source=rss-14f575a241a9------2</link>
            <guid isPermaLink="false">https://medium.com/p/6654b13be3c6</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sqlite]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[relational-databases]]></category>
            <dc:creator><![CDATA[Meet Gor]]></dc:creator>
            <pubDate>Tue, 16 Sep 2025 16:44:30 GMT</pubDate>
            <atom:updated>2025-09-16T16:44:30.450Z</atom:updated>
            <content:encoded><![CDATA[<h3>Change directory dot command</h3><p>If you are in a sqlite shell and forgot to change directory or want to navigate to a separate directory, you can do that with the .cd dot command.</p><pre>.cd /path/to/directory</pre><p>This is better than doing .shell cd /path/to/directory because it doesn&#39;t open a separate terminal process. So, the .cd is persistant throughout the session, whereas the .shell cd &lt;path&gt; will only within that command (subprocess).</p><p>The .cd command changes the working directory of the SQLite shell itself, so the change persists for the rest of the session. This means commands like .import, .read, or .output will automatically look for files in the new directory.</p><p>However, .shell cd &lt;path&gt; spawns a separate subprocess, and the directory change is discarded as soon as that command finishes. It does not affect SQLite’s own state of the current directory.</p><p>So if you plan to read or write multiple files from a different location during your SQLite session, prefer the built-in .cd command.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6654b13be3c6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQLite dot commands: run system shell commands]]></title>
            <link>https://meet-gor.medium.com/sqlite-dot-commands-run-system-shell-commands-01953e3317d3?source=rss-14f575a241a9------2</link>
            <guid isPermaLink="false">https://medium.com/p/01953e3317d3</guid>
            <category><![CDATA[sqlite]]></category>
            <category><![CDATA[relational-databases]]></category>
            <category><![CDATA[sqlite3]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[database-administration]]></category>
            <dc:creator><![CDATA[Meet Gor]]></dc:creator>
            <pubDate>Mon, 15 Sep 2025 17:56:43 GMT</pubDate>
            <atom:updated>2025-09-15T17:56:43.010Z</atom:updated>
            <content:encoded><![CDATA[<h3>Shell dot command</h3><p>If you are in middle of a sqlite shell session, and you don’t want to quit the shell to run arbitrary shell command, you can simply use the .shell &lt;command&gt; to execute any shell commands right from within the sqlite shell. How handy is this!</p><pre>.shell echo &quot;hello, world!&quot;</pre><p>That is a lame example, but it shows you the power of the .shell command.</p><p>Let’s say I want to run a golang project, I can do this:</p><pre>.shell go run main.go</pre><p>Its helpful if you want to do something but you don’t want to quit the shell to do that:</p><ul><li>look up few files/datapoints from the local filesystem,</li><li>run scripts to populate data</li><li>populate database and then reopen the db shell</li></ul><p>This is are the things that I have stumbled upon, so far. Need more experience to see if there are more.</p><p>Read the entire blog post <a href="https://www.meetgor.com/sqlog/sqlite-shell-dot-command/">here</a> with interactive SQL codeblocks and playground like environment</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=01953e3317d3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQLite dot commands: read command is recursive?]]></title>
            <link>https://meet-gor.medium.com/sqlite-dot-commands-read-command-is-recursive-e6db0f9dce0b?source=rss-14f575a241a9------2</link>
            <guid isPermaLink="false">https://medium.com/p/e6db0f9dce0b</guid>
            <category><![CDATA[sqlite3]]></category>
            <category><![CDATA[relational-databases]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[sqlite]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Meet Gor]]></dc:creator>
            <pubDate>Sun, 14 Sep 2025 10:58:37 GMT</pubDate>
            <atom:updated>2025-09-14T10:58:37.055Z</atom:updated>
            <content:encoded><![CDATA[<p>Yesterday, while exploring the .read command I discovered a little hack.</p><p>We know you can read arbitrary sqlite shell commands from the .read | operator, even the dot commands.</p><p>So, you can even use .read itself again. Which means we can call the same file to read inside the script.</p><p>Ops!</p><p>Let’s create a file called <a href="http://loop.sh/">loop.sh</a> this will emit certain valid sql/sqlite commands like so:</p><pre>#!/bin/bash<br>echo &quot;SELECT &#39;hello, world!&#39;;&quot;<br>echo &quot;.read |./loop.sh&quot;</pre><p>The first will print out “SELECT ‘hello world!’;” so when directed to the shell, it will be executed and print “hello, world!”</p><p>Then the 3rd line echo &quot;.read |./<a href="http://loop.sh/">loop.sh</a>&quot; is the magic, it will execute the script again right?</p><p>Because it’s a valid sqlite shell command and it will execute it. It will take the read command and print everything when executing the <a href="http://loop.sh/">loop.sh</a> shell script.</p><p>What’s the output?</p><pre>$ cat loop.sh<br>#!/bin/bash<br>echo &quot;SELECT &#39;hello, world!&#39;;&quot;<br>echo &quot;.read |./loop.sh&quot;</pre><pre>$ sqlite3<br>SQLite version 3.50.4 2025-07-30 19:33:53<br>Enter &quot;.help&quot; for usage hints.<br>Connected to a transient in-memory database.<br>Use &quot;.open FILENAME&quot; to reopen on a persistent database.<br>sqlite&gt; .read |./loop.sh<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>hello, world!<br>Input nesting limit (25) reached at line 2. Check recursion.<br>sqlite&gt;</pre><p>Well, SQLite developers are smarter to handle the edge case I think. :)</p><p>This is a little fun, but it’s kind of a quirk, an not-so easter egg but interesting thing to know.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e6db0f9dce0b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQLite dot commands: read file or standard output]]></title>
            <link>https://meet-gor.medium.com/sqlite-dot-commands-read-file-or-standard-output-798b004504ea?source=rss-14f575a241a9------2</link>
            <guid isPermaLink="false">https://medium.com/p/798b004504ea</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[relational-databases]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sqlite3]]></category>
            <category><![CDATA[sqlite]]></category>
            <dc:creator><![CDATA[Meet Gor]]></dc:creator>
            <pubDate>Sat, 13 Sep 2025 14:37:58 GMT</pubDate>
            <atom:updated>2025-09-13T14:37:58.569Z</atom:updated>
            <content:encoded><![CDATA[<h3>Read dot Command</h3><p>The .read dot command is a quick handy command to import and run your SQL queries in the current session.</p><p>You can just pass a SQL file (usually a query ending with ; it will execute each query one by one)</p><pre>.read filename</pre><p>Let’s say this is a sql file containing the schema of a database, just one table users.</p><pre>CREATE TABLE users (<br>  id   INTEGER PRIMARY KEY,<br>  name TEXT<br>);</pre><p>Writing this sql query in the schema.sql file</p><p>If we use the .read command with the name of the file it will execute it line by line, (line meaning terminated by ; here or even any dot commands)</p><pre>.read schema.sql</pre><p>This will just execute the query, if there are SELECT statements possibly then will output the result set too.</p><p>Let’s add some users to the table with the insert_users.sql</p><pre>INSERT INTO users(name) VALUES(&#39;abc&#39;), (&#39;def&#39;), (&#39;ghi&#39;);<br>SELECT * FROM users;</pre><p>Saving this and running .read insert_users.sql should now have displayed all the 3 inserted users.</p><h3>Reading with dot commands</h3><p>Let’s create one more file with specific dot commands to render a nice table format of a result set from a select statement</p><pre>.mode table<br>SELECT * FROM users;</pre><p>I save the above contents into a file called users.sql</p><p>Now if I run the .read command with the users.sql you should see all your users but with the mode as table format. It even takes the dot commands and all of the nice options out there. Be cautious though!</p><pre>.read users.sql</pre><pre>sqlite&gt; .read users.sql<br>+----+---------+<br>| id |  name   |<br>+----+---------+<br>| 1  | User_1  |<br>| 2  | User_2  |<br>| 3  | User_3  |<br>| 4  | User_4  |<br>| 5  | User_5  |<br>| 6  | User_6  |<br>| 7  | User_7  |<br>| 8  | User_8  |<br>| 9  | User_9  |<br>| 10 | User_10 |<br>+----+---------+</pre><h3>Read with standard output</h3><p>You can also run a script that will generate some SQL commands and execute them. By using the | operator after .read and providing the name of the script. this will run the script and any output of that script (valid sql) will be executed on the shell.</p><p>For instance I have a script that would insert 10 users with a bash script like so called inser_<a href="http://users.sh/">users.sh</a></p><pre>#!/bin/bash<br>for i in $(seq 1 10); do<br>  echo &quot;INSERT INTO users (name) VALUES (&#39;User_$i&#39;);&quot;<br>done</pre><p>If you would use this as is it won’t work as its a bash script, you can’t run those in a sqlite shell. However by using .read | filename (shell script), that will indeed do the trick.</p><p>The important thing to note here is that it would run all the things outputted from the script, so its kind of running the script and the std out of that script is taken as the input for the read command so like typical pipe operator in linux.</p><pre>.read | insert_users.sh</pre><p>This will run the script and the insert statements will be printed, so it will execute them. So, in essence we are basically piping the output of one command to the other in this case the read command.</p><pre>SELECT * FROM users;</pre><p>As you can see it has now inserted all the 10 users.</p><p>The | command can be followed by any script, not limited to bash, it could be python, or any script or program that can print to the standard output a correct and legal SQL statements.</p><p>This is a really great small yet highly important piece of functionality of sqlite shell.</p><p>Reference:</p><ul><li><a href="https://sqlite.org/cli.html#reading_sql_from_a_file">SQLite CLI: Reading SQL from a File</a></li></ul><p>Read the full post <a href="https://www.meetgor.com/sqlog/sqlite-dot-commands-read-file-or-standard-output/">here</a> along with the interactive SQL playground.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=798b004504ea" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQLite Scalar function: 3 valued iif and if scalar function]]></title>
            <link>https://meet-gor.medium.com/sqlite-scalar-function-3-valued-iif-and-if-scalar-function-c2b74bec0d07?source=rss-14f575a241a9------2</link>
            <guid isPermaLink="false">https://medium.com/p/c2b74bec0d07</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[relational-databases]]></category>
            <category><![CDATA[sqlite]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Meet Gor]]></dc:creator>
            <pubDate>Fri, 12 Sep 2025 16:58:50 GMT</pubDate>
            <atom:updated>2025-09-12T16:58:50.650Z</atom:updated>
            <content:encoded><![CDATA[<p>In the recent SQLite version specifically the 3.48.0, the support for pair of conditions was added to the iif function. And also an alias for iif as if was added for compatibility and ease of use for people coming from other databases.</p><h3>IIF Before 3.48</h3><p>Before this release it was kind of 3 way /ternary operation kind of thing like this</p><h3>Simple IF THEN ELSE</h3><p>We can use the IIF function by passing 3 paraeters, first is the condition to check, the second is the value to return if the condition is true and the third is the value to return if the condition is false.</p><pre>SELECT iif(1=0, &#39;one is zero&#39;, &#39;one is not zero&#39;);</pre><pre>one is not zero</pre><h3>Ternary like nested IIF</h3><p>Optionally the third condition can be nested with another IIF as it will be a value in the end. So, we can use IIF like this</p><pre>SELECT iif(7%2=0, &#39;even&#39;, iif(7%3=0, &#39;multiple of 3&#39;, &#39;prime&#39;)) AS is_prime;</pre><pre>+---------+<br>| is_prime|<br>+---------+<br>| prime   |<br>+---------+</pre><p>That is quite simple but it gets little messed up for more than one check, take this for instance</p><pre>SELECT iif(2025 % 400 = 0, &#39;leap&#39;,<br>       iif(2025 % 100 = 0, &#39;not leap&#39;,<br>       iif(2025 % 4 = 0, &#39;leap&#39;, &#39;not leap&#39;))) AS  is_leap_year;</pre><pre>+--------------+<br>| is_leap_year |<br>+--------------+<br>| not leap     |<br>+--------------+</pre><p>I have printed it in a nice way, but still might get harder to read and even write, notice the number of closing brackets at the end.</p><h3>IIF After 3.48</h3><p>With the latest 3.48 version, this changes quite a bit, you can have iif or if as like a case when then replacement like so:</p><pre>SELECT iif(2025 % 400 = 0, &#39;leap&#39;,<br>           2025 % 100 = 0, &#39;not leap&#39;,<br>           2025 % 4 = 0, &#39;leap&#39;, &#39;not leap&#39;) AS is_leap_year;</pre><pre>+--------------+<br>| is_leap_year |<br>+--------------+<br>| not leap     |<br>+--------------+</pre><p>How clean is that, with this you can specify, Condition, Value as a pair and it can have N subsequent pairs of this condition — value. All and this while maintaining backwards compatibility, that’s quite a remarkable thing to push I would say, not easy to handle these.</p><p>Now it has 3 variants</p><ul><li>3 valued expression (single expression, could be nested too, but three expressions, like → condition, true, false)</li><li>2 valued expression (assumes the false value is NULL)</li><li>N condition — valued pair expression (two pair of condition and value)</li></ul><p>These is neat and makes a lot of sense.</p><p>Let’s see the same example in all 3 variants</p><h3>3 Valued Expression</h3><p>This is the most simple one</p><pre>SELECT iif(2025%4=0, &#39;probably leap&#39;, &#39;not leap&#39;) AS is_leap_year;</pre><pre>+--------------+<br>| is_leap_year |<br>+--------------+<br>| not leap     |<br>+--------------+</pre><p>This returned the false value which happens when the condition evaluated to false.</p><h3>2 Valued Expression</h3><pre>SELECT iif(2025%4=0, &#39;probably leap&#39;) AS is_leap_year;</pre><pre>+--------------+<br>| is_leap_year |<br>+--------------+<br>|              |<br>+--------------+</pre><p>This returned NULL as 2025 is not divisible by 4. If there are 2N parameters to the function, and if all the conditions are false, it will return NULL. And the above example is the base case of it. Remember to have atleast 2 parameters, one the condition and one as the value if that condition evaluates to true. The last parameter is optional and if the condition is false, it would return NULL.</p><h3>N Pair Expression</h3><p>So moving on the final one which is the most readable version of it and allows more stuff</p><pre>SELECT iif(2025 % 400 = 0, &#39;leap&#39;,<br>           2025 % 100 = 0, &#39;not leap&#39;,<br>           2025 % 4 = 0, &#39;leap&#39;,<br>           &#39;not leap&#39;)<br>|| &#39; year&#39; AS is_leap_year;</pre><pre>+---------------+<br>| is_leap_year  |<br>+---------------+<br>| not leap year |<br>+---------------+</pre><p>Elegant Notice the last condition, if you don’t specify that, it will be NULL, so it would be 2N+1 parameters (where N is the number of conditions)</p><p>Other way to remember is</p><ul><li>Even parameters and all conditions are</li><li>True (the first value) or equivalent corresponding value for that condition</li><li>False (it will return NULL)</li><li>Odd parameters and all conditions are</li><li>True (the first value) or equivalent corresponding value for that condition</li><li>False (it will return the last parameter which is the false condition)</li></ul><p>You can use iif or if interchangeably. Both these do the same thing as the CASE WHEN THEN conditions, I think IIF now is really easy to write as now it supports arguments in pairs</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c2b74bec0d07" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQLite functions: unistr and unistr_quote]]></title>
            <link>https://meet-gor.medium.com/sqlite-functions-unistr-and-unistr-quote-f0051ea757bf?source=rss-14f575a241a9------2</link>
            <guid isPermaLink="false">https://medium.com/p/f0051ea757bf</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[relational-databases]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[sqlite]]></category>
            <dc:creator><![CDATA[Meet Gor]]></dc:creator>
            <pubDate>Thu, 11 Sep 2025 18:31:48 GMT</pubDate>
            <atom:updated>2025-09-11T18:31:48.317Z</atom:updated>
            <content:encoded><![CDATA[<p>SQLite <a href="https://www.sqlite.org/releaselog/3_50_0.html">3.50</a> introduced <a href="https://www.sqlite.org/lang_corefunc.html#unistr">unistr</a> and <a href="https://www.sqlite.org/lang_corefunc.html#unistr_quote">unistr_quote</a> functions</p><p>These are for taking in escape sequences and returning the actual unicode string like emoji or other non-english characters outside the ascii convention forming the utf-8 character set</p><p>The unistr is used to take in the escape sequences and return the actual unicode string The unistr_quote is used to escape those escape sequences in a sql query so your data can be stored with emojis.</p><blockquote><em>I wonder these functions were added because of LLMs emoji-heavy outputs?</em></blockquote><p>Here’s how you use unistr in a simple query</p><pre>SELECT unistr(&#39;\u2665, \U0001F600&#39;);</pre><p>This will print the :heart and :smiley emojis</p><p>I won’t print the emojis, it might look like a AI-generated post (grunts with pride)</p><p>This is handy to render the values but first you need to get the values stored in the db, for that you need to have it like a string i.e. escaped string</p><p>That is where the unistr_quote comes in</p><pre>SELECT unistr_quote(&#39;\U0001F600&#39;);</pre><p>This will simply print ‘\U0001F600’ notice the quotes here, it will be a escaped character sequence making it like a special string in middle of a string,</p><p>so if we have something like this</p><pre>SELECT printf(&#39;what the heck is this%s symbol?&#39;, unistr_quote(&#39;\U0001F449&#39;));</pre><blockquote><em>output: what the heck is this’\U0001F449&#39; symbol?</em></blockquote><p>This will make the distinction clear, we get a quoted string for that special set of characters or escape sequence which can be useful if the storage medium would make storing emojis obsolete.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f0051ea757bf" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQLite dot commands: html tables with output and once]]></title>
            <link>https://meet-gor.medium.com/sqlite-dot-commands-html-tables-with-output-and-once-98a14179c980?source=rss-14f575a241a9------2</link>
            <guid isPermaLink="false">https://medium.com/p/98a14179c980</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sqlite]]></category>
            <category><![CDATA[html]]></category>
            <category><![CDATA[relational-databases]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Meet Gor]]></dc:creator>
            <pubDate>Wed, 10 Sep 2025 17:34:14 GMT</pubDate>
            <atom:updated>2025-09-10T17:34:14.756Z</atom:updated>
            <content:encoded><![CDATA[<p>The .once -w or .www was released in <a href="https://www.sqlite.org/releaselog/3_47_0.html">SQLite 3.47</a> release last year.</p><p>I was trying this command</p><pre>.once -w results.html</pre><p>But was getting an error that it was not a valid argument, and the .www command doesn’t exist. I saw the git repo and found the source code and found out that it was released in the recent version. My laptop had SQLite 3.37 version installed.</p><p>I ran a docker image of sqlite to the latest version and check out the working of that .www command or .once -w subcommand option.</p><h3>What is .once -w or .output -w or .www</h3><p>All of these are equivalent</p><ul><li>.once -w</li><li>.www</li></ul><p>The output is just for all the subsequent commands to be saved in a file, whereas the .once -w and .www are only for the next one query.</p><p>This command basically allows the resultset to be saved in a html format/file. Note that you cannot specify a file, it will be stored in a temporary file, and if that is accessible and readable to the browser and enough permissions are set to the temp folder (whatever that is for Linux and windows or mac), it should render an html page (its simple)</p><p>However, if you are thinking of getting some specific file to write the results then you need to do some work</p><pre>.output result.html<br><br>.print &#39;&lt;!DOCTYPE html&gt;&lt;html&gt;&lt;body&gt;&lt;TABLE border=&quot;1&quot; cellspacing=&quot;0&quot; cellpadding=&quot;2&quot;&gt;&#39;<br><br>.mode html<br><br>.headers on<br><br>SELECT abs(random()%10)+1 as &quot;some numbers&quot;, &#39;number&#39; from generate_series(1,10);<br><br>.print &#39;&lt;/table&gt;&lt;/body&gt;&lt;/html&gt;&#39;</pre><p>This will store the html document in the result.html with proper tags and semantics not just the table element</p><p>Phew! That might not be a lot, but that is the thing, the .mode html will only get you the table element but it wont render properly, browsers won’t be able to read the raw html elements without the html doc-type tags.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=98a14179c980" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>