In this article on data analysis, When CSV Stops Being Enough, we reached an important conclusion:

What are the things I am keeping track of, and how are they related?

That question takes us beyond the humble CSV file.

But there is a danger at this point.

We might hear the word database and imagine servers, administrators, configuration files, users, permissions, backups, replication, clusters, and a person whose full-time job is keeping the database alive.

That is one kind of database.

It is not where we need to begin.

We are Textsmiths.

We like tools that are small, composable, scriptable, inspectable, and useful from the command line.

And there is a database that fits remarkably well into that way of thinking:

SQLite.

The database you may already have

SQLite is unusual because it does not require the traditional database-server arrangement.

There is no database daemon waiting for connections.

There is no server to configure before we can start working.

There is no separate database service that has to be running in the background.

Instead, the database can simply be a file.

students.db

That is the whole idea.

The database is an ordinary file containing our database.

This makes SQLite feel much more familiar to someone coming from the world of CSV and plain text.

We are not abandoning files.

We are giving one of our files more structure.

From CSV to SQLite

Suppose we have been keeping our students in:

students.csv

and our modules in:

modules.csv

and enrolments in:

enrolments.csv

We could continue juggling these files with scripts.

And sometimes that is exactly the right thing to do.

But once relationships become important, we might create:

university.db

Inside that one file we can have tables such as:

students
modules
enrolments
lecturers
faculties

Now the relationships between those tables can be represented explicitly.

The database becomes a small, self-contained data system.

You don’t need to become a database administrator

This is perhaps the most important reassurance.

Learning SQLite does not mean that we must immediately learn everything about database administration.

We don’t need to begin by learning how to configure a production PostgreSQL cluster.

We don’t need to understand database replication.

We don’t need to worry about running a database server on another machine.

We don’t even need to abandon the command line.

We can simply open a database and start asking it questions.

For example:

sqlite3 university.db

And now we are at the SQLite prompt.

We can ask:

SELECT * FROM students;

Or:

SELECT first_name, last_name
FROM students
WHERE age > 20;

The experience is remarkably close to the way we already think about data.

We have records.

We have fields.

We have transformations.

We have queries.

The difference is that now the database understands the relationships between our collections of records.

SQL gives us a familiar language

SQLite supports the core SQL operations that make relational databases useful.

We can create records:

INSERT INTO students
    (student_id, first_name, last_name, age)
VALUES
    ('S001', 'Tendai', 'Moyo', 21);

We can read them:

SELECT first_name, last_name
FROM students;

We can update them:

UPDATE students
SET age = 22
WHERE student_id = 'S001';

And we can delete them:

DELETE FROM students
WHERE student_id = 'S001';

These are the familiar CRUD operations:

Create
Read
Update
Delete

For a Textsmith who is accustomed to transforming files, SQL adds another language for expressing transformations.

And that is the important way to approach it.

We don’t need to think of SQL as some mysterious database language.

We can think of it as a language for asking questions about structured data.

The interesting part is the JOIN

The real reason we came here from CSV is relationships.

Suppose we have:

students

enrolments

and:

modules

We can ask:

SELECT students.first_name,
       students.last_name,
       modules.module_name
FROM students
JOIN enrolments
  ON students.student_id = enrolments.student_id
JOIN modules
  ON enrolments.module_id = modules.module_id;

Now the database can follow the relationships.

We are asking:

Find the student, follow the student’s enrolment, find the corresponding module, and give me the module name.

That is much more expressive than trying to manually coordinate three CSV files.

And notice something important.

We haven’t become database administrators.

We have simply learned how to ask a better question.

SQLite fits into a pipeline

This is where SQLite becomes particularly attractive to Textsmiths.

We already have pipelines.

Perhaps we download data:

curl ...

Then inspect it:

head data.csv

Search it:

grep ...

Transform it:

awk ...

Analyse it with Python or R.

Now we can add another possibility:

CSV
  |
  v
SQLite
  |
  v
SQL queries
  |
  v
CSV / JSON / report

SQLite doesn’t have to replace our existing toolbox.

It can become another stage in it.

We might import a collection of CSV files into SQLite, perform relational queries, and export the result for further processing.

The database becomes part of the pipeline rather than an obstacle standing between us and our data.

A database can still be a file

There is something philosophically satisfying about this.

