Integrating SQLite into Android Studio: a complete guide

Creating modern mobile applications almost always requires solving the problem of data storage. If information must be retained between app restarts or processed locally without constant access to the server, the developer must implement a reliable database management system. In the Android ecosystem, the de facto standard is SQLite a lightweight relational DBMS built directly into the operating system. Connecting this tool allows you to organize structured storage of user settings, cache, action history and any other content.

The process of integrating the database into the project Android Studio has undergone significant changes in recent years. If earlier developers wrote cumbersome code based on the class, today Google strongly recommends using the library. This abstraction on top of SQLite makes working with the database easier by ensuring queries are checked at compile time and reducing the likelihood of runtime errors. However, understanding how pure SQL works remains critical for deep optimization and debugging of complex queries. SQLiteOpenHelper, then today Google strongly recommends using the library Room. This abstraction on top of SQLite makes working with the database easier by ensuring queries are checked at compile time and reducing the likelihood of runtime errors. However, understanding how pure SQL works remains critical for deep optimization and debugging of complex queries.

In this article we will look in detail at how to properly set up a development environment for working with databases, connect the necessary dependencies and implement a basic data access layer. You'll learn the differences between using SQL directly and working through ORM frameworks, and get practical advice on application architecture. Regardless of whether you are creating a simple list of tasks or a complex financial system, proper organization of data storage will be the foundation for the stability of your product.

Preparing the project and connecting dependencies

The first step before starting to work with the database is to correctly configure the project build file. In modern Kotlin or Java development, using the Gradle build system is a mandatory standard. You need to open the file build.gradle (module level, usually app) and add the appropriate dependencies to work with Room or the direct SQLite driver. Without these libraries, the compiler simply will not see the necessary classes and annotations.

To include the Room library, which is the recommended way to work with SQLite in Android Studio, you need to add a few lines to the section dependencies. It is important to use current versions of libraries, as older versions may contain vulnerabilities or not support new features of the Kotlin language. Below is an example configuration that includes the Room library itself, an annotation compiler and additional utilities for working with coroutines.

dependencies {
def room_version = "2.5.2"
implementation "androidx.room:room-runtime:$room_version"
annotationProcessor "androidx.room:room-compiler:$room_version"
// For Kotlin, use kapt instead of annotationProcessor
kapt "androidx.room:room-compiler:$room_version"
implementation "androidx.room:room-ktx:$room_version"
}

After making changes to the build file, be sure to synchronize the project by clicking the button Sync Nowthat appears in top of the editor. This process will download all the required libraries from the Maven repository and prepare the project for compilation. If synchronization was successful, you will be able to import classes from the package androidx.room without errors. Otherwise, check your Internet connection and the correctness of the repositories specified in the file settings.gradle.

⚠️ Attention: When using Kotlin, make sure that the plugin kapt (Kotlin Annotation Processing Tool) is included in the file build.gradle project level. Without it, Room annotations will not be processed and the generated code will not appear, resulting in compilation errors.

Application architecture and components Room

The correct application architecture is the key to its support and scalability. The Room library offers a clear separation of responsibilities between three main components: Entity, DAO (Data Access Object) and Database. Understanding the role of each of these elements allows you to create code that is easy to read, test, and change in the future. Ignoring these principles often leads to the creation of “spaghetti code”, where the logic for working with data is mixed with the logic for displaying the interface.

Entity is a data model that is directly mapped to a table in a SQLite database. Each class, marked with an annotation @Entitydescribes the structure of the table, including column names, primary keys, and indexes. Class fields correspond to table columns, and Kotlin data types are automatically converted to compatible SQLite types. For example, a Type String becomes TEXTand a Int — INTEGER.

Component DAO is an interface or abstract class that contains methods for performing database operations. This is where you write SQL queries or use convenient annotations like @Insert, @Update and @Delete. Room generates an implementation of this interface at compile time by checking the syntax of your SQL queries. This allows you to catch errors in queries even before launching the application, which significantly saves time on debugging.

