DEV Community: Meet Rajesh Gor

This is so cool. Actually supporting writers and developers to grow their audience without hassle of managing multiple platforms

Meet Rajesh Gor — Fri, 13 Mar 2026 04:34:31 +0000

Jon Gottfried for The DEV Team

Mar 9

Revamped RSS Feed Imports

#forem #devto #rss

Comments 15

2 min read

SQLite dot commands: Output mode separator command

Meet Rajesh Gor — Sat, 20 Sep 2025 11:57:37 +0000

Using the separator for the output

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.

So, if we set use .separator "|" "---" then it will split the columns with | and each row with ---.

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---

The output looks wired but I was giving a example.

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:

.separator "|" "\n---"

>sqlite>.separator "|" "\n---"
sqlite> select * from books;
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
---sqlite>

The \n--- will add it at the end of each row, so the first row won't have it if you are seeing the above output and confused, then that's the expected result.

You can also use \r or \t for the row separator, and for the column separator you can use \n or \t or \r.

The modes that will respect the separator setting:

list
quote

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.

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:

.mode tabs
.separator "\t" "\n\n"

This will keep the column separator as is and change the row separator to \n\n

sqlite> .mode tabs
sqlite> .separator "\t" "\n\n"
sqlite> select * from books;
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

sqlite>

Similarly for csv you would keep the separator as , and then pass the row separator

Nice tricks to know.

References:

SQLite dot commands: Changing output formats

Read the entire blog post here with interactive SQL codeblocks and playground like environment

SQLite dot commands: Output mode separator command | Meet Gor

Using the separator for the ouput If you wanted to use a specific separator for columns and rows while displaying the result set / table, you can use the dot co

meetgor.com

SQLite dot commands: Output mode

Meet Rajesh Gor — Fri, 19 Sep 2025 16:23:17 +0000

Output mode dot command

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.

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.

The .mode will give you the currently configured/set output mode with some options which are default for certain type of modes.

The default mode is list, with the option of escape with the ascii character set.

current output mode: list --escape ascii

The list mode defined in the docs as:

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.

So, in short, the list output mode prints each row on a line with a | as the separator.

You can get all the information you need with the .help mode command

.mode ?MODE? ?OPTIONS?   Set output mode
   MODE is one of:
     ascii       Columns/rows delimited by 0x1F and 0x1E
     box         Tables using unicode box-drawing characters
     csv         Comma-separated values
     column      Output in columns.  (See .width)
     html        HTML \<table\> code
     insert      SQL insert statements for TABLE
     json        Results in a JSON array
     line        One value per line
     list        Values delimited by "|"
     markdown    Markdown table format
     qbox        Shorthand for "box --wrap 60 --quote"
     quote       Escape answers as for SQL
     table       ASCII-art table
     tabs        Tab-separated values
     tcl         TCL list elements
   OPTIONS: (for columnar modes or insert mode):
     --escape T     ctrl-char escape; T is one of: symbol, ascii, off
     --wrap N       Wrap output lines to no longer than N characters
     --wordwrap B   Wrap or not at word boundaries per B (on/off)
     --ww           Shorthand for "--wordwrap 1"
     --quote        Quote output text as SQL literals
     --noquote      Do not quote output text
     TABLE          The name of SQL table used for "insert" mode

Let's set up with some sample data

CREATE TABLE IF NOT EXISTS books(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    pages INTEGER NOT NULL,
    release_date TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO books(title, author, pages, release_date, price)
VALUES
('The Hobbit', 'J.R.R. Tolkien', 310, '1937-09-21', 39.99),
('The Fellowship of the Ring', 'J.R.R. Tolkien', 423, '1954-07-29', 49.99),
('The Two Towers', 'J.R.R. Tolkien', 352, '1954-11-11', 49.99), 
('The Return of the King', 'J.R.R. Tolkien', 416, '1955-10-20', 49.99);

SELECT * FROM books;

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

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).

Let's change it to a table format with .mode table This will set the mode as table

table ASCII-art table

This will show the result sets in a ascii-like table structure with the +++ and --- to separate the rows and columns.

SELECT * FROM books;

+----+----------------------------+----------------+-------+--------------+-------+
| 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 |
+----+----------------------------+----------------+-------+--------------+-------+

That is soo nice looking! Crystal clear.

