You need a database that lives in a file
You wrote a Python script to track your home inventory, or you inherited a legacy tool that expects a database file. You run the command and get sqlite3: command not found. Or the script crashes with no such module: sqlite3 even though you assume it is installed. You need a database that requires no daemon, no network configuration, and no user management. SQLite is the answer. Fedora minimal installs do not include the SQLite command-line tool by default. You need to pull it in.
What is actually happening
SQLite is not a server. There is no background process to start, no port to open, and no service to enable. The database is a single file on disk. When an application opens the database, it reads the file directly. When the application closes, the file is updated. Think of it like a spreadsheet that understands SQL. You can copy the file to another machine, email it to a colleague, or version control it. The recipient can open it with any SQLite-compatible tool without installing a database server.
This architecture makes SQLite ideal for local tools, embedded applications, development environments, and caching layers. It also shifts the operational burden. There is no database user to manage. Security relies entirely on file permissions and directory access. Backups are file copies. Performance depends on disk I/O and file locking rather than network latency.
Install SQLite
Run a metadata refresh before installing new packages. Stale metadata can cause dependency resolution failures.
sudo dnf upgrade --refresh
# Forces dnf to download fresh repo metadata
# Prevents errors caused by outdated package lists
Install the runtime and the development headers. The headers are required only if you compile software that links against SQLite.
sudo dnf install sqlite sqlite-devel
# sqlite provides the sqlite3 binary and shared libraries
# sqlite-devel installs headers and static libs for compiling C extensions
Run dnf upgrade --refresh first. Fresh metadata prevents dependency resolution failures.
Verify the installation
Check that the binary is on your path and reports the library version.
sqlite3 --version
# Confirms the CLI is installed and prints the version string
# Output includes the date and build info, e.g., 3.45.1 2024-01-xx
Check the version string. If it is older than expected, your mirrors might be stale.
Create and query a database
Create a database file in your home directory and open the interactive shell. The file is created automatically if it does not exist.
sqlite3 ~/mydata.db
# Creates mydata.db in the home directory if missing
# Opens the interactive SQL shell for ad-hoc queries
Define a table, insert data, and query it. Use .quit to exit the shell.
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
SELECT * FROM users;
.quit
# Creates the schema, adds a row, retrieves the data, and exits
Close the shell with .quit. The database file persists on disk. You can open it again later with the same command.
Shell meta-commands
The SQLite shell supports dot-commands that control the interface. These commands start with a period and are not SQL. They do not modify the database.
| Command | Action |
|---|---|
.tables |
List all tables in the database |
.schema tablename |
Show the CREATE statement for a table |
.mode column |
Format output with aligned columns |
.headers on |
Display column names in output |
.import file.csv tablename |
Import CSV data into a table |
.dump |
Export the entire database as SQL text |
Set your preferred defaults in ~/.sqliterc. The shell reads this file on startup.
.mode column
.headers on
# Sets column-aligned output and headers by default
# Applied automatically when sqlite3 starts
Edit ~/.sqliterc once. Your shell configuration survives reboots and updates.
Non-interactive usage
Run a query without entering the interactive shell. This is useful for scripts and one-off checks.
sqlite3 ~/mydata.db "SELECT * FROM users;"
# Executes the SQL string and prints the result to stdout
# Returns exit code 0 on success, non-zero on error
Parse the output in a script. SQLite returns plain text by default, which is easy to grep or pipe.
Enable WAL mode for concurrency
SQLite uses a rollback journal by default. A writer locks the database file, blocking readers. Write-Ahead Logging (WAL) mode improves concurrency for read-heavy workloads. WAL creates a separate log file. Readers see the main database file. Writers append to the log. When a transaction commits, the log is checkpointed to the main file.
Enable WAL mode in the database.
sqlite3 ~/mydata.db "PRAGMA journal_mode=WAL;"
# Switches to Write-Ahead Logging mode
# Allows readers and writers to operate concurrently
# Returns "wal" to confirm the change
WAL mode creates two additional files: mydata.db-wal and mydata.db-shm. Do not delete these files manually. They contain uncommitted changes and shared memory state. Backup all three files or use the .backup command.
Backup all three files. The WAL file contains uncommitted changes that are part of the database state.
Maintenance and vacuum
Databases grow as data is inserted and deleted. Free space remains in the file until you rebuild it. The VACUUM command rebuilds the database file to reclaim free space and defragment the data.
Run vacuum to shrink the file.
sqlite3 ~/mydata.db "VACUUM;"
# Rebuilds the database file to reclaim free space
# Creates a temporary copy and replaces the original
# Takes an exclusive lock during execution
Vacuum locks the database. Run it during a maintenance window or when no other process is accessing the file.
Run vacuum when the file size is significantly larger than the data size. Check with du -h and compare to the row count.
Language bindings
Python includes SQLite support in the standard library. The sqlite3 module is part of python3. You do not need to install extra packages to use SQLite from Python on Fedora.
import sqlite3
# The sqlite3 module is available by default
# No dnf install required for basic usage
Install bindings for other languages if your application requires them.
sudo dnf install perl-DBD-SQLite tcl-sqlite3
# perl-DBD-SQLite provides the DBI interface for Perl scripts
# tcl-sqlite3 adds SQLite support to the Tcl interpreter
Trust the package manager. Fedora ships pre-built bindings that are tested against the system SQLite version.
GUI tools
Install a graphical interface for visual inspection and editing. DB Browser for SQLite is the standard tool.
sudo dnf install sqlitebrowser
# Installs DB Browser for SQLite
# Provides a GUI for browsing tables, running queries, and importing data
Launch the tool from your application menu or run sqlitebrowser in the terminal.
Install the GUI when you need to inspect a database without writing SQL. Use the CLI for automation and scripts.
Backup and restore
Because the database is a single file, copying it creates a backup. However, if the database is open and being written to, a simple copy might capture a partial write. Use the .backup command for a consistent snapshot while the database is active.
Create a consistent backup.
sqlite3 ~/mydata.db ".backup ~/mydata.db.bak"
# Uses the internal backup API to create a consistent copy
# Safer than cp if the database is currently in use
# Handles locks and journal state correctly
Restore from the backup by copying the file back.
cp ~/mydata.db.bak ~/mydata.db
# Restores the database from the backup file
# Ensure no process is using the database before overwriting
Test your backups. Restore to a temporary file and query it. A backup you cannot restore is just a file taking up space.
Common pitfalls
You will encounter specific errors when permissions, locks, or disk space cause issues.
Permission denied. You see Error: unable to open database file if the directory lacks write permissions. SQLite checks the permissions of the directory, not just the file. The process needs write access to the directory to create journal or WAL files.
ls -ld ~/
# Checks the permissions of the home directory
# Ensure the user has write permission (w) on the directory
Check permissions before blaming the database. ls -l reveals the owner and mode instantly.
Database locked. You see Error: database is locked if another process holds a write lock. SQLite allows multiple readers but only one writer. If a long transaction is running, other writers wait. If the lock persists, check for a stale process or a crashed application that left a lock file.
SELinux denials. If you place a SQLite database in a web root like /var/www/html/, SELinux blocks the web server from writing to it. Restore the context or move the database outside the web root.
restorecon -v /var/www/html/mydata.db
# Restores the default SELinux context for the file
# May still block writes depending on the policy
Move the database to /var/lib/ or a dedicated directory. Web roots should contain static content, not writable database files.
Check SELinux logs with journalctl -t setroubleshoot if you suspect policy denials. Read the one-line summary before disabling SELinux.
When to use SQLite
Use SQLite when you need a zero-config database for a local tool or embedded application. Use SQLite when you want the database to be a single portable file. Use SQLite when you have read-heavy workloads and moderate concurrency. Use PostgreSQL or MySQL when you have concurrent writers, need network access, or require advanced replication. Use MongoDB when your data is unstructured JSON and you need flexible schemas. Stay on SQLite for development and testing before migrating to a server database for production scale.
Start with SQLite. Add complexity only when the single-file model breaks.