SQLite

SQLite is a lightweight, file-based database management system. Unlike other databases like MySQL or PostgreSQL, SQLite doesn’t require a separate server process. It reads and writes directly to disk files. A whole database, with multiple tables, indexes, triggers, and views, is contained in a single disk file. Why SQLite?

  1. Lightweight: SQLite is incredibly lightweight. It’s a mere few megabytes in size and requires minimal resources to run. This makes it ideal for small to medium-sized applications, embedded systems, and even large-scale applications needing simple data storage.

  2. Serverless: SQLite doesn’t operate on the traditional client-server model. There’s no need to install, manage, or configure a server. This simplicity is a huge win for developers who want to focus on their application without worrying about database administration.

  3. Easy to Use: SQLite databases are easy to backup and transfer. You can literally copy the database file to a new location, and you’re done!

  4. Reliable: It’s known for its reliability. Data integrity is a key focus, with features like atomic commit, rollback, and robust crash recovery.

  5. Widely Used: You might be surprised to learn that SQLite is used in many applications you interact with daily. It’s in browsers (like Chrome, Firefox), operating systems (like Windows, iOS, Android), and popular applications (like Skype, iTunes).

Inserting Rows into a Table

To insert a new row into a table:

sqlite3 /path/to/your/database.db "INSERT INTO table_name (column1, column2) VALUES (value1, value2);"

Replace table_name, column1, column2, value1, and value2 with your actual table and column names and the values you wish to insert.

Deleting Rows from a Table

To delete rows from a table:

sqlite3 /path/to/your/database.db "DELETE FROM table_name WHERE condition;"

Specify the condition to match the rows you want to delete, like column_name = value.

Updating Rows in a Table

To update existing rows:

sqlite3 /path/to/your/database.db "UPDATE table_name SET column1 = value1 WHERE condition;"

Here, condition determines which rows get updated.

Identifying and Fixing Corrupt Rows

Finding Corrupt Rows

SQLite3 is robust, but corruption can occur. Identifying corrupt rows typically involves:

  1. Running queries and looking for errors or unexpected results.
  2. Using PRAGMA integrity_check to scan the database for signs of corruption.

Fixing Corrupt Rows

Directly fixing corrupt rows via CLI is tricky. It usually involves:

  1. Exporting the data from the table.
  2. Manually correcting the data in the export file.
  3. Importing the corrected data back into the database.

However, if the corruption is due to structural issues in the database, you might need to export all data, recreate the database, and then import the data back.

Finding the SQLite Database File used by a application

When the Location is Unknown

Sometimes, the location of the SQLite database file might not be obvious. In such cases, the lsof (List Open Files) command comes to the rescue. Here’s how to use it:

  1. Identify the process ID (PID) of the application using the database (e.g., audisp-rndb) with ps aux | grep [application_name].
  2. Execute lsof -p [PID] to list all files the process is accessing. The database file, typically with a .db extension, should be among these.

Making Backups of Your SQLite Database

Backing up a SQLite database is a straightforward process:

cp /path/to/your/database.db /path/to/backup/database_backup.db

Always ensure you have a recent backup before making any changes to your database.

Clearing a Database While Preserving the Schema

To clear all data from a SQLite database but keep its structure (schema), follow these steps:

  1. Export the schema to a new SQL file:

    sqlite3 /path/to/database.db .schema > /path/to/schema.sql

  2. Recreate the database with the same schema but no data:

    sqlite3 /path/to/new_database.db < /path/to/schema.sql

Repairing a Corrupted SQLite Database

The Standard Approach

  1. Export and Import Data

    • Export data and schema to an SQL file:

      sqlite3 corrupted.db ".dump" > dump.sql

    • Import it into a new database:

      sqlite3 new.db < dump.sql

  2. Check Integrity

    Execute sqlite3 corrupted.db "PRAGMA integrity_check" to identify errors.

  3. Clone the Database

    If the above doesn’t work, try cloning:

    sqlite3 corrupted.db ".clone new.db"

Replace the old database file with the new, repaired one, ensuring correct file permissions and ownership.

Viewing Tables in a SQLite Database

To see what tables exist within your SQLite database, use the .tables command:

sqlite3 /path/to/your/database.db ".tables"

This command lists all tables in the database.

SQLite provides several dot commands that are useful for interacting with databases in the SQLite command-line interface. These commands provide a range of functionalities from managing the databases to formatting the output. Here’s a markdown list of some common dot commands in SQLite:

  • .backup ?DB? FILE : Backup DB (default “main”) to FILE
  • .bail ON|OFF : Stop after hitting an error. Default OFF
  • .databases : List names and files of attached databases
  • .dbinfo ?DB? : Show status information about the database
  • .dump ?TABLE? : Dump the database in an SQL text format. If TABLE specified, only dump tables matching LIKE pattern TABLE.
  • .echo ON|OFF : Turn command echo on or off
  • .exit : Exit this program
  • .explain ON|OFF|? : Turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on.
  • .fullschema : Show schema and the content of sqlite_stat tables
  • .headers ON|OFF : Turn display of headers on or off
  • .help : Show this message
  • .import FILE TABLE : Import data from FILE into TABLE
  • .indexes ?TABLE? : Show names of indexes. If TABLE specified, only show indexes for tables matching LIKE pattern TABLE.
  • .load FILE ?ENTRY? : Load an extension library
  • .log FILE|off : Turn logging on or off. FILE can be stderr/stdout
  • .mode MODE ?TABLE? : Set output mode where MODE is one of: csv, column, html, insert, line, list, quote, tabs, tcl
  • .nullvalue STRING : Use STRING in place of NULL values
  • .open ?FILENAME? : Close existing database and reopen FILENAME
  • .output ?FILE? : Send output to FILE or stdout
  • .print STRING... : Print literal STRING
  • .prompt MAIN CONTINUE : Replace the standard prompts
  • .quit : Exit this program
  • .read FILENAME : Execute SQL in FILENAME
  • .restore ?DB? FILE : Restore content of DB (default “main”) from FILE
  • .save FILE : Write in-memory database into FILE
  • .schema ?TABLE? : Show the CREATE statements. If TABLE specified, only show tables matching LIKE pattern TABLE.
  • .separator STRING : Change separator used by output mode and .import
  • .show : Show the current values for various settings
  • .stats ON|OFF : Show stats or not
  • .system CMD : Run CMD in a system shell
  • .tables ?TABLE? : List names of tables. If TABLE specified, only lis

These commands are integral to effectively managing and querying SQLite databases through the CLI. They provide a wide array of functionalities, from database maintenance tasks to customizing the output format, making the SQLite CLI a versatile tool for database administration.


Buy Me a Coffee