The SQLite command line shell provides the .backup dot command that enables you to quickly and easily back up a database.
To use this command, provide the name of the database you want to back up, and a file name for the backup file.
SQLite
Export an Entire SQLite Database to an SQL File
The SQLite command line provides several methods for exporting or backing up an entire database. One of those involves the .dump dot command.
The .dump command enables you to convert a whole database to a single ASCII text file. In other words, it renders the whole database as SQL. The text file contains all the SQL statements required to create the tables, insert data, etc.
You can also use .dump to create a compressed file. I outline both of these methods below.
Automatically Open SQLite Query Results in Excel
The SQLite command line interface has a handy little feature where you can open your SQL query results in an Excel file.
When you run a query, instead of the query being output to your console as it normally would, it instead opens as an Excel spreadsheet (or LibreOffice, or whatever program your system uses to open CSV files).
You have two options when doing this:
- Use a temporary file
- Use a permanent file (i.e. save it for later)
This article covers both options.
Output SQLite Query Results as a Tab-Separated List
The SQLite command line interface allows you to use various modes for formatting the result set of SQL queries. For example, you can format the results in columns, as a comma separated list, using vertical output, and more.
This article explains how to use the .mode dot command to format your results as a tab-separated list.
Display SQLite Query Results using Vertical Output
One of the values you can provide to the .mode dot command when using the SQLite command line shell is line.
Using .mode line outputs the query results using vertical output. What I mean is that, each column is displayed on a new line. The column name is displayed, along with an equals sign and then the column’s value.