You can even see the current set mode precisely with the .mode command

Since I changed the mode to table, let's see what the mode is now set as?

sqlite> .mode
current output mode: table --wrap 60 --wordwrap off --noquote --escape ascii

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't change the options, but it might be very rare to switch from the defaults.

Output modes

ASCII

This mode separates the columns with by 0x1F and rows with 0x1E.

.mode ascii
SELECT * FROM books;

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.99

Box

The box mode renders the result set using unicode box-drawing characters.

.mode box
SELECT * FROM books;

┌────┬────────────────────────────┬────────────────┬───────┬──────────────┬───────┐
│ 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 │
└────┴────────────────────────────┴────────────────┴───────┴──────────────┴───────┘

CSV

The csv mode simply outputs the result in the comma-separated values for columns and newline character for rows.

.mode csv
SELECT * FROM books;

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

Column

The column mode simply outputs the result in columns with certain width. We can set the option .width to change the width.

.mode column
SELECT * FROM books;

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

HTML

The html output mode simply renders the result set / tables into an table element in html.

.mode html
SELECT * FROM books;

<TR><TH>id</TH>
<TH>title</TH>
<TH>author</TH>
<TH>pages</TH>
<TH>release_date</TH>
<TH>price</TH>
</TR>
<TR><TD>1</TD>
<TD>The Hobbit</TD>
<TD>J.R.R. Tolkien</TD>
<TD>310</TD>
<TD>1937-09-21</TD>
<TD>39.99</TD>
</TR>
<TR><TD>2</TD>
<TD>The Fellowship of the Ring</TD>
<TD>J.R.R. Tolkien</TD>
<TD>423</TD>
<TD>1954-07-29</TD>
<TD>49.99</TD>
</TR>
<TR><TD>3</TD>
<TD>The Two Towers</TD>
<TD>J.R.R. Tolkien</TD>
<TD>352</TD>
<TD>1954-11-11</TD>
<TD>49.99</TD>
</TR>
<TR><TD>4</TD>
<TD>The Return of the King</TD>
<TD>J.R.R. Tolkien</TD>
<TD>416</TD>
<TD>1955-10-20</TD>
<TD>49.99</TD>
</TR>

Insert

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.

.mode insert
SELECT * FROM books;

INSERT INTO "table"(id,title,author,pages,release_date,price) VALUES(1,'The Hobbit','J.R.R. Tolkien',310,'1937-09-21',39.99000000000000198);
INSERT INTO "table"(id,title,author,pages,release_date,price) VALUES(2,'The Fellowship of the Ring','J.R.R. Tolkien',423,'1954-07-29',49.99000000000000198);
INSERT INTO "table"(id,title,author,pages,release_date,price) VALUES(3,'The Two Towers','J.R.R. Tolkien',352,'1954-11-11',49.99000000000000198);
INSERT INTO "table"(id,title,author,pages,release_date,price) VALUES(4,'The Return of the King','J.R.R. Tolkien',416,'1955-10-20',49.99000000000000198);

JSON

The json mode simply outputs the result in json format.

.mode json
SELECT * FROM books;

[
    {"id":1,"title":"The Hobbit","author":"J.R.R. Tolkien","pages":310,"release_date":"1937-09-21","price":39.99000000000000198},
    {"id":2,"title":"The Fellowship of the Ring","author":"J.R.R. Tolkien","pages":423,"release_date":"1954-07-29","price":49.99000000000000198},
    {"id":3,"title":"The Two Towers","author":"J.R.R. Tolkien","pages":352,"release_date":"1954-11-11","price":49.99000000000000198},
    {"id":4,"title":"The Return of the King","author":"J.R.R. Tolkien","pages":416,"release_date":"1955-10-20","price":49.99000000000000198}
]

List (The default)

As we already know, the list mode is the default mode.

.mode list
SELECT * FROM books;

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

Markdown

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.

.mode markdown
SELECT * FROM books;

| 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 |

Qbox

The qbox mode simply outputs the result in box format with the option as --wrap 60 --quote.

.mode qbox
SELECT * FROM books;

┌────┬──────────────────────────────┬──────────────────┬───────┬──────────────┬───────┐
│ 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 │
└────┴──────────────────────────────┴──────────────────┴───────┴──────────────┴───────┘

Quote

