Kotlin Android SQLite – Example Application

Kotlin Android SQLite Tutorial for CRUD Operations

Kotlin Android SQLite is used when an Android app needs a small local relational database on the device. Android includes SQLite support through the android.database.sqlite package, and SQLiteOpenHelper helps you create, open, upgrade, and manage a database file for your app.

In this tutorial, we will build a simple user management screen and learn the basic SQLite operations in Kotlin: creating a table, inserting a row, reading rows, updating rows, and deleting rows. The example uses a classic XML layout and SQLiteOpenHelper, so it is useful for learning low-level Android SQLite as well as for maintaining older Android projects.

For new production apps with larger data models, also review Android’s official SQLite storage guide and Room database guide. Room is built on top of SQLite and provides a higher-level persistence API, while this tutorial focuses on direct SQLite CRUD code in Kotlin.

What this Kotlin Android SQLite example stores

The app stores user records in a local SQLite table named users. Each row has a user id, user name, and age. The screen lets you add a user, delete a user by id, and display all saved users.

SQLite item in this tutorial	Purpose in the Android app
users table	Stores one row for each user record.
userid column	Primary key used to identify a user.
name column	Stores the user name typed in the form.
age column	Stores the user age typed in the form.
UsersDBHelper	Contains the Kotlin methods that perform SQLite insert, read, update, and delete operations.

Kotlin Android SQLite project structure

Kotlin Android SQLite Example Application : In this Android Tutorial, we shall learn how to use SQLite database in your Android Application with an example using Kotlin Programming language.

The example is divided into small files so that the database schema, database operations, model class, and activity code are easy to understand.

DB Contract Class	Contains schema (table name and column names) for program understandability.
DB Helper Class	This class contains methods that do database operations like insert, select, update, delete, etc.
Model Data Class	Used to carry objects (rows of DB table)
Activity Class	This is class file of your Activity from which you call DB Helper’s methods for database activities

Create the Android project for the Kotlin SQLite example

Following are the details of the Android Application we created for this example.

Application Name	SQLiteTutorial
Company name	tutorialkart.com
Minimum SDK	API 21: Android 5.0 (Lollipop)
Activity	Empty Activity

You may keep rest of the values as default and create Android Application with Kotlin Support.

In newer Android Studio versions, choose an Empty Views Activity if you want to follow this XML-based example directly. If your project uses Jetpack Compose, the SQLite helper code can still be reused, but the UI layer will be different.

Build activity_main.xml for the Kotlin Android SQLite form

The layout contains three input fields for user id, name, and age. It also contains buttons for Add, Delete, and Show All. The result TextView displays the action status, and the vertical LinearLayout at the bottom is used to add TextView rows dynamically when users are fetched from SQLite.

activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="vertical"
    android:gravity="center"
    tools:context="com.tutorialkart.sqlitetutorial.MainActivity">

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="SQLite Tutorial - User Management"
        android:textSize="20dp"
        android:padding="10dp" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical">
        <EditText
            android:id="@+id/edittext_userid"
            android:hint="User ID"
            android:gravity="center"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />
        <EditText
            android:id="@+id/edittext_name"
            android:hint="User Name"
            android:gravity="center"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />
        <EditText
            android:id="@+id/edittext_age"
            android:hint="User Age"
            android:gravity="center"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <Button
            android:id="@+id/button_add_user"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:onClick="addUser"
            android:text="Add" />

        <Button
            android:id="@+id/button_delete_user"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:onClick="deleteUser"
            android:text="Delete" />

        <Button
            android:id="@+id/button_show_all"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:onClick="showAllUsers"
            android:text="Show All" />
    </LinearLayout>
    <TextView
        android:id="@+id/textview_result"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />
    <LinearLayout
        android:id="@+id/ll_entries"
        android:padding="15dp"
        android:orientation="vertical"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"></LinearLayout>
</LinearLayout>

Create UserModel.kt for SQLite rows in Kotlin

The model class represents one row in the users table. In this basic example, all fields are strings because the form receives values from EditText fields.

UserModel.kt

package com.tutorialkart.sqlitetutorial

class UserModel(val userid: String, val name: String, val age: String)

Define the SQLite table schema in DBContract.kt

A contract class keeps the table name and column names in one place. This avoids typing the same SQLite strings in several methods and reduces mistakes when you update the schema later.

