VACUUM with INTO Clause

The INTO clause with VACUUM is used to provide an user selected file as the temporary database in which all the contents of the original Database will be copied and stored, but here, all the contents will be “vacuumed“, meaning that every blank pages or clutters will be cleared and only the contents which are present in the database will be copied.

The main catch about VACUUM INTO is that, the filename or the file path passes as the argument of INTO clause, should either be non-existent (SQLite will create the file and store the content) or it must be an empty file. If any of the criteria doesn’t satisfy, the statement will result to an error.

Syntax:

VACUUM <database_schema_name> INTO <path_with_filename_extension>;

The generic syntax is pretty simple, here we will use the VACUUM command and then pass the name of the Database schema which we want to copy and save in a temporary location. Then we will use the INTO command and pass the filename or the full path of the file alongside it’s name and extension, where VACUUM command will temporarily store the contents of the Database.

Generally, in case of SQLite, all the “schemas” are being stored in the “main”, so VACUUM works with only the main schema name, as of now, SQLite doesn’t support the VACUUM command with a particular table name present in the database.

Example of VACUUM with INTO Clause

We will VACUUM the main schema of our database into a file named “mydb_backup.db” using the following command.

Query:

VACUUM main INTO 'mydb_backup.db';

If the file doesn’t exists, then SQLite will create the mydb_backup.db file and dump the main schema into it.

User may now use some advanced SQLite command like ATTACH DATABASE and DETACH DATABASE to transfer all the tables and peripherals into the mydb_backup. That part is out of scope of this Article.

Output:

Explanation: As we can see, the Database named mydb_backup now holds every content of the mydb Database, from which the main schema was vacuumed into it.

SQLite is an embedded database that doesn’t use a database like Oracle in the background to operate. It is written in C language and is used by developers who embed a lightweight database over the existing application, browser, or embedded systems. SQLite works on various platforms like Windows, Mac, Linux, iOS and Android. This makes it a popular choice for developers to develop applications for several platforms or Operating Systems.

As the name itself suggests, the VACUUM command of SQLite is especially used to clean the extra space that is either left after the deletion of tables or schemas from a Database, or unused data blocks that are left behind due to the use of commands like INSERT, UPDATE or DELETE. They decrease the space of each Page of the database.

In this article, we will see how we can use the VACUUM command to clean up extra space and increase the performance of our SQLite queries.