Create A SQLite Database In Android With SQLiteOpenHelper

SQLite is a lightweight relational database included in Android, making it useful for storing structured information directly on a device. It works well for notes, shopping lists, local settings, cached records, and small business apps that need data available without a constant internet connection.

The SQLiteOpenHelper class provides a controlled way to create, open, and upgrade a database. Instead of writing database setup code throughout an activity, you place schema creation and migration logic in one reusable helper class.

This approach is particularly useful in Australia, where an app may need to work during a train trip through Melbourne, in areas with limited regional coverage, or while a customer is moving between shops in Sydney or Brisbane. A local database can keep essential records available when mobile connectivity is unreliable.

Understand The Database Structure

Before writing Kotlin code, define the database name, table name, columns, and relationships. For a simple shopping list application, each item can have an automatically generated ID, a product name, a quantity, and a completion flag.

SQLite uses tables and columns rather than Android view objects. The database stores values such as TEXT, INTEGER, REAL, and BLOB. Android commonly represents a Boolean value as an integer: 0 for false and 1 for true.

A primary key gives every row a unique identity. The _id column is especially useful when connecting records to older Android components such as CursorAdapter. Even when using RecyclerView, a stable ID makes updates and deletes easier to manage.

Build A Reusable SQLiteOpenHelper

Create a Kotlin class that extends SQLiteOpenHelper. The constructor receives a context, database name, optional cursor factory, and schema version. The onCreate() method runs when the database is created for the first time, while onUpgrade() runs when the version number increases.

class ShoppingDatabaseHelper(context: Context) :
    SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(CREATE_ITEMS_TABLE)
    }

    override fun onUpgrade(
        db: SQLiteDatabase,
        oldVersion: Int,
        newVersion: Int
    ) {
        db.execSQL("DROP TABLE IF EXISTS $TABLE_ITEMS")
        onCreate(db)
    }

    companion object {
        private const val DATABASE_NAME = "shopping_list.db"
        private const val DATABASE_VERSION = 1

        const val TABLE_ITEMS = "items"
        const val COLUMN_ID = "_id"
        const val COLUMN_NAME = "name"
        const val COLUMN_QUANTITY = "quantity"
        const val COLUMN_DONE = "done"

        private const val CREATE_ITEMS_TABLE = """
            CREATE TABLE $TABLE_ITEMS (
                $COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                $COLUMN_NAME TEXT NOT NULL,
                $COLUMN_QUANTITY INTEGER NOT NULL DEFAULT 1,
                $COLUMN_DONE INTEGER NOT NULL DEFAULT 0
            )
        """
    }
}

The database file is private to the application by default. Android creates it when getWritableDatabase() or getReadableDatabase() is first called. Keep the helper instance for the lifetime of a screen or repository rather than repeatedly constructing new objects.

Dropping a table in onUpgrade() is acceptable for an early tutorial, but it deletes existing records. A released application should use migration statements that preserve customer data.

Insert Read And Delete Records

Use ContentValues to map Kotlin values to database columns. The insert() method returns the new row ID, or -1 if insertion fails. Values should be supplied through ContentValues rather than concatenated into SQL strings.

fun addItem(name: String, quantity: Int): Long {
    val values = ContentValues().apply {
        put(ShoppingDatabaseHelper.COLUMN_NAME, name)
        put(ShoppingDatabaseHelper.COLUMN_QUANTITY, quantity)
        put(ShoppingDatabaseHelper.COLUMN_DONE, 0)
    }

    return writableDatabase.insert(
        ShoppingDatabaseHelper.TABLE_ITEMS,
        null,
        values
    )
}

A query returns a Cursor, which must be closed after use. The use extension function closes it automatically. A selection argument prevents SQL injection and correctly handles quoted text entered by users.

fun readItems(): List<ShoppingItem> {
    val items = mutableListOf<ShoppingItem>()

    readableDatabase.query(
        ShoppingDatabaseHelper.TABLE_ITEMS,
        null,
        null,
        null,
        null,
        null,
        "${ShoppingDatabaseHelper.COLUMN_NAME} COLLATE NOCASE ASC"
    ).use { cursor ->
        val idIndex = cursor.getColumnIndexOrThrow("_id")
        val nameIndex = cursor.getColumnIndexOrThrow("name")
        val quantityIndex = cursor.getColumnIndexOrThrow("quantity")
        val doneIndex = cursor.getColumnIndexOrThrow("done")

        while (cursor.moveToNext()) {
            items += ShoppingItem(
                id = cursor.getLong(idIndex),
                name = cursor.getString(nameIndex),
                quantity = cursor.getInt(quantityIndex),
                done = cursor.getInt(doneIndex) == 1
            )
        }
    }

    return items
}

Database work should run away from the main thread. For a modern Android project, use Kotlin coroutines, a repository, and preferably Room when an application has complex relationships or extensive testing requirements. SQLiteOpenHelper remains valuable for learning SQL fundamentals and for small, direct storage tasks.

Handle Upgrades And Protect User Data

The version number controls schema upgrades. If version 2 adds a category column, increase DATABASE_VERSION and use ALTER TABLE rather than deleting the table.

override fun onUpgrade(
    db: SQLiteDatabase,
    oldVersion: Int,
    newVersion: Int
) {
    if (oldVersion < 2) {
        db.execSQL(
            "ALTER TABLE items ADD COLUMN category TEXT NOT NULL DEFAULT 'General'"
        )
    }
}

Migration code should be incremental so that a user moving from version 1 to version 3 receives every required change in order. Test upgrades using a copy of a real database containing records, including empty strings, large quantities, and completed items.

Australian applications should also consider privacy from the beginning. The Privacy Act 1988 and the Australian Privacy Principles may apply when an app handles personal information, especially if shopping records, contact details, location data, or customer identifiers are collected. A local SQLite file is still sensitive data and should not be treated as automatically safe.

Avoid storing passwords, payment card numbers, or government identifiers in an ordinary SQLite table. Use Android Keystore-backed encryption or an established encrypted storage solution when the data requires stronger protection. Provide a clear deletion path if users need to remove their local information.

Test The Database In A Real App

A database helper should be tested independently from an activity. Android instrumentation tests can create an in-memory or temporary database, insert rows, query them, update a record, and verify that deletion behaves as expected.

Check common Australian usage conditions as well. Test with Australian English product names, prices represented in cents, daylight-saving transitions relevant to Sydney, Melbourne, Hobart, and Canberra, and offline use during travel between regional towns. SQLite stores no currency formatting automatically, so keep monetary values as integer cents rather than floating-point dollars.

The following summary helps match each helper operation with its purpose:

A practical implementation checklist includes:

• Define table and column constants instead of scattering raw strings through activities.
• Use ContentValues and selection arguments for database writes and queries.
• Increase the database version whenever the schema changes.
• Write migration code that preserves existing user records.
• Run queries away from the main UI thread.
• Close cursors and database resources when their work is complete.
• Consider Room or encrypted storage as the project grows.

With these practices, SQLiteOpenHelper gives an Android application a clear local persistence layer. It supports reliable offline behaviour, keeps SQL operations organised, and provides a strong foundation for features such as ListViews, RecyclerViews, search screens, and saved user preferences.