SQLite is a powerful, serverless database engine, and sqlite-utils is a command-line tool that makes it easy to manage SQLite databases. This comprehensive guide will show you how to use sqlite-utils to perform common database operations, work with JSON data, and manage database schemas effectively.

## What is sqlite-utils?

sqlite-utils is a command-line tool that provides a simple interface for managing SQLite databases. It's particularly useful for:
- Creating and managing SQLite databases
- Performing CRUD operations
- Working with JSON data
- Managing database schemas
- Data import and export
- Schema migrations

## Prerequisites

Before you begin, ensure you have:
- sqlite-utils installed (`pip install sqlite-utils`)
- Basic understanding of SQLite and database concepts
- Command-line experience
- Python 3.6 or later
- Familiarity with JSON data format

## Installation and Setup

### Installing sqlite-utils

```sh
# Install using pip
pip install sqlite-utils

# Verify installation
sqlite-utils --version
```

### Creating Your First Database

```sh
# Create a new database
sqlite-utils create-database person.db

# Verify database creation
sqlite-utils tables person.db
```

## Basic Operations

### Creating Tables

Create a table with specified columns and primary key:

```sh
# Basic table creation
sqlite-utils create-table person.db people \
  id integer \
  name text \
  height float \
  photo blob \
  --pk id

# Create table with foreign key
sqlite-utils create-table person.db addresses \
  id integer \
  person_id integer \
  street text \
  city text \
  --pk id \
  --foreign-key person_id people id
```

### Managing Data

#### Inserting and Updating Records

Upsert (insert or update) records using JSON data:

```sh
# Insert single record
echo '{"id": 1, "name": "Linus Torvalds"}' | \
  sqlite-utils insert person.db people -

# Insert multiple records
echo '[ 
  {"id": 1, "name": "Linus Torvalds"}, 
  {"id": 2, "name": "Steve Wozniak"}, 
  {"id": 3, "name": "Tony Hoare"} 
]' | sqlite-utils upsert person.db people - --pk id

# Update existing records
echo '{"id": 1, "name": "Linus Torvalds Updated"}' | \
  sqlite-utils upsert person.db people - --pk id
```

#### Querying Records

View and filter records:

```sh
# View all records
sqlite-utils rows person.db people

# View with column headers
sqlite-utils rows person.db people --headers

# Filter records
sqlite-utils query person.db "select * from people where name like '%Torvalds%'"

# Sort records
sqlite-utils rows person.db people --order-by name
```

#### Deleting Records

Delete records using SQL queries:

```sh
# Delete by condition
sqlite-utils query person.db "delete from people where name = 'Tony Hoare'"

# Delete all records
sqlite-utils query person.db "delete from people"
```

### Table Management

#### Schema Operations

```sh
# View table schema
sqlite-utils schema person.db people

# Add new column
sqlite-utils add-column person.db people email text

# Drop column
sqlite-utils drop-column person.db people email

# Drop table
sqlite-utils drop-table person.db people
```

## Advanced Features

### Working with JSON

#### Importing JSON Data

```sh
# Import from JSON file
sqlite-utils insert person.db people data.json

# Import from JSON array
echo '[{"name": "John"}, {"name": "Jane"}]' | \
  sqlite-utils insert person.db people -
```

#### Exporting to JSON

```sh
# Export table to JSON
sqlite-utils rows person.db people --json

# Export with specific columns
sqlite-utils rows person.db people --json --columns name,email
```

### Database Operations

#### Backup and Restore

```sh
# Backup database
sqlite-utils dump person.db > backup.sql

# Restore from backup
sqlite-utils restore person.db backup.sql
```

#### Index Management

```sh
# Create index
sqlite-utils create-index person.db people name

# View indexes
sqlite-utils indexes person.db
```

## Best Practices

1. Database Design
   - Always specify primary keys
   - Use appropriate data types
   - Implement foreign key constraints
   - Create indexes for frequently queried columns

2. Data Management
   - Use transactions for complex operations
   - Validate data before insertion
   - Regular database backups
   - Monitor database size and performance

3. Security
   - Set appropriate file permissions
   - Use parameterized queries
   - Validate user input
   - Regular security audits

## Troubleshooting

### Common Issues

1. Installation Problems
   - Verify Python version
   - Check pip installation
   - Update pip if needed
   - Check system dependencies

2. Database Operations
   - Verify file permissions
   - Check disk space
   - Validate SQL syntax
   - Monitor error logs

3. Performance Issues
   - Optimize queries
   - Create appropriate indexes
   - Monitor database size
   - Regular maintenance

## Further Reading

- [SQLite Documentation]
- [sqlite-utils GitHub Repository]
- [Database Design Best Practices]
- [SQLite Performance Tuning]
- [Data Import/Export Guide]

[sqlite]: https://www.sqlite.org/about.html
[sqlite documentation]: https://www.sqlite.org/docs.html
[sqlite-utils github repository]: https://github.com/simonw/sqlite-utils
[database design best practices]: https://www.sqlite.org/datatype3.html
[sqlite performance tuning]: https://www.sqlite.org/optoverview.html
[data import/export guide]: https://sqlite-utils.datasette.io/en/stable/cli.html#working-with-json