The third component - Database is an abstract class that extends RoomDatabase. It serves as the main entry point for accessing the database and holds a link to the DAO. Creating a database instance should happen once, usually using the Singleton pattern, to avoid the overhead of opening a connection. This class is also responsible for migrating the database schema when updating application versions.

Component	Annotation	Purpose	Type
Entity	@Entity	Description of table and columns	Data class / Class
DAO	@Dao	Data access methods (CRUD)	Interface
Database	@Database	Database holder and migration	Abstract Class
TypeConverter	@TypeConverter	Conversion of complex types	Class / Object

Creating an Entity and defining a schema

Let's start the practical implementation by creating an entity. Let's say we are developing an application for tracking tasks, and we need a table Task. In Kotlin, the most convenient way to describe such an entity is to use data class. This class automatically generates equals, hashCode and toStringmethods, making it easier to work with data objects. The annotation @Entity(tableName = "tasks") instructs Room that this class should be represented by a table named tasks.

Each table must have a Primary Key that uniquely identifies each record. In Room this is done using annotation @PrimaryKey. You can specify whether the key will be generated automatically by the database (auto-increment) or whether you will assign it manually. For most use cases, such as lists of users or products, it is more convenient to trust the ID generation to the database itself by setting the parameter autoGenerate = true.

@Entity(tableName = "tasks")
data class Task(
@PrimaryKey(autoGenerate = true) val id: Int = 0,
@ColumnInfo(name = "title") val title: String,
@ColumnInfo(name = "description") val description: String,
@ColumnInfo(name = "is_completed") val isCompleted: Boolean,
@ColumnInfo(name = "created_at") val createdAt: Long = System.currentTimeMillis()
)

Use annotation @ColumnInfo allows you to explicitly set the name of a column in the database that is different from the name of the property in the class. This is useful if you want to follow a certain SQL naming style (such as snake_case) or if the field name in your code conflicts with reserved words. In addition, you can set indexes to speed up searching for certain fields by adding a parameter indices to the annotation @Entity.

⚠️ Attention: Changing the entity structure (adding or removing fields) without corresponding database migration will crash the application at startup. Room strictly monitors the compliance of the schema and code versions.

What is database migration?

Migration is the process of updating the database structure from one version to another without losing user data. In Room, this is implemented through the Migration class, where you explicitly write ALTER TABLE SQL commands to change the schema. If migration is not specified, Room will require you to delete the database (destructive migration), which will result in the loss of all local data.

Implementing a data access layer (DAO)

After defining the data structure, you need to create a DAO interface that will manage read and write operations. This interface acts as an intermediary between the application logic and the physical database. All methods in a DAO must return data either directly or through wrappers that support asynchrony, such as Flow, LiveData or Suspend functions. The use of blocking operations in the main thread (UI Thread) is strictly prohibited, as this will cause the interface to freeze.

For basic insertion, update and delete operations, Room provides ready-made annotations: @Insert, @Update and @Delete. You don't have to write the SQL code for these actions manually; the library will generate it for you. However, to select data (SELECT), you will have to write a SQL query using the @Queryannotation. The advantage of Room is that it checks this query at the compilation stage: if you misspell the name of a column or table, the project simply will not build.

• 📝 @Insert: Adds new records to the table. Can accept a single object or a list of objects. Returns the inserted row ID or the number of inserted rows.
• 🔄 @Update: Updates existing records based on their primary key. Returns the number of rows updated.
• 🗑️ @Delete: Deletes records from the table. Also works based on the primary key of the passed objects.
• 🔍 @Query: Executes a custom SQL query to read data. Supports parameters passed through a colon (for example, :userId).

When working with asynchrony in Kotlin, it is recommended to use coroutines. Declaring DAO methods as suspend allows them to be called from coroutines without blocking the thread. This makes the code cleaner and more readable compared to using callbacks. Additionally, Room integrates well with Flow, allowing you to create data flows that automatically notify observers of changes in the database in real time.

