Android SQLiteOpenHelper: local SQLite CRUD guide
This tutorial explains how Android apps can persist structured data locally with SQLiteOpenHelper. The example is a shopping-list app, but the same pattern applies to notes, cached catalogs, offline forms and other small datasets that belong to the device rather than to a remote backend.
What this example covers
- Creating and opening a local SQLite database from Android code.
- Separating readable access from writable access with
getReadableDatabase()andgetWritableDatabase(). - Implementing create, read, update and delete operations in a small app model.
- Understanding when a local database is useful and when a backend API is a better option.
If you are reviewing the whole Android/iOS series, this article connects with the first Android steps and the iOS local storage guide.
1.Objectives
The objectives of this exercise are for the reader to learn to:- Be able to design a mobile application.
- Know how to implement various functionalities in an Android application:
- Data persistence.
- File and folder management.
- Integration of multimedia API functionalities.
- Application menu.
- Capture of events.
- Notification management.
Exercise
Github: The objective of this exercise is to learn how data persistence works using Android's own SQL API. A project has been created on GitHub to carry out this exercise. You can download it by following the steps in exercise 1 of PEC 2 and using the following URL: GitHub link This app consists of emulating a shopping list and showing the products in a list. As you will see, the visualization and user interaction part is already done. Tasks to perform:- Look in the official Android documentation where data persistence using SQL tools is explained. Share the link. Storage Option Official Documentation
- Read the documentation and answer the following questions:
- What is the SQLiteOpenHelper class for? The SQLiteOpenHelper class is an abstract class used to manage databases. This class allows us to implement a creation, opening and updating pattern and its purpose is to abstract from the logic underlying the decision to create or update a database before it must be opened.
- What methods are necessary to add information, read it, delete it and update it? Write information getWritableDatabase() and to read it getReadableDatabase(). That with both methods we can access the database and be able to perform both reads and writes.
- Once we are familiar with the documentation, take a look at how the project is structured. The tasks to do within the application are to complete the methods that you will find in the ShoppingItemDB.java class. Each method has a TODO label where it explains the tasks that have to be performed (you can search for them by going to the lower left tab of Android Studio, next to the Android tab). https://github.com/al118345/Android1-PEC2-ShoppingList
When SQLiteOpenHelper is a good fit
SQLiteOpenHelper is useful when the data is relational, small enough to live on the device and needs to remain available offline. It is less appropriate for collaborative data, server-authoritative information or datasets that require synchronization rules between many users.
In modern Android projects you may also evaluate Room, which adds a higher-level abstraction over SQLite. Even so, learning SQLiteOpenHelper helps you understand what Room is doing underneath: opening the database, creating tables, applying migrations and executing SQL statements safely.
Practical CRUD checklist
- Define table names and column constants in one place to avoid string mistakes.
- Use parameterized SQL or helper methods instead of concatenating user input.
- Close cursors and database resources when the operation has finished.
- Keep schema upgrades explicit in
onUpgrade()so existing users do not lose data unexpectedly.
How to think about schema upgrades
The part that usually creates problems is not the first version of the database, but the second or third version after real users already have data on the device. onUpgrade() should be treated as a migration plan: add columns carefully, copy data when a table changes shape and test the upgrade from every version that still exists in production.
For a shopping-list app, a future version might add categories, quantities or synchronization status. Those fields sound small, but each change has to preserve existing items. A destructive migration is acceptable in a classroom exercise; it is rarely acceptable in a personal app where the list belongs to the user.
SQLiteOpenHelper vs Room
Room is usually the better choice for modern Android apps because it adds compile-time checks, DAO classes and a cleaner migration API. SQLiteOpenHelper is still valuable for learning because it exposes the lower-level database lifecycle. Understanding it makes Room less magical and helps debug performance or migration problems later.