15.1 SQLite Overview

Understanding SQLite in Android

SQLite is the built in database engine used on Android devices. Every Android device ships with SQLite, so you can store structured, relational data locally without installing anything extra. In this chapter you will see what SQLite is, how it organizes data, and what its main strengths and limitations are for Android apps. You will not yet use Room or code, but you will build the foundation that Room will sit on.

What SQLite Is and How It Fits in Android

SQLite is a small relational database engine that runs inside your app process. It reads and writes data in a single file stored on the device. There is no separate database server, no network, and no background database process. Your app talks directly to the SQLite library.

On Android, when you hear “database” in the context of local storage, it usually means SQLite. Other libraries such as Room, ORMs, or query builders still talk to SQLite behind the scenes. When you define a Room database, Room generates code that creates SQLite tables and sends SQL queries to the SQLite engine.

Because SQLite is relational, it organizes data in tables made of rows and columns. You can create relationships between tables with keys and use SQL queries to filter, sort, and join data. This is very different from simple key value storage like SharedPreferences.

Database Files and Location

An SQLite database is stored in a single file. In Android each app gets its own internal storage area, and SQLite database files are usually created inside that private folder. Other apps cannot access your database file.

A typical SQLite database file might be named app.db or users.db. You can create more than one database file if you need to separate data, but in many apps a single database is enough, with multiple tables inside.

Because the file lives in your app’s internal storage, it is removed when the user uninstalls your app. This means SQLite is a good choice for persistent data that should survive app restarts, but not necessarily survive an uninstall.

Tables, Rows, and Columns

SQLite follows the common relational model. You define tables, each table has columns with data types, and each row in the table holds one record.

A table definition in SQL is written with CREATE TABLE. With Room you will not usually write these manually, but they are still executed under the hood. An example of a simple SQL statement is:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);

This statement creates a table named users with three columns. id is an integer primary key, name is text and cannot be null, and age is an optional integer.

Each row in the users table might look like:

1, "Alice", 30
2, "Bob", 25

When you insert a row, you use SQL INSERT. When you read data, you use SELECT. For example:

SELECT * FROM users WHERE age > 20;

SQLite returns all rows where age is greater than 20.

Primary Keys and Row Identity

In relational databases, a primary key is a column or combination of columns that uniquely identifies each row. In SQLite, primary keys are extremely important because they are also used to build indexes and to connect related tables.

Most Android apps use integer primary keys. You often see a pattern like id INTEGER PRIMARY KEY AUTOINCREMENT. In this pattern, SQLite automatically generates a new integer id for each new row. Even if you do not explicitly write AUTOINCREMENT, SQLite can still generate row ids for an INTEGER PRIMARY KEY.

Room expects each entity to have a primary key field, so understanding this concept now will make it easier to define Room entities later.

Basic SQLite Data Types

Unlike some other database engines, SQLite uses a flexible type system. It has a small set of storage classes and allows some flexibility when inserting values. SQLite’s main storage classes are:

INTEGER for whole numbers.
REAL for floating point numbers.
TEXT for strings stored as text.
BLOB for binary data such as raw bytes.
NULL for missing values.

In practice, most Android app tables use INTEGER and TEXT most of the time, and sometimes REAL. BLOB is used when you want to store raw binary data such as a small image or file fragment directly in the database. Large media files are usually stored in the file system instead, with only the file path kept in SQLite.

Even though SQLite is permissive, it is a good habit to pick consistent column types and use them correctly. This will help a lot when you move to Room, which maps Kotlin types to SQLite types.

Querying Data with SQL

SQLite uses SQL, the Structured Query Language, for all operations. SQL covers creating tables, inserting data, updating existing rows, deleting rows, and querying data. Room will generate SQL for you or allow you to write it in a controlled way, but it still runs SQL in the SQLite engine.

The most common SQL operations are:

SELECT to read data.
INSERT to add data.
UPDATE to modify existing rows.
DELETE to remove rows.

You can also organize results with ORDER BY, filter them with WHERE, and limit how many rows you get back with LIMIT.

For example, to get the five youngest users with a non null age:

SELECT * FROM users
WHERE age IS NOT NULL
ORDER BY age ASC
LIMIT 5;

To update a user’s name:

UPDATE users
SET name = 'Alice Smith'
WHERE id = 1;

