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

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.

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

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:

Week #57

It was a fun week, really doubled down on learning SQLite and SQL. Shipped a bit of code and had fun creating improving metrics on the work side of things. Able to see 10% improvement in the things I have been working on the past few months. Really a good vibe week. I finally broke the barrier or imposter syndrome and whatever it might be called it while using the AI-assisted/vibe coding things.

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

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

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.

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?

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.