Creating SQLite Tables Programmatically in Kotlin

Understanding SQLite and Kotlin

SQLite is a popular relational database system, integrated within an application, offering efficient storage capabilities. Kotlin, being a modern and concise language, simplifies operations with SQLite. This article will guide you through creating SQLite tables programmatically in Kotlin.

Getting Started with SQLite in Kotlin

First, ensure you have the necessary dependencies set in your Android app's build.gradle file:

implementation "androidx.sqlite:sqlite:2.1.0"

androidx.sqlite is a part of AndroidX library that provides an SQLite database feature and ensures backward compatibility for older versions.

Creating a Database Helper

In order to create and manage a database, Kotlin makes use of SQLiteOpenHelper class. You need to create a class that extends SQLiteOpenHelper:

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper

class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    companion object {
        const val DATABASE_VERSION = 1
        const val DATABASE_NAME = "MyDatabase.db"
    }

    override fun onCreate(db: SQLiteDatabase) {
        // This method is called during the creation of the database
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // This method is called during an upgrade of the database
    }
}

Defining and Creating SQLite Tables

Inside the onCreate method, you can define the SQL statements to create your tables. Here is an example of creating an "Employee" table:

override fun onCreate(db: SQLiteDatabase) {
    val createEmployeeTable = """
        CREATE TABLE Employee (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            firstName TEXT NOT NULL,
            lastName TEXT NOT NULL,
            department TEXT NOT NULL
        )
    """
    db.execSQL(createEmployeeTable)
}

This code sets up a simple table with the columns id, firstName, lastName, and department. The id column is specified as the primary key.

Managing Database Versions

When changes are required, such as modifying table structures, the onUpgrade method is called. Typically, this method facilitates steps to upgrade the database schema:

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

This basic example drops the existing "Employee" table and calls onCreate to recreate it. In a production environment, you should handle upgrades more granularly to preserve existing data.

Creating an Instance of the Database Helper

Lastly, create an instance of your DatabaseHelper class within your activity or application where it is needed:

val dbHelper = DatabaseHelper(context)

From here, you can call methods like getWritableDatabase() or getReadableDatabase() to access the database.

Conclusion

Working with SQLite in Kotlin simplifies data management tasks in Android applications. With the use of SQLiteOpenHelper, developers can efficiently manage databases, ensuring that application requirements are met dynamically. With the knowledge provided in this guide, you can now build and integrate complex data structures within your apps.