DBContract.kt

package com.tutorialkart.sqlitetutorial

import android.provider.BaseColumns

object DBContract {

    /* Inner class that defines the table contents */
    class UserEntry : BaseColumns {
        companion object {
            val TABLE_NAME = "users"
            val COLUMN_USER_ID = "userid"
            val COLUMN_NAME = "name"
            val COLUMN_AGE = "age"
        }
    }
}

Create UsersDBHelper.kt for Kotlin Android SQLite CRUD

UsersDBHelper extends SQLiteOpenHelper. The onCreate() method creates the SQLite table, and the helper methods perform insert, delete, and read operations from the activity.

UsersDBHelper.kt

package com.tutorialkart.sqlitetutorial

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteConstraintException
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteException
import android.database.sqlite.SQLiteOpenHelper

import java.util.ArrayList

class UsersDBHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(SQL_CREATE_ENTRIES)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        db.execSQL(SQL_DELETE_ENTRIES)
        onCreate(db)
    }

    override fun onDowngrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        onUpgrade(db, oldVersion, newVersion)
    }

    @Throws(SQLiteConstraintException::class)
    fun insertUser(user: UserModel): Boolean {
        // Gets the data repository in write mode
        val db = writableDatabase

        // Create a new map of values, where column names are the keys
        val values = ContentValues()
        values.put(DBContract.UserEntry.COLUMN_USER_ID, user.userid)
        values.put(DBContract.UserEntry.COLUMN_NAME, user.name)
        values.put(DBContract.UserEntry.COLUMN_AGE, user.age)

        // Insert the new row, returning the primary key value of the new row
        val newRowId = db.insert(DBContract.UserEntry.TABLE_NAME, null, values)

        return true
    }

    @Throws(SQLiteConstraintException::class)
    fun deleteUser(userid: String): Boolean {
        // Gets the data repository in write mode
        val db = writableDatabase
        // Define 'where' part of query.
        val selection = DBContract.UserEntry.COLUMN_USER_ID + " LIKE ?"
        // Specify arguments in placeholder order.
        val selectionArgs = arrayOf(userid)
        // Issue SQL statement.
        db.delete(DBContract.UserEntry.TABLE_NAME, selection, selectionArgs)

        return true
    }

    fun readUser(userid: String): ArrayList<UserModel> {
        val users = ArrayList<UserModel>()
        val db = writableDatabase
        var cursor: Cursor? = null
        try {
            cursor = db.rawQuery("select * from " + DBContract.UserEntry.TABLE_NAME + " WHERE " + DBContract.UserEntry.COLUMN_USER_ID + "='" + userid + "'", null)
        } catch (e: SQLiteException) {
            // if table not yet present, create it
            db.execSQL(SQL_CREATE_ENTRIES)
            return ArrayList()
        }

        var name: String
        var age: String
        if (cursor!!.moveToFirst()) {
            while (cursor.isAfterLast == false) {
                name = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_NAME))
                age = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_AGE))

                users.add(UserModel(userid, name, age))
                cursor.moveToNext()
            }
        }
        return users
    }

    fun readAllUsers(): ArrayList<UserModel> {
        val users = ArrayList<UserModel>()
        val db = writableDatabase
        var cursor: Cursor? = null
        try {
            cursor = db.rawQuery("select * from " + DBContract.UserEntry.TABLE_NAME, null)
        } catch (e: SQLiteException) {
            db.execSQL(SQL_CREATE_ENTRIES)
            return ArrayList()
        }

        var userid: String
        var name: String
        var age: String
        if (cursor!!.moveToFirst()) {
            while (cursor.isAfterLast == false) {
                userid = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_USER_ID))
                name = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_NAME))
                age = cursor.getString(cursor.getColumnIndex(DBContract.UserEntry.COLUMN_AGE))

                users.add(UserModel(userid, name, age))
                cursor.moveToNext()
            }
        }
        return users
    }

    companion object {
        // If you change the database schema, you must increment the database version.
        val DATABASE_VERSION = 1
        val DATABASE_NAME = "FeedReader.db"

        private val SQL_CREATE_ENTRIES =
                "CREATE TABLE " + DBContract.UserEntry.TABLE_NAME + " (" +
                        DBContract.UserEntry.COLUMN_USER_ID + " TEXT PRIMARY KEY," +
                        DBContract.UserEntry.COLUMN_NAME + " TEXT," +
                        DBContract.UserEntry.COLUMN_AGE + " TEXT)"

        private val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + DBContract.UserEntry.TABLE_NAME
    }

}

