SQLite Provider

SQLite is a lightweight, file-based database that’s perfect for local development, testing, and embedded applications. No server setup required—just point to a database file and start querying.

Connection String Format

The basic SQLite connection string format:

Data Source=path/to/database.db

Connection Examples

Local File
In-Memory
Read-Only
Advanced

Relative Path

# Current directory
Data Source=./mydb.db
Data Source=myapp.db

# Subdirectory
Data Source=./data/mydb.db
Data Source=data/mydb.db

Absolute Path

# Linux/macOS
Data Source=/home/user/databases/app.db
Data Source=/var/lib/myapp/data.db

# Windows
Data Source=C:\Users\username\databases\app.db
Data Source=C:\data\myapp.db

On Windows, use double backslashes (\\) or forward slashes (/) in paths.

In-Memory Database

# Shared in-memory database
Data Source=:memory:

# Named in-memory database (shareable within app)
Data Source=file:memdb1?mode=memory&cache=shared

In-memory databases are temporary and lost when the connection closes. Use for testing or temporary data only.

Read-Only Access

# Open database in read-only mode
Data Source=mydb.db;Mode=ReadOnly

# Absolute path, read-only
Data Source=/path/to/mydb.db;Mode=ReadOnly

Use read-only mode to prevent accidental modifications when browsing production databases.

Advanced Options

# Custom timeout
Data Source=mydb.db;Default Timeout=30

# Shared cache
Data Source=mydb.db;Cache=Shared

# Custom page size and cache size
Data Source=mydb.db;Page Size=4096;Cache Size=10000

# Foreign keys enabled
Data Source=mydb.db;Foreign Keys=True

Foreign key constraints are disabled by default in SQLite. Enable them with Foreign Keys=True if your schema relies on them.

Adding a SQLite Connection

Interactive Mode

queryly connect add

You’ll be prompted:

Name: Enter a friendly name (e.g., “LocalDB”, “TestDB”)
Type: Select “SQLite”
Connection String: Enter your connection string

Example session:

$ queryly connect add
Enter connection name: MyApp
Select database type: SQLite
Enter connection string: Data Source=./myapp.db
✓ Connection test successful!
✓ Connection 'MyApp' saved

Supported Features

Connection Management - Add, test, list, and remove connections

Schema Exploration - List tables and view table structures

Table Browsing - View table data with pagination

Query Execution - Run custom SQL queries interactively

Data Export - Export tables to CSV or JSON formats

File Path Handling

Cross-Platform Paths

Linux/macOS
Windows

# Home directory
Data Source=~/databases/myapp.db
Data Source=/home/user/databases/myapp.db

# Relative to current directory
Data Source=./data/myapp.db
Data Source=../shared/myapp.db

# Temporary directory
Data Source=/tmp/test.db

# User directory
Data Source=C:\Users\username\databases\myapp.db

# Forward slashes also work
Data Source=C:/Users/username/databases/myapp.db

# Relative paths
Data Source=.\data\myapp.db
Data Source=data\myapp.db

# Network share
Data Source=\\\\server\share\databases\myapp.db

Ensure the directory exists before specifying the path. SQLite will create the database file if it doesn’t exist, but will not create parent directories.

SQLite-Specific Tips

Performance Optimization

Enable WAL Mode

WAL (Write-Ahead Logging) mode improves concurrency and performance:

PRAGMA journal_mode=WAL;

Run this query once after creating your database using:

queryly data query MyConnection
SQL> PRAGMA journal_mode=WAL;

Optimize VACUUM

Reclaim unused space and optimize the database:

VACUUM;

Run periodically to maintain optimal performance:

queryly data query MyConnection
SQL> VACUUM;

Analyze Statistics

Update query optimizer statistics:

ANALYZE;

Run after significant data changes:

queryly data query MyConnection
SQL> ANALYZE;

Common Workflows

Create & Populate
Explore Existing
Query & Analyze

# Create a new SQLite database with sqlite3
sqlite3 myapp.db

# In sqlite3 prompt:
sqlite> CREATE TABLE users (
   ...>   id INTEGER PRIMARY KEY,
   ...>   name TEXT NOT NULL,
   ...>   email TEXT UNIQUE
   ...> );
sqlite> INSERT INTO users VALUES (1, 'Alice', '[email protected]');
sqlite> .quit

# Add to Queryly
queryly connect add
# name: MyApp
# type: SQLite
# connection string: Data Source=myapp.db

