How to create a table in SQLite Android: a step-by-step guide

Development of mobile applications for the platform Android almost always requires local data storage. Whether it's a news cache, a list of user tasks, or profile settings, you can't do without a reliable database. The standard solution in the ecosystem is a lightweight relational DBMS built directly into the operating system. The process of creating SQLite on Android has undergone a significant evolution. If earlier developers had to write cumbersome SQL queries manually and manage schema versions via SQLite, a lightweight relational DBMS built directly into the operating system.

Creation process tables SQLite on Android has undergone significant evolution. Previously, developers had to write cumbersome SQL queries manually and manage schema versions via onUpgrade, today the library Roomdominates. It provides an abstraction layer on top of SQLite, allowing you to work with the database as a collection of Java or Kotlin objects. This reduces the likelihood of errors and speeds up development.

In this guide we will look at both approaches: modern through Room annotations and classic through SQLiteOpenHelper. You will learn how to correctly define data types, set primary keys, and organize relationships between entities. Understanding these principles is critical to creating stable and productive applications.

Data storage architecture and choice of approach

Before writing code, you need to choose an architectural pattern for working with data. Direct use of SQL queries via SQLiteDatabase gives maximum control, but requires careful handling of compilation errors at runtime. The library Room, included in Android Jetpack, solves this problem by checking queries at the project compilation stage.

Using Room involves dividing the logic into three main components: Entity (data model), DAO (data access object) and Database (base container). This approach follows the principles of clean architecture and makes testing easier. However, for very simple cases or teaching the basics of SQL, the classical approach also has a right to life.

The choice of tool depends on the complexity of your project. If you just need to save a couple of settings, then SharedPreferencesmay be enough. But for structured data with one-to-many relationships, creating a complete tables table is a prerequisite.

Creating an Entity in the Room library

In the Room ecosystem, a table is created automatically on based on the class marked with the annotation @Entity. This class is a regular data class in Kotlin or a POJO in Java. The default table name is the same as the class name, but it can be changed through the annotation parameters.

Each class field corresponds to a column in the table. To specify the primary key (Primary Key), the annotation @PrimaryKeyis used. Often, auto-increment mode is enabled for identifiers by setting the parameter autoGenerate = true. This saves the developer from having to manually generate unique IDs for each new record.

@Entity(tableName = "users")
data class User(
@PrimaryKey(autoGenerate = true) val id: Int = 0,
val firstName: String,
val lastName: String,
val age: Int,
val email: String?
)

Pay attention to the data type String? for the email field. SQLite supports NULL values ​​natively, and Room correctly maps Kotlin nullable types to NULL in the database. If a field is marked as non-cancellable (without a question mark), attempting to store null will throw an exception.

Configuring the schema via SQLiteOpenHelper (Classic method)

For those who want to understand the internals or cannot use Room, there is a class SQLiteOpenHelper. The table creation here occurs by executing a raw SQL query inside the onCreatemethod. This method is called only once - when the database file is first created.

The query syntax CREATE TABLE must be perfect. An error in one letter will cause the application to crash the first time it is launched. It is important to explicitly indicate data types: INTEGER for numbers, TEXT for strings, REAL for floating point numbers.

override fun onCreate(db: SQLiteDatabase) {
val createTable = "CREATE TABLE IF NOT EXISTS products (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"name TEXT NOT NULL, " +
"price REAL, " +
"category_id INTEGER)"
db.execSQL(createTable)
}

In this example, we create a table products. The keyword IF NOT EXISTS insures against errors if the method is called repeatedly, although in a normal situation onCreate it is triggered once. The field name is marked as NOT NULL, which guarantees data integrity at the database level.

⚠️ Attention: When using SQLiteOpenHelper any change in the table structure (adding a column, changing a type) requires increasing the database version and writing migration logic in the method onUpgrade. Otherwise, the application will crash with an error when starting.

Implementing DAO and working with queries

After defining the table structure, you need to create an interface DAO (Data Access Object). In Room this is an interface annotated as @Dao, containing methods for CRUD operations (Create, Read, Update, Delete). Room generates an implementation of this interface automatically.