The quote mode simply outputs the result in SQL-string quote format.

.mode quote
SELECT * FROM books;

'id','title','author','pages','release_date','price'
1,'The Hobbit','J.R.R. Tolkien',310,'1937-09-21',39.99000000000000198
2,'The Fellowship of the Ring','J.R.R. Tolkien',423,'1954-07-29',49.99000000000000198
3,'The Two Towers','J.R.R. Tolkien',352,'1954-11-11',49.99000000000000198
4,'The Return of the King','J.R.R. Tolkien',416,'1955-10-20',49.99000000000000198

Table

The table mode simply outputs the result in table format. We saw this mode in the previous example.

.mode table
SELECT * FROM books;

+----+----------------------------+----------------+-------+--------------+-------+
| 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 |
+----+----------------------------+----------------+-------+--------------+-------+

Tabs

The tabs mode simply outputs the result in tab-separated values.

.mode tabs
SELECT * FROM books;

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

Tcl

The Tcl mode simply outputs the result in Tcl format. Tcl or tool command language is a configuration friendly format.

.mode tcl
SELECT * FROM books;

"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"

Read the entire blog post here with interactive SQL codeblocks and playground like environment

SQLite dot commands: Output mode | Meet Gor

Output mode dot command 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 v

meetgor.com

SQLite dot commands: change directory command

Meet Rajesh Gor — Thu, 18 Sep 2025 18:15:36 +0000

Change directory dot command

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.

.cd /path/to/directory

This is better than doing .shell cd /path/to/directory because it doesn't open a separate terminal process. So, the .cd is persistant throughout the session, whereas the .shell cd <path> will only within that command (subprocess).

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.

However, .shell cd <path> 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.

So if you plan to read or write multiple files from a different location during your SQLite session, prefer the built-in .cd command.

Read the entire blog post here with interactive SQL codeblocks and playground like environment

SQLite dot commands: change directory command | Meet Gor

Change directory dot command 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 d

meetgor.com

SQLite dot commands: run system shell commands

Meet Rajesh Gor — Tue, 16 Sep 2025 16:41:52 +0000

Shell dot command

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 <command> to execute any shell commands right from within the sqlite shell. How handy is this!

.shell echo "hello, world!"

That is a lame example, but it shows you the power of the .shell command.

Let's say I want to run a golang project, I can do this:

.shell go run main.go

Its helpful if you want to do something but you don't want to quit the shell to do that:

look up few files/datapoints from the local filesystem,
run scripts to populate data
populate database and then reopen the db shell

This is are the things that I have stumbled upon, so far. Need more experience to see if there are more.

Read the entire blog post here with interactive SQL codeblocks and playground like environment

SQLite dot commands: run system shell commands | Meet Gor

Shell dot command 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 to

meetgor.com

SQLite dot commands: read command is recursive?

Meet Rajesh Gor — Mon, 15 Sep 2025 17:54:19 +0000

Yesterday, while exploring the .read command I discovered a little hack.

We know you can read arbitrary sqlite shell commands from the .read | operator, even the dot commands.

So, you can even use .read itself again. Which means we can call the same file to read inside the script.

Ops!

Let's create a file called loop.sh this will emit certain valid sql/sqlite commands like so:

#!/bin/bash
echo "SELECT 'hello, world!';"
echo ".read |./loop.sh"

The first will print out "SELECT 'hello world!';" so when directed to the shell, it will be executed and print "hello, world!"

Then the 3rd line echo ".read |./loop.sh" is the magic, it will execute the script again right?

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 loop.sh shell script.

What's the output?

$ cat loop.sh
#!/bin/bash
echo "SELECT 'hello, world!';"
echo ".read |./loop.sh"

$ sqlite3
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read |./loop.sh
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
hello, world!
Input nesting limit (25) reached at line 2. Check recursion.
sqlite>

Well, SQLite developers are smarter to handle the edge case I think. :)

This is a little fun, but it's kind of a quirk, an not-so easter egg but interesting thing to know.

Read the entire blog post here with interactive SQL codeblocks and playground like environment

SQLite dot commands: read command is recursive? | Meet Gor

Yesterday, while exploring the command I discovered a little hack. We know you can read arbitrary sqlite shell commands from the operator, even the dot commands

meetgor.com

SQLite dot commands: read file or standard output