Understand the SQLiteOpenHelper CRUD flow in this Kotlin code

The helper class is the database layer of the app. The activity should not build SQL statements directly. It should call clear methods such as insertUser(), deleteUser(), readUser(), or readAllUsers().

Kotlin SQLite method	SQLite operation used	What it does
onCreate()	execSQL()	Creates the users table when the database is first created.
insertUser()	insert()	Adds a new row using ContentValues.
deleteUser()	delete()	Deletes rows that match the selected user id.
readUser()	rawQuery()	Reads rows for one user id.
readAllUsers()	rawQuery()	Reads every row from the users table.
onUpgrade()	DROP TABLE and recreate	Rebuilds the table when the database version changes. For real apps, write a migration that preserves user data.

SQLite CREATE TABLE statement used by the Kotlin helper

The SQL generated by SQL_CREATE_ENTRIES is equivalent to the following table creation statement.

CREATE TABLE users (
    userid TEXT PRIMARY KEY,
    name TEXT,
    age TEXT
);

Safer Kotlin SQLite query with selection arguments

The original readUser() method above demonstrates the basic idea of reading from SQLite. In application code, avoid joining user input directly into a SQL string. Use selection arguments or query() placeholders so that values are bound separately from the SQL statement.

fun readUserSafely(userid: String): ArrayList<UserModel> {
    val users = ArrayList<UserModel>()
    val db = readableDatabase

    val cursor = db.query(
        DBContract.UserEntry.TABLE_NAME,
        arrayOf(
            DBContract.UserEntry.COLUMN_USER_ID,
            DBContract.UserEntry.COLUMN_NAME,
            DBContract.UserEntry.COLUMN_AGE
        ),
        "${DBContract.UserEntry.COLUMN_USER_ID} = ?",
        arrayOf(userid),
        null,
        null,
        null
    )

    cursor.use {
        while (it.moveToNext()) {
            val userIdValue = it.getString(
                it.getColumnIndexOrThrow(DBContract.UserEntry.COLUMN_USER_ID)
            )
            val nameValue = it.getString(
                it.getColumnIndexOrThrow(DBContract.UserEntry.COLUMN_NAME)
            )
            val ageValue = it.getString(
                it.getColumnIndexOrThrow(DBContract.UserEntry.COLUMN_AGE)
            )

            users.add(UserModel(userIdValue, nameValue, ageValue))
        }
    }

    return users
}

Add a Kotlin SQLite update method for user records

The sample app already inserts, deletes, and reads rows. To complete CRUD, add an update method to UsersDBHelper. The method below updates the name and age for the row that matches the given user id and returns the number of affected rows.

fun updateUser(user: UserModel): Int {
    val db = writableDatabase

    val values = ContentValues().apply {
        put(DBContract.UserEntry.COLUMN_NAME, user.name)
        put(DBContract.UserEntry.COLUMN_AGE, user.age)
    }

    val selection = "${DBContract.UserEntry.COLUMN_USER_ID} = ?"
    val selectionArgs = arrayOf(user.userid)

    return db.update(
        DBContract.UserEntry.TABLE_NAME,
        values,
        selection,
        selectionArgs
    )
}

You can call this method from an Update button in the same way that the existing Add and Delete buttons call insertUser() and deleteUser().

Connect MainActivity.kt to the Kotlin SQLite helper

MainActivity creates an instance of UsersDBHelper and calls its methods when the buttons are clicked. The code below uses the older Kotlin Android synthetic view access pattern. In current Android projects, use View Binding, Data Binding, Compose state, or findViewById() instead of synthetic imports. The SQLite helper methods remain the same.

MainActivity.kt

package com.tutorialkart.sqlitetutorial

import android.support.v7.app.AppCompatActivity
import android.os.Bundle
import android.view.View
import android.widget.TextView
import kotlinx.android.synthetic.main.activity_main.*

class MainActivity : AppCompatActivity() {

