Building and Using a `.sqliterc` File in SQLite

Building and Using a `.sqliterc` File in SQLite



Introduction

The .sqliterc file is a powerful tool that allows you to customize your SQLite experience by automatically applying settings every time you open an SQLite session. In this guide, we'll walk through building your .sqliterc file and show how SQLite interacts with it when launching from the command line.



Step 1: Building Your `.sqliterc` File

If you don't already have a .sqliterc file, creating one is easy. This file is stored in your home directory and can contain dot commands that customize the behavior of the SQLite interactive session. Here's an example of a simple .sqliterc file:

.headers on
.mode column
.nullvalue NULL

These commands control how your data is displayed:

• .headers on: Ensures that column headers are shown by default in query results.

• .mode column: Organizes the output into aligned columns, making it easier to read.

• .nullvalue NULL: Displays NULL values as "NULL" instead of leaving them blank.

To create the file:

1. Open your terminal and navigate to your home directory:

$ cd ~

2. Use a text editor like nano to create or modify the .sqliterc file:

$ nano .sqliterc

3. Add the dot commands above to the file and save it.



Step 2: Launching SQLite with the `.sqliterc` File

Once you've built your .sqliterc file, SQLite will automatically load the file each time you start a session. When you launch SQLite from the command line, it will display a message confirming that it's loading your configuration:

$ sqlite3 mydb.sqlite
-- Loading resources from /home/pi/.sqliterc
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite>

As shown above, SQLite confirms that it's loading resources from the .sqliterc file, and from that point onward, your session will adhere to the settings specified in the file. This means queries will automatically include column headers, format output in columns, and display NULL values as "NULL."



Step 3: Bypassing the `.sqliterc` File

If you ever want to bypass the .sqliterc file and start a clean SQLite session with default settings, you can use the -init option. Here’s an example:

$ sqlite3 -init /dev/null mydb.sqlite
-- Loading resources from /dev/null
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite>

In this case, SQLite reads from /dev/null, meaning it skips any configurations from your .sqliterc file. You’ll start with the default SQLite settings, which is useful for troubleshooting or running different setups temporarily.


Conclusion

The .sqliterc file allows you to automate your preferred settings and customize the SQLite CLI to better suit your workflow. By setting commands like .headers on and .mode column, you ensure that each session behaves exactly as you want it to. And when you need a clean slate, you can bypass the file using -init /dev/null.

Source:  SQLite.org - Command Line Shell For SQLite

Image: Sárfi Benjámin from Pixabay