Meet Rajesh Gor — Sun, 14 Sep 2025 10:58:25 +0000

Read dot Command

The .read dot command is a quick handy command to import and run your SQL queries in the current session.

You can just pass a SQL file (usually a query ending with ; it will execute each query one by one)

.read filename

Let’s say this is a sql file containing the schema of a database, just one table users.

CREATE TABLE users (
  id   INTEGER PRIMARY KEY,
  name TEXT
);

Writing this sql query in the schema.sql file

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)

.read schema.sql

This will just execute the query, if there are SELECT statements possibly then will output the result set too.

Let’s add some users to the table with the insert_users.sql

INSERT INTO users(name) VALUES('abc'), ('def'), ('ghi');
SELECT * FROM users;

Saving this and running .read insert_users.sql should now have displayed all the 3 inserted users.

Reading with dot commands

Let’s create one more file with specific dot commands to render a nice table format of a result set from a select statement

.mode table
SELECT * FROM users;

I save the above contents into a file called users.sql

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!

.read users.sql

sqlite> .read users.sql
+----+---------+
| id |  name   |
+----+---------+
| 1  | User_1  |
| 2  | User_2  |
| 3  | User_3  |
| 4  | User_4  |
| 5  | User_5  |
| 6  | User_6  |
| 7  | User_7  |
| 8  | User_8  |
| 9  | User_9  |
| 10 | User_10 |
+----+---------+

Read with standard output

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.

For instance I have a script that would insert 10 users with a bash script like so called inser_users.sh

#!/bin/bash
for i in $(seq 1 10); do
  echo "INSERT INTO users (name) VALUES ('User_$i');"
done

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.

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.

.read | insert_users.sh

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.

SELECT * FROM users;

As you can see it has now inserted all the 10 users.

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.

This is a really great small yet highly important piece of functionality of sqlite shell.

Reference:

SQLite CLI: Reading SQL from a File

Read the entire blog post here with interactive SQL codeblocks and playground like environment

SQLite dot commands: read file or standard output | Meet Gor

Read dot Command The dot command is a quick handy command to import and run your SQL queries in the current session. You can just pass a SQL file (usually a que

meetgor.com

SQLite Scalar function: 3 valued iif and if scalar function

Meet Rajesh Gor — Sat, 13 Sep 2025 14:34:45 +0000

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.

IIF Before 3.48

Before this release it was kind of 3 way /ternary operation kind of thing like this

Simple IF THEN ELSE

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.

SELECT iif(1=0, 'one is zero', 'one is not zero');

one is not zero

Ternary like nested IIF

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

SELECT iif(7%2=0, 'even', iif(7%3=0, 'multiple of 3', 'prime')) AS is_prime;

+---------+
| is_prime|
+---------+
| prime   |
+---------+

That is quite simple but it gets little messed up for more than one check, take this for instance

SELECT iif(2025 % 400 = 0, 'leap',
       iif(2025 % 100 = 0, 'not leap',
       iif(2025 % 4 = 0, 'leap', 'not leap'))) AS  is_leap_year;

+--------------+
| is_leap_year |
+--------------+
| not leap     |
+--------------+

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.

IIF After 3.48

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:

SELECT iif(2025 % 400 = 0, 'leap',
           2025 % 100 = 0, 'not leap',
           2025 % 4 = 0, 'leap', 'not leap') AS is_leap_year;

+--------------+
| is_leap_year |
+--------------+
| not leap     |
+--------------+

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.

Now it has 3 variants

3 valued expression (single expression, could be nested too, but three expressions, like → condition, true, false)
2 valued expression (assumes the false value is NULL)
N condition - valued pair expression (two pair of condition and value)

These is neat and makes a lot of sense.

Let’s see the same example in all 3 variants

3 Valued Expression

This is the most simple one

SELECT iif(2025%4=0, 'probably leap', 'not leap') AS is_leap_year;

+--------------+
| is_leap_year |
+--------------+
| not leap     |
+--------------+

This returned the false value which happens when the condition evaluated to false.

2 Valued Expression

SELECT iif(2025%4=0, 'probably leap') AS is_leap_year;

+--------------+
| is_leap_year |
+--------------+
|              |
+--------------+

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.

N Pair Expression

So moving on the final one which is the most readable version of it and allows more stuff

SELECT iif(2025 % 400 = 0, 'leap',
           2025 % 100 = 0, 'not leap',
           2025 % 4 = 0, 'leap',
           'not leap')
|| ' year' AS is_leap_year;

+---------------+
| is_leap_year  |
+---------------+
| not leap year |
+---------------+

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)