    lateinit var usersDBHelper : UsersDBHelper

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        usersDBHelper = UsersDBHelper(this)
    }

    fun addUser(v:View){
        var userid = this.edittext_userid.text.toString()
        var name = this.edittext_name.text.toString()
        var age = this.edittext_age.text.toString()
        var result = usersDBHelper.insertUser(UserModel(userid = userid,name = name,age = age))
        //clear all edittext s
        this.edittext_age.setText("")
        this.edittext_name.setText("")
        this.edittext_userid.setText("")
        this.textview_result.text = "Added user : "+result
        this.ll_entries.removeAllViews()
    }

    fun deleteUser(v:View){
        var userid = this.edittext_userid.text.toString()
        val result = usersDBHelper.deleteUser(userid)
        this.textview_result.text = "Deleted user : "+result
        this.ll_entries.removeAllViews()
    }

    fun showAllUsers(v:View){
        var users = usersDBHelper.readAllUsers()
        this.ll_entries.removeAllViews()
        users.forEach {
            var tv_user = TextView(this)
            tv_user.textSize = 30F
            tv_user.text = it.name.toString() + " - " + it.age.toString()
            this.ll_entries.addView(tv_user)
        }
        this.textview_result.text = "Fetched " + users.size + " users"
    }
}

Run and test the Kotlin Android SQLite CRUD app

Install the app on an emulator or device and test the SQLite operations in this order.

1. Enter a unique user id, user name, and age.
2. Tap Add. The helper inserts the row into the users table.
3. Tap Show All. The activity reads all SQLite rows and displays the saved names and ages.
4. Enter an existing user id and tap Delete. The helper deletes the matching row.
5. Tap Show All again to confirm that the row is removed.

A simple run may show messages like the following in the result TextView.

Added user : true
Fetched 1 users
Deleted user : true
Fetched 0 users

SQLite limitations in Android and when to choose Room

SQLite is a good choice for structured local data that belongs to the app and can be stored on the device. It is not a remote database server, and it does not automatically sync data between users or devices. For shared data, login-based sync, or multi-device access, you usually need an API and a server-side database.

Android data storage need	Better option to consider	Reason
Learn low-level local SQL or maintain existing SQLite code	SQLiteOpenHelper	Direct access to Android SQLite APIs and SQL statements.
Build a new app with entities, DAO classes, migrations, and query checks	Room persistence library	Room provides an abstraction layer over SQLite and reduces boilerplate code.
Store a few settings such as theme, login flag, or sort order	DataStore	A full relational database is usually unnecessary for small key-value preferences.
Share data across multiple users or devices	Remote API with a backend database	SQLite is local to the app installation unless you implement sync yourself.

Common Kotlin Android SQLite mistakes to avoid

• Not incrementing the database version after schema changes: If you change table columns, update DATABASE_VERSION and handle the migration in onUpgrade().
• Dropping user data in production migrations: The sample drops and recreates the table for simplicity. Real apps should preserve data when possible.
• Building SQL with direct user input: Prefer selection arguments, query(), insert(), update(), and delete() with placeholders.
• Forgetting to close Cursor objects: Use cursor.use { ... } in Kotlin so the cursor is closed automatically.
• Running large database work on the main thread: For larger tables or repeated operations, run database work away from the UI thread.
• Using synthetic view imports in new projects: The sample keeps the older code unchanged, but new Android projects should use View Binding, Compose, or another supported UI access pattern.

Kotlin Android SQLite editorial QA checklist

• The tutorial explains what SQLite stores in this specific Android Kotlin example.
• The table name, column names, and model fields match across DBContract.kt, UserModel.kt, and UsersDBHelper.kt.
• The tutorial warns that the sample onUpgrade() strategy drops data and should be replaced by real migrations in production apps.
• The article mentions modern Android alternatives such as Room for new database-heavy apps and DataStore for small settings.
• Any new code block uses a PrismJS-compatible language class such as language-kotlin, language-sql syntax, or output.

Kotlin Android SQLite FAQs

Kotlin Android SQLite CRUD example summary

In this Kotlin Android SQLite tutorial, we created a simple user management app with a local SQLite table. We used a contract class for schema names, a model class for row data, a SQLiteOpenHelper class for CRUD methods, and an activity class to call those methods from the UI. The same structure can be extended with update buttons, safer query methods, validation, background execution, and proper database migrations.