DAO methods can return different types of data: a single object, a list of objects, LiveData or Flow to reactively update the interface when data changes. Annotations @Insert, @Update, @Delete and @Query cover most usage scenarios.

• 📌 @Insert — adds a new record to the table, returning the ID of the inserted row.
• 📌 @Query — allows you to write arbitrary SQL queries to select data with conditions.
• 📌 @Delete — deletes an object corresponding to the passed entity.

An example method for getting all users of a certain age is as follows:

@Dao
interface UserDao {
@Query("SELECT * FROM users WHERE age > :minAge")
fun getUsersOlderThan(minAge: Int): List

@Insert
suspend fun insertAll(vararg users: User)
}

The use of the keyword suspend indicates that the operation is performed in a coroutine, which prevents the main interface thread from blocking. This is critical to the responsiveness of the application.

Database initialization and version control

The final step is to create a database instance via Room.databaseBuilder (or SQLiteOpenHelper.getWritableDatabase() in the classic approach). This object is the entry point for retrieving the DAO and performing transactions.

During initialization, it is important to configure the behavior when the schema changes. In Room, by default the database is destroyed and recreated whenever there is any version mismatch, resulting in loss of user data. For production, you must configure migrations (Migrations).

Component	Purpose	Annotation / Class
Entity	Describes the structure of the table and fields	@Entity
DAO	Contains data access methods	@Dao
Database	Main access class, stores configuration	@Database
TypeConverter	Converts complex types for storage	@TypeConverter

If you use complex data types such as Date or lists that SQLite does not directly support need to be implemented TypeConverter. It converts the object to a string or number before saving and restores it when read.

⚠️ Attention: Never call database methods on the main thread (UI Thread). This will cause an exception MainThreadException in the debug build and will cause the interface to freeze in the release version. Always use coroutines, RxJava or Executors.

What is WAL mode?

Write-Ahead Logging (WAL) mode allows readers not to block writers and vice versa. In Room, it is enabled by default in the latest versions, which significantly improves performance when writing data frequently.

Typical errors and performance optimization

One ​​of the most common mistakes newbies make is creating many small transactions. If you insert 1000 records in a loop, calling insert for each, the database will sync to disk 1000 times. This is catastrophically slow.

The solution is to combine operations into one transaction. There is an annotation for this in Room @Transaction. It guarantees that all operations within the method are performed atomically: either all are successful, or none will be applied in case of an error.

@Transaction
@Insert
suspend fun insertUsersInBatch(users: List) {
// Inserting the entire list in one operation
insertAll(*users.toTypedArray())
}

It is also worth paying attention to indexes. If you frequently make queries on a specific field (for example, searching for a user by email), creating an index for this field will speed up the query significantly. In Room, this is done through the parameter indices in the annotation @Entity.

⚠️ Attention: Deleting a database through the app context (context.deleteDatabase("name")) requires caution. Make sure that all connections to the database are closed before deleting the file, otherwise the operation may fail on some versions of Android.

Frequently asked questions (FAQ)

Is it possible to change the data type of a column after creating a table?

In SQLite, you cannot directly change the data type of a column. You need to create a new table with the correct schema, copy the data from the old table, delete the old one, and rename the new one. In Room, this process is automated through the object Migration.

Where is the database file physically stored on the device?

By default, the database is saved in the private directory of the application along the path: /data/data/your.package.name/databases/data_i="150">How to view the contents of the database during development?. This folder can only be accessed with root access or via USB debugging on an emulator/debug build.

How to view the contents of a database during development?

In Android Studio there is tool App Inspection (or Database Inspector in older versions). It allows you to connect to a running application, view tables, run SQL queries and edit data in real time.

What to do if the application crashes with the error "table has no column named"?

This error means that the application code expects a column that is not in the database file on the device. This usually happens after adding a new field to the Entity without increasing the database version and setting up migration. Solution: increase the database version and add migration, or remove the application and install again (for tests).

Does Room support relationships between tables (Foreign Keys)?

Yes, Room fully supports foreign keys. To do this, you need to enable the option foreignKeys = true in the annotation @Database and use the annotation @ForeignKey inside Entity to describe relationships between tables.