# Browse the data
queryly data browse MyApp users

# Test connection
queryly connect test MyApp

# List all tables
queryly schema list MyApp

# View table structure
queryly schema info MyApp users

# Browse data
queryly data browse MyApp users

# Export to CSV
queryly data export MyApp users csv

# Enter query mode
queryly data query MyApp

# Run queries
SQL> SELECT COUNT(*) FROM users;
SQL> SELECT * FROM users WHERE email LIKE '%@example.com';
SQL> SELECT DATE(created_at), COUNT(*) 
     FROM orders 
     GROUP BY DATE(created_at);

# Exit query mode
SQL> exit

Troubleshooting

Database file not found

Error: unable to open database fileSolutions:

Verify the file path is correct (check spelling and case)
Use absolute paths to avoid confusion
Ensure parent directory exists
Check file permissions (must be readable)

# Check if file exists
ls -l myapp.db

# Use absolute path
Data Source=/full/path/to/myapp.db

Permission denied

Error: attempt to write a readonly databaseSolutions:

Check file permissions: chmod 664 myapp.db
Check directory permissions (SQLite needs write access to the directory)
Ensure you’re not accessing a read-only filesystem

# Fix permissions
chmod 664 myapp.db
chmod 775 $(dirname myapp.db)

Database is locked

Error: database is lockedSolutions:

Close other applications accessing the database
Wait for long-running queries to complete
Enable WAL mode for better concurrency
Check for stale lock files (.db-shm, .db-wal)

# Enable WAL mode
queryly data query MyApp
SQL> PRAGMA journal_mode=WAL;

Corrupted database

Error: database disk image is malformedSolutions:

Try to recover with .recover command in sqlite3
Restore from backup if available
Check disk space and filesystem integrity

# Attempt recovery
sqlite3 myapp.db ".recover" | sqlite3 myapp_recovered.db

# Verify integrity
sqlite3 myapp.db "PRAGMA integrity_check;"

Best Practices

Use absolute paths in production to avoid ambiguity

Enable WAL mode for better concurrency

Run VACUUM periodically to optimize storage

Regular backups - simply copy the .db file

Foreign keys - explicitly enable if your schema uses them

SQLite is not recommended for:

High-concurrency write workloads
Network/distributed access
Very large datasets (>100GB)
Multi-server deployments

Working with Commands

SQLite works with all Queryly commands:

# Connection management
queryly connect add
queryly connect list
queryly connect test MyApp
queryly connect remove MyApp

# Schema exploration
queryly schema list MyApp
queryly schema info MyApp users
queryly schema tree MyApp

# Data operations
queryly data browse MyApp users
queryly data query MyApp
queryly data export MyApp users csv
queryly data export MyApp users json

Command Reference

See the complete command reference for all available options

Next Steps

Schema Exploration

Learn how to explore database schemas

Data Operations

Browse and query table data

Providers Overview

Compare all database providers

Connection Management

Manage your database connections

Get Started

Commands

Database Providers

Guides

Development

SQLite Provider

Connection String Format

Connection Examples

Relative Path

Absolute Path

In-Memory Database

Read-Only Access

Advanced Options

Adding a SQLite Connection

Interactive Mode

Supported Features

File Path Handling

Cross-Platform Paths

SQLite-Specific Tips

Performance Optimization

Common Workflows

Troubleshooting

Best Practices

Working with Commands

Command Reference

Next Steps

Schema Exploration

Data Operations

Providers Overview

Connection Management

Build docs developers (and LLMs) love

Get Started

Commands

Database Providers

Guides

Development

​Connection String Format

​Connection Examples

​Relative Path

​Absolute Path

​In-Memory Database

​Read-Only Access

​Advanced Options

​Adding a SQLite Connection

​Interactive Mode

​Supported Features

​File Path Handling

​Cross-Platform Paths

​SQLite-Specific Tips

​Performance Optimization

​Common Workflows

​Troubleshooting

​Best Practices

​Working with Commands

Command Reference

​Next Steps

Schema Exploration

Data Operations

Providers Overview

Connection Management

Build docs developers (and LLMs) love

Connection String Format

Connection Examples

Relative Path

Absolute Path

In-Memory Database

Read-Only Access

Advanced Options

Adding a SQLite Connection

Interactive Mode

Supported Features

File Path Handling

Cross-Platform Paths

SQLite-Specific Tips

Performance Optimization

Common Workflows

Troubleshooting

Best Practices

Working with Commands

Next Steps