Other way to remember is

Even parameters and all conditions are
- True (the first value) or equivalent corresponding value for that condition
- False (it will return NULL)
Odd parameters and all conditions are
- True (the first value) or equivalent corresponding value for that condition
- False (it will return the last parameter which is the false condition)

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.

Read the entire blog post here with interactive SQL codeblocks and playground like environment

SQLite Scalar function: 3 valued iif and if scalar function | Meet Gor

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 adde

meetgor.com

SQLite functions: unistr and unistr quote

Meet Rajesh Gor — Fri, 12 Sep 2025 16:37:01 +0000

SQLite 3.50 introduced unistr and unistr_quote functions

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

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.

I wonder these functions were added because of LLMs emoji-heavy outputs?

Here’s how you use unistr in a simple query

SELECT unistr('\u2665, \U0001F600');

This will print the :heart and :smiley emojis

I won’t print the emojis, it might look like a AI-generated post (grunts with pride)

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

That is where the unistr_quote comes in

sql SELECT unistr_quote('\U0001F600');

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,

so if we have something like this

sql SELECT printf('what the heck is this%s symbol?', unistr_quote('\U0001F449'));

output: what the heck is this'\U0001F449' symbol?

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.

Read the entire blog post here with interactive SQL codeblocks and playground like environment

SQLite functions: unistr and unistr_quote | Meet Gor

SQLite introduced and functions These are for taking in escape sequences and returning the actual unicode string like emoji or other non-english characters outs

meetgor.com

True, we can also have empathy on the maintainers because with all of this AI slop, people. start spamming the project too. It might be a miatake in this case, but can relate why the friction is added, to sift out the genuine ones. It might back fire too

Meet Rajesh Gor — Fri, 12 Sep 2025 11:54:13 +0000

Jan Küster 🔥

Sep 10 '25

Wasted Open Source efforts 😮

#opensource #productivity #python #github

Comments 16

4 min read

SQLite dot commands: html tables with output and once

Meet Rajesh Gor — Thu, 11 Sep 2025 18:28:12 +0000

The .once -w or .www was released in SQLite 3.47 release last year.

I was trying this command

.once -w results.html

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.

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.

What is .once -w or .output -w or .www

All of these are equivalent

.once -w
.www

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.

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 accesible 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)

However, if you are thinking of getting some specific file to write the results then you need to do some work

.output result.html
.print '<!DOCTYPE html><html><body><TABLE border="1" cellspacing="0" cellpadding="2">'
.mode html
.headers on
SELECT abs(random()%10)+1 as "some numbers", 'number' from generate_series(1,10);
.print '</table></body></html>'

This will store the html document in the result.html with proper tags and semantics not just the table element

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 elemetnts without the html doctype tags.

Read the entire blog post here with interactive SQL codeblocks and playground like environment

SQLite dot commands: html tables with output and once | Meet Gor

The or was released in release last year. I was trying this command But was getting an error that it was not a valid argument, and the .www command doesn’t ex

meetgor.com

SQLite: Randomly order rows

Meet Rajesh Gor — Wed, 10 Sep 2025 17:30:46 +0000

Return rows or records in a table with random order in SQLite

Let's take a simple table for this:

CREATE TABLE user (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);
INSERT INTO user (name) VALUES ('abc'), ('def'), ('ghi'), ('jkl'), ('mno');

SELECT * FROM user ORDER BY random();

This will return the rows in a random order

You can limit the number of rows

SELECT * FROM user ORDER BY random() LIMIT 5;

We can even randomly shuffle a subset of rows with limit and where clauses

SELECT * FROM user
WHERE id BETWEEN 1 and 10 ORDER BY random() limit 5;

This is cool if you want to get certain subset of samples but in no strict order.

Read the full post with interactive SQL codeblocks and playground like environment here.

SQLite: Randomly order rows | Meet Gor

Return rows or records in a table with random order in SQLite Let's take a simple table for this: This will return the rows in a random order You can limit the

meetgor.com