SQL can join multiple tables too. For example, a users table and an orders table can be connected through a foreign key user id, and a query can combine both tables to show user information with their orders.

Relationships and Foreign Keys

Relational databases support relationships between tables. In SQLite you can define a foreign key column that refers to the primary key of another table. This allows you to represent one to many and many to many relationships.

As a simple example, suppose you have a users table and a notes table. Each note belongs to one user, so the notes table can have a user_id column that refers to users.id.

In SQL this may look like:

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    content TEXT,
    FOREIGN KEY(user_id) REFERENCES users(id)
);

SQLite can enforce foreign key constraints if they are enabled. When they are enforced, SQLite will prevent you from inserting a note with a user_id that does not exist in the users table, and it can also restrict you from deleting a user who still has notes.

Room takes advantage of this relational structure. When you define relationships in your entities, Room uses foreign keys under the hood to keep the data consistent.

Indexes and Performance Basics

An index is a data structure that improves lookup speed for certain columns. SQLite automatically creates an index for INTEGER PRIMARY KEY columns, which is one reason they are so widely used.

You can create additional indexes to speed up queries that frequently filter or sort by specific columns. For example, if you often search users by name, you might define an index on the name column.

Indexes make reading faster for indexed columns but can slow down writes because SQLite must update the index whenever data changes. You will not tune indexes deeply at beginner level, but it is useful to know they exist because Room can create indexes based on your annotations.

Transactions and Atomicity

A transaction groups one or more SQL operations so that they either all succeed or all fail as a single unit. This is important when you need to update several tables consistently.

For example, imagine you insert a new user and then insert multiple notes that belong to that user. If something goes wrong after the user is inserted but before all notes are saved, you might end up with inconsistent data. A transaction solves this by wrapping all operations together.

With plain SQLite you write BEGIN TRANSACTION, your operations, and then COMMIT to save or ROLLBACK to cancel. Room will provide higher level transaction support, but it still relies on SQLite transactions internally.

Advantages of Using SQLite in Android

SQLite offers several clear benefits for local data in Android apps. It is already available on every device, so there is no extra installation. It is efficient for structured data and can handle large datasets much better than simple files or preferences.

SQLite supports powerful querying with SQL. You can filter, sort, group, and join data without loading everything into memory. This is important on mobile devices where memory is limited. It also makes it easier to answer complex questions such as “all items in a category sorted by date for a particular user.”

Because SQLite is transactional and durable, it is a good choice for important data such as cached API responses, user generated content, or configuration that must not be corrupted if the app or device shuts down unexpectedly. The engine guarantees that committed changes are safely written.

Finally, the relational model makes it easy to represent complex structures cleanly. Instead of packing everything into one file or JSON string, you can use multiple related tables with clear keys and constraints, which also aligns nicely with Room’s entity and DAO concepts.

Limitations and Considerations

Despite its power, SQLite has limits that matter on Android. It is a local database. It does not sync by itself with a server or other devices. If your app needs cloud sync, you still need a backend or another service and must design your own sync logic.

SQLite is not a full client server database. It works in a single process and uses file locking to control access. On Android, you usually access it from one app process at a time. You must be careful not to perform heavy database operations on the main thread because they can block the user interface.

The type system is flexible, but that also means SQLite does not enforce strict schemas as strongly as some other databases. Room adds extra checks on top to protect you from some mistakes, but understanding SQLite’s flexibility is helpful when you debug.

Finally, working directly with SQL can become verbose and error prone as your schema grows. This is exactly why libraries like Room exist. Room reduces boilerplate, helps manage migrations, and gives you compile time checks on SQL queries.

How Room Builds on SQLite

Room is a higher level library that sits on top of SQLite. It does not replace SQLite. Instead, it simplifies how you define tables, queries, and migrations. When you define a Room entity, Room generates the CREATE TABLE statement for SQLite. When you write a DAO method, Room generates the SELECT, INSERT, UPDATE, or DELETE SQL behind the scenes.

Because of this relationship, everything you do with Room is ultimately limited by what SQLite can do. Room stores data in SQLite tables, uses SQLite transactions, and returns results based on SQLite queries.

Understanding SQLite’s core ideas tables, primary keys, data types, relationships, indexes, and transactions will make the Room specific features much easier to understand in the upcoming chapters.