how to

Return a List of Databases in SQLite

by

When using the SQLite command line interface, you can use the .databases command to return a list of attached databases. More specifically, it returns the names and file names/location of each attached database.

You can also use the PRAGMA database_list statement to return a list of databases attached to the current database connection.

Read more

Include Column Names in SQLite Query Results

by

By default, the SQLite command line interface displays query results using a pipe-separated list, without column headers.

This might be fine if you’re using the results in an application that doesn’t need the column names, but if you’re just a human, it can make it hard to read the data, especially if there are lots of columns.

Fortunately, there’s a quick way to present the column names in your query results.

Actually, there are at least a couple of ways to do this, and I explain them in this article.

Read more

Backup an SQLite Database

by

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.

Read more

Clone an SQLite Database

by

You can use the SQLite .clone dot command to create a clone of the current database.

The way it works is quite simple. You simply use .clone newdb where newdb is the name of the new database you want created.

Read more

Export an Entire SQLite Database to an SQL File

by

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.

Read more

Automatically Open SQLite Query Results in Excel

by

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.

Read more