@Dao
interface TaskDao {
@Query("SELECT * FROM tasks ORDER BY created_at DESC")
fun getAllTasks(): Flow>

@Query("SELECT * FROM tasks WHERE id = :taskId")
suspend fun getTaskById(taskId: Int): Task?

@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertTask(task: Task)

@Update
suspend fun updateTask(task: Task)

@Delete
suspend fun deleteTask(task: Task)
}

Initializing the database and working with migrations

The final stage of configuration is the creation of a database class that combines entities and DAO. This class must be abstract and inherit from RoomDatabase. In it you declare abstract methods for obtaining DAO instances. It also specifies the list of entities included in the database and the current version of the schema. The creation of a database instance is done through the Builder pattern, which allows you to configure various parameters, such as. as the name of the database file and the migration strategy.

An important aspect is versioning control. Every time you change the entity structure (adding a field, changing the data type), you must increase the version number in the annotation. @Database(version = N)If you do not provide a migration strategy for moving from version N to version N+1, the application will crash with an exception when trying to open the database. test applications you can use the strategy fallbackToDestructiveMigration(), which will simply delete the old database and create a new one, but this is unacceptable for production.

The database initialization process should occur in the context of the application, not the activity, to avoid memory leaks. Typically, a separate repository class is created for this or Dependency Injection is used (for example, Hilt or Koin). This provides a single point of database creation throughout the entire application lifecycle. Use allowMainThreadQueries() is allowed only in debug builds to simplify testing, but should never make it into the release version.

⚠️ Attention: Never store a database instance in a static variable inside an Activity or Fragment. This will lead to a memory leak since the database holds the connection to the file, and the garbage collector will not be able to free the activity context resources.

Testing and debugging the database in Android Studio

Developing functionality for working with data does not end with writing code; It is critical to ensure that queries work correctly and data is stored as expected. Android Studio provides powerful tools for debugging Room databases right during the development process. The App Inspection tab (formerly the Database Inspector) allows you to view the contents of tables, run arbitrary SQL queries, and even change data in real time while the application is running on an emulator or physical device.

To access the Database Inspector, run the application in debug mode, then go to the menu View → Tool Windows → App Inspection. Select your application process and tab Database Inspector. You will see a list of all connected databases. By expanding the database node, you will have access to the tables. Double-clicking on a table will open its contents in a grid where you can filter rows and edit cell values. This incredibly speeds up the search for errors in the data saving logic.

In addition to the visual inspector, it is recommended to write unit tests for the DAO. The Room library provides support for testing in the JUnit environment without the need to run an emulator. You can create an in-memory database (in-memory) that is created and destroyed for each test. This guarantees test isolation and high speed of execution. Testing SQL queries in the early stages helps to avoid situations where errors pop up only on user devices.

How to view a database file on a device without root access?

Starting with Android Studio 4.1, you can use Device File Explorer to access application data in a debug build. Go to View → Tool Windows → Device File Explorer, then navigated to path /data/data/your.package/databases/. From there you can download the .db file to your computer and open it with any SQLite client, for example DB Browser for SQLite.

Why doesn't Room see my annotations?

Most often the problem lies in incorrect configuration kapt or annotationProcessor in the build.gradle file. Make sure that the versions of the room-runtime and room-compiler libraries match. Also try running command Build → Clean Project and then Rebuild Projectto force code regeneration.

Can Room be used with Java?

Yes, Room fully supports Java. Instead data class usual classes with getters and setters (or libraries like Lombok) are used. The annotations and operating principles remain the same. However, using Kotlin provides significant advantages due to coroutines and more concise syntax.

How to perform a complex query with a table join (JOIN)?

Room supports complex SQL queries in annotation @Query. You can write standard SQL commands with JOIN, GROUP BY, HAVING and subqueries. The only limitation is that the query result must be mapped to an existing Entity or to a special class marked with an annotation @ColumnInfo to match the fields.