We often think of “database” and “file” as two different worlds.

But SQLite gives us a useful middle ground.

Our database might simply be:

project/
├── data/
│   ├── raw/
│   │   ├── students.csv
│   │   └── modules.csv
│   └── university.db
├── scripts/
└── reports/

There is no database server hiding somewhere else.

There is simply a database file in our project.

That makes SQLite particularly convenient for small and medium-sized projects, personal data analysis, experiments, utilities, prototypes, and applications that do not need a separate database server.

But isn’t SQLite “less powerful”?

This is where we need to be precise.

SQLite is not intended to solve every database problem.

A large multi-user application with thousands of simultaneous writers may have requirements that make a client-server database more appropriate.

But that is not an argument against SQLite.

It is an argument for choosing tools according to the problem.

We don’t reject grep because it cannot replace PostgreSQL.

We don’t reject CSV because it cannot represent a relational model.

And we shouldn’t reject SQLite because it isn’t designed to be every kind of database.

The Textsmith’s question is always:

What problem am I trying to solve?

For many data-processing tasks, SQLite may be more than enough.

From files to tables

There is also a useful conceptual progression here.

We might begin with:

students.csv

and think:

I have a file containing student records.

Then we discover relationships and create:

university.db

Now we can think:

I have a database containing students, modules, and enrolments.

That is a significant change in how we think about our data.

The data is no longer merely a collection of files.

It has a structure.

We can define tables.

We can define keys.

We can establish relationships.

We can query those relationships.

And because SQLite keeps everything in one file, we haven’t had to construct an elaborate infrastructure to get there.

You can still use your favourite languages

SQLite also plays nicely with the tools many Textsmiths already use.

Python has SQLite support through its standard library.

R can work with SQLite through database packages.

Shell scripts can invoke the sqlite3 command-line client.

Other programming languages have SQLite libraries.

So the database doesn’t force us into one particular environment.

We can continue working in the tools we already know.

For example, a Python program can query SQLite and process the results.

An R analysis can read from the database.

A shell pipeline can export a query as CSV.

The database is simply another source and destination for structured data.

The database is not the end of the pipeline

This is an important mindset.

We should not think:

CSV → DATABASE → EVERYTHING ELSE

as though the database has replaced our toolbox.

Think instead:

       ┌── grep
       │
       ├── awk
       │
CSV ───┼── Python
       │
       ├── R
       │
       └── SQLite
              |
              v
             SQL
              |
              v
       analysis / report

Each tool has a job.

CSV remains excellent for interchange.

grep remains excellent for searching.

awk remains excellent for record-oriented transformations.

Python and R remain excellent for analysis.

SQLite becomes excellent when our data has relationships that deserve to be represented explicitly.

This is precisely the Unix way of thinking:

Don’t ask one tool to do everything. Give each tool a job.

SQLite lowers the barrier

Perhaps this is the greatest reason to learn it.

A database can sound like a major technological commitment.

SQLite makes it almost casual.

You can have:

sqlite3 notes.db

and within moments you are creating tables and querying data.

You can experiment.

You can make mistakes.

You can throw the database away and start again.

You can script the entire process.

You can keep the database alongside your code.

You can put the surrounding project under version control.

And most importantly, you can learn relational thinking without first having to learn database administration.

The Textsmith’s database

There is a larger lesson here.

We began with plain text.

Then we learned that CSV gives us a convenient way to represent records.

Then we discovered that records can have relationships.

And now SQLite gives us a way to represent those relationships without abandoning the things we value about small, file-based tools.

The progression is natural:

Plain text
    ↓
CSV
    ↓
Related records
    ↓
SQLite
    ↓
SQL

We haven’t abandoned the Textsmith’s philosophy.

We have extended it.

The point is not to collect another tool.

The point is to gain another way of thinking.

When the question is simply:

What does this row contain?

CSV may be enough.

When the question becomes:

How does this record relate to those other records?

we should start thinking relationally.

And when we want to do that without diving into the deep end of database administration, there is a remarkably approachable place to begin.

SQLite: the database you already have.

The next time your CSV files begin acquiring siblings, identifiers, repeated fields, and increasingly complicated joins between them, don’t immediately reach for a database server.

Try SQLite.

It may be exactly the missing tool in your Textsmith’s forge.