SQLite in Android: Architecture, Benefits, Limitations, and a Practical Student Database Example
I am an IT student with a strong passion and proficiency in programming. I have solid experience working with.
Introduction
In the realm of mobile application development, managing data efficiently is crucial. While cloud-based solutions are popular, there is a strong need for a reliable, offline-first database that runs directly on the device. Enter SQLite.
This article explores SQLite, the embedded relational database management system (RDBMS) at the heart of Android's data persistence strategy. We will break down its architecture, advantages, limitations, and how Android bridges the gap between C-based SQLite and Java/Kotlin applications.
What Is SQLite?
SQLite is an embedded, relational database management system (RDBMS). Unlike traditional databases such as MySQL or PostgreSQL that run as separate server processes, SQLite is provided in the form of a library linked directly into your application.
Key Characteristics
| Characteristic | Description |
|---|---|
| Serverless | No standalone database server. All operations are handled internally via functions from the SQLite library. |
| File-Based | Reads and writes directly to ordinary disk files. |
| Single File | A complete database with multiple tables, indices, triggers, and views is contained in one disk file. |
| Cross-Platform | Database files can be freely copied between 32-bit and 64-bit systems. |
| Zero Configuration | No installation or setup required. |
Figure 1: A database can contain multiple tables, each designed for a specific type of information.
Understanding Database Tables: Rows and Columns
It is helpful to view a database table as being similar to a spreadsheet such as Microsoft Excel:
Columns represent data fields in the corresponding table.
Rows, also called records or entries, represent information. Each new record saved to a table is stored in a row.
Each table has a name that must be unique within that particular database.
Database Schema
The database schema defines the characteristics of the data stored in a database table. It is also used to define:
The structure of entire databases.
The relationships between various tables contained in a database.
Primary Key
A database table must contain one column that can be used to uniquely identify each row in the table. This is called the primary key.
Remember: An index is used to query data faster, speed up sort operations, and enforce unique constraints.
Figure 2: Columns represent data fields. Rows represent records or entries.
What Is a Relational Database System?
A Relational Database System (RDS) contains one or more objects called tables. The data or information for the database are stored in these tables.
Popular Relational Database Management Systems
Many companies use a database management system that has SQL already built into it. Common RDBMS that use SQL include:
| RDBMS | Vendor |
|---|---|
| Oracle | Oracle Corporation |
| Sybase | SAP |
| Microsoft SQL Server | Microsoft |
| Microsoft Access | Microsoft |
| MySQL | Oracle (originally MySQL AB) |
| SQLite | Public Domain |
Figure 3: A Relational Database System contains one or more objects called tables.
Figure 4: Common Relational Database Management Systems that use SQL.
Key takeaway: For all of these there is SQL (Structured Query Language).
What Is SQL?
SQL stands for Structured Query Language.
SQL is used to communicate with a database. It is a programming language that is used to communicate with and manipulate databases.
History of SQL
The SQL programming language was first developed in the 1970s by IBM researchers Raymond Boyce and Donald Chamberlin. The programming language, known then as SEQUEL, was created following the publishing of Edgar Frank Codd's paper, "A Relational Model of Data for Large Shared Data Banks," in 1970.
SQL Standard
According to ANSI (American National Standards Institute), SQL is the standard language for relational database management systems.
What Can SQL Do?
SQL statements are used to perform tasks such as:
Update data in a database.
Retrieve data from a database.
Query the database.
MySQL
Developed and distributed by Oracle, MySQL is one of the most popular SQL database management systems currently available. The software is an open-source version, which means it can be downloaded and used for free.
According to the web hosting service GoDaddy, MySQL is a sophisticated and powerful relational database management system used by many websites to create and change content quickly.
SQLite in Android
SQLite is an embedded, relational database management system with these key characteristics:
| Feature | Description |
|---|---|
| Embedded | Provided in the form of a library, linked into applications. |
| No Server | No standalone database server process. |
| Internal Operations | All database operations are handled internally using functions from the SQLite library. |
| File-Based I/O | Reads and writes directly to ordinary disk files. |
| Single File Database | Complete SQL database with multiple tables contained in a single disk file. |
| Cross-Platform | Database file format is cross-platform and can be freely copied between 32-bit and 64-bit systems. |
The Language Gap
SQLite is written in C language, but Android apps are written in Java, and now Kotlin.
So how do they work together?
To bridge this language gap, the Android SDK includes a set of classes that provide a Java layer on top of the SQLite database management system.
Indexes in SQLite
SQLite supports different types of indexes:
| Index Type | Purpose |
|---|---|
| Unique Indexes | Used for performance and data integrity. Does not allow duplicate values. |
| Composite Index | An index on two or more columns of a table. |
Best practice: Indexes should not be used in small tables or tables with columns that contain a high number of
NULLvalues.
Android SQLite Java Classes
The Cursor Class
The Cursor class exposes results from a query on a SQLiteDatabase.
Class hierarchy:
java.lang.Object
-> android.database.AbstractCursor
-> android.database.AbstractWindowedCursor
-> android.database.sqlite.SQLiteCursor
Important Cursor methods:
| Method | Description |
|---|---|
close() |
Releases all resources used by the cursor and closes it. |
getCount() |
Returns the number of rows/records within the result set. |
moveToFirst() |
Moves to the first row/record within the result set. |
moveToLast() |
Moves to the last row/record in the result set. |
moveToNext() |
Moves to the next row/record in the result set. |
move(offset) |
Moves by a specified offset from the current position. |
getString() |
Returns String value at specified column index. |
getInt() |
Returns Integer value at specified column index. |
getShort() |
Returns Short value at specified column index. |
getFloat() |
Returns Float value at specified column index. |
getDouble() |
Returns Double value at specified column index. |
SQLiteDatabase Class
| Method | Description |
|---|---|
insert() |
Inserts a new row into a database table. |
delete() |
Deletes rows from a database table. |
query() |
Performs a database query and returns results via a Cursor object. |
execSQL() |
Executes a single SQL statement that does not return result data. |
rawQuery() |
Executes an SQL query and returns results in a Cursor object. |
SQLiteOpenHelper Class
| Method | Description |
|---|---|
onCreate() |
Called when the database is created for the first time. |
onUpgrade() |
Called when app code contains a more recent database version number. |
getWritableDatabase() |
Opens or creates a database for reading and writing. Returns a SQLiteDatabase object. |
getReadableDatabase() |
Creates or opens a database for reading only. Returns a SQLiteDatabase object. |
close() |
Closes the database. |
More methods are available at developer.android.com/reference/android/database/sqlite/.
SQLite Advantages for Mobile Development
| # | Advantage | Description |
|---|---|---|
| 1 | Free and Public Domain | Free for any purpose, commercial or private. |
| 2 | No Separate Server | Reads and writes directly to ordinary disk files. |
| 3 | Single File | Complete database with multiple tables, indices, triggers, and views in one file. |
| 4 | Full Offline Mode | Apps that depend on stored data work without internet. |
| 5 | Stable Performance | Predictable performance independent from network availability. |
| 6 | Data Privacy (Safe) | Personal data stored only on your device. |
| 7 | Custom Schema | Developers can define exactly the data schema they want. |
| 8 | Full Control | Handwritten SQL queries give complete control. |
| 9 | Powerful Query Language | SQL supports most standard features. |
| 10 | Debuggable | Developers can grab the database file and analyze it. |
| 11 | Rock-Solid | Widely used technology established since year 2000. |
| 12 | No Setup Required | No need to set up a database server and interface. |
| 13 | Secure by Default | Android stores the database in the app's private folder, inaccessible to other apps or users. |
| 14 | Native Support | Android has built-in native support for SQLite. |
SQLite Disadvantages for Mobile Development
| # | Disadvantage | Description |
|---|---|---|
| 1 | Lot of Boilerplate Code | Much code is required, leading to inefficiencies in long-term maintenance. |
| 2 | No Compile-Time Checks | SQL queries are strings, so errors can cause runtime crashes. |
| 3 | Unreliable Performance | Different devices produce different performance results. |
| 4 | Another Language to Master | SQL is an additional language developers must learn. |
| 5 | Complex Queries | SQL queries can get long and complicated. |
| 6 | Slower Performance | SQLite must access the file system, which is slower than memory. |
| 7 | Limited Database Size | Only for local and small databases, with read/query under 5 ms. |
| 8 | Storage Limitations | Mobile device storage constraints prevent huge databases. |
Recommendation: If you need a database for a huge amount of data, better use a remote DB on an external server.
ORM and Realm
An ORM (Object-Relational Mapper) keeps SQLite abstraction by letting database entities map to Java objects more easily.
Realm (Introduced June 2014)
Realm Mobile Database is a database designed specifically for mobile devices from the ground up.
| Feature | Description |
|---|---|
| Not an ORM | Not an abstraction built on top of SQLite. |
| New Engine | A whole new database engine was created. |
| Object Store | Based on an object store rather than a relational model. |
| C++ Core | Core consists of a self-contained C++ library. |
| Platform Support | Android, iOS (Objective-C and Swift), Xamarin, and React Native. |
Learn more at realm.io.
Android Layout Fundamentals
When building the UI for your database app, understanding layout parameters is essential.
| Concept | Meaning |
|---|---|
match_parent |
Makes the view expand to as much space as possible horizontally and vertically within the parent view. The view will be as big as its parent. In general, parent_view is not the same size as a mobile display. |
wrap_content |
Takes only enough space for its contents to get enclosed. It does not waste space. |
android:orientation |
Specifies the layout direction. Values: vertical stacks items top-to-bottom, while horizontal arranges items side-by-side. |
| Color | Hex Code | RGB Breakdown |
|---|---|---|
| White | #FFFFFF |
33% Red + 33% Green + 33% Blue |
| Black | #000000 |
0% Red + 0% Green + 0% Blue |
android:background="#ffffff" sets a white background.
Figure 5: android:orientation="vertical" specifies the layout direction by stacking items top-to-bottom.
Figure 6: match_parent expands fully, while wrap_content shrinks to fit content only.
Practical Application: Student Database Example
Goal
An Android app for managing a sample student database (
EnrollNumber,StudentName, andStudentMarks). The user's operations will be: Add, Modify, Delete, Search, and ViewAll records.
Database Schema
| Database Element | Value |
|---|---|
| Database Name | StudentDB (or BookDB in examples) |
| Table Name | Students (or Books in examples) |
| Columns | ID, EnrollNumber, StudentName, StudentMarks |
Column data types:
| Column | Data Type | Description |
|---|---|---|
id |
INTEGER PRIMARY KEY AUTOINCREMENT |
Unique identifier for each record. |
enroll_number |
TEXT or INTEGER |
Student's enrollment number. |
student_name |
TEXT |
Student's full name. |
student_marks |
REAL or INTEGER |
Student's marks or grades. |
Figure 7: The name and structure or columns of the database table.
Required Imports for Database Operations
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
Creating the Database
The function used to create the database is:
db.execSQL(CREATE_BOOK_TABLE);
Where CREATE_BOOK_TABLE is a String:
String CREATE_BOOK_TABLE = "CREATE TABLE books (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT," +
"title TEXT," +
"author TEXT)";
Note: This is the first operation needed if no database exists.
Sample UI Layout (XML)
<TextView
android:text="Student Data"
android:layout_x="110dp"
android:layout_y="10dp"
android:layout_width="wrap_content"
android:layout_height="wrap_content" />
<TextView
android:text="Enroll No.: "
android:layout_x="30dp"
android:layout_y="48dp"
android:layout_width="wrap_content"
android:layout_height="wrap_content" />
<EditText
android:id="@+id/editEnrollno"
android:layout_x="155dp"
android:layout_y="33dp"
android:layout_width="150dp"
android:layout_height="50dp" />
<TextView
android:text="Name: "
android:layout_x="30dp"
android:layout_y="100dp"
android:layout_width="wrap_content"
android:layout_height="wrap_content" />
<EditText
android:id="@+id/editName"
android:inputType="text"
android:layout_x="155dp"
android:layout_y="81dp"
android:layout_width="150dp"
android:layout_height="50dp" />
<TextView
android:text="Marks: "
android:layout_x="30dp"
android:layout_y="150dp"
android:layout_width="wrap_content"
android:layout_height="wrap_content" />
<EditText
android:id="@+id/editMarks"
android:inputType="numberDecimal"
android:layout_x="155dp"
android:layout_y="128dp"
android:layout_width="150dp"
android:layout_height="50dp" />
<Button
android:id="@+id/btnAdd"
android:text="Add"
android:layout_x="30dp"
android:layout_y="200dp"
android:layout_width="100dp"
android:layout_height="40dp" />
<Button
android:id="@+id/btnDelete"
android:text="Delete"
android:layout_x="250dp"
android:layout_y="200dp"
android:layout_width="100dp"
android:layout_height="40dp" />
<Button
android:id="@+id/btnModify"
android:text="Modify"
android:layout_x="140dp"
android:layout_y="200dp"
android:layout_width="100dp"
android:layout_height="40dp" />
<Button
android:id="@+id/btnSearch"
android:text="Search"
android:layout_x="85dp"
android:layout_y="250dp"
android:layout_width="100dp"
android:layout_height="40dp" />
<Button
android:id="@+id/btnViewAll"
android:text="View All"
android:layout_x="195dp"
android:layout_y="250dp"
android:layout_width="100dp"
android:layout_height="40dp" />
Strings.xml Resource Values
| Resource | Value |
|---|---|
| App name | Student Database App |
| Heading | Student Data |
| Label | Enroll No.: |
| Label | Name: |
| Label | Marks: |
| Button | Add |
| Button | Delete |
| Button | Modify |
| Button | Search |
| Button | View All |
Complete Code Example Structure
public class MainActivity extends AppCompatActivity {
// Database Helper Class
public class DatabaseHelper extends SQLiteOpenHelper {
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_TABLE = "CREATE TABLE students (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT," +
"enroll_no TEXT," +
"name TEXT," +
"marks TEXT)";
db.execSQL(CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS students");
onCreate(db);
}
// CRUD Operations
public boolean addStudent(String enrollNo, String name, String marks) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("enroll_no", enrollNo);
values.put("name", name);
values.put("marks", marks);
long result = db.insert("students", null, values);
return result != -1;
}
public Cursor getAllStudents() {
SQLiteDatabase db = this.getReadableDatabase();
return db.rawQuery("SELECT * FROM students", null);
}
public Cursor searchStudent(String enrollNo) {
SQLiteDatabase db = this.getReadableDatabase();
return db.rawQuery("SELECT * FROM students WHERE enroll_no = ?",
new String[]{enrollNo});
}
public boolean updateStudent(String enrollNo, String name, String marks) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", name);
values.put("marks", marks);
int result = db.update("students", values, "enroll_no = ?",
new String[]{enrollNo});
return result > 0;
}
public boolean deleteStudent(String enrollNo) {
SQLiteDatabase db = this.getWritableDatabase();
int result = db.delete("students", "enroll_no = ?",
new String[]{enrollNo});
return result > 0;
}
}
}
Conclusion
SQLite remains a critical skill for Android developers. Despite its verbosity compared to modern ORMs, understanding SQLite means understanding the fundamentals of data persistence on constrained devices. It offers stability, full control, and security that external databases cannot guarantee when the device is offline.
Key Takeaways
| Concept | Key Point |
|---|---|
| SQLite | Embedded, serverless, file-based RDBMS |
| Language Bridge | Android SDK provides a Java layer over C-based SQLite |
| Main Classes | SQLiteOpenHelper, SQLiteDatabase, Cursor |
| Best For | Offline-first, local, small-to-medium data apps |
| Watch Out For | Boilerplate code and no compile-time SQL checks |
Future Work
To improve upon the standard SQLite implementation, developers should consider:
Implementing
RecyclerViewto displayCursordata efficiently.Using Room Persistence Library, Google's official abstraction over SQLite, to reduce boilerplate and add compile-time SQL verification.
Encrypting the database using SQLCipher for sensitive data storage.
Migrating to modern alternatives like Realm for object-based storage.
References
Android Developers Documentation:
android.database.sqliteRealm Mobile Database:
https://realm.io/SQLite Official Documentation:
https://www.sqlite.org/
