Skip to main content

Databases and Migrations

Database

The PEKK stack used by Narbase uses Postgresql for database management. Ensure that Postgresql is installed and running on your machine.  Then refer to the .conf file in your project. Create databases by the names specified in the .conf file, along with the new roles with respective passwords. Give the necessary permissions for the roles. 

Migration

Database migrations are controlled sets of changes developed to modify the structure of the objects within a relational database. Migrations help transition database schemas from their current state to a new desired state, whether that involves adding tables and columns, removing elements, splitting fields, or changing types and constraints. Each migration implements up  and  down  methods The up method is called when migrating “up” the database – forward in time – while the down method is called when migrating “down” the database – or, back in time. In other words, the up method is a set of directions for running a migration, while the down method is a set of instructions for reverting a migration. This implies that the code in these two methods should fundamentally do the opposite things of one another.

To create a new migration, create a new object of the existing Migration class. Then override both up and  down functions. Name your migration class descriptively to emphasize the change to the database. Such as AddStudentTable(). 

import org.jetbrains.exposed.sql.transactions.transaction
import tech.suhub.sutech.common.db.migrations.Migration
import tech.suhub.sutech.common.db.migrations.version
object AddStudentTable : Migration("Add Student table", version(2022, 12, 22, 9, 23)) {
    override fun up() {
        transaction {
            exec("""
                CREATE TABLE IF NOT EXISTS student (id uuid PRIMARY KEY, full_name TEXT NOT NULL, address TEXT NOT NULL);
            """.trimIndent())
        }
    }
    override fun down() {
        transaction {
            exec("""
                DROP TABLE student;
            """.trimIndent())
        }
    }

}

Every Narbase project is started from a core project which includes several basic packages. Add your migration object to initializeMigrations() method in the UserMigrations file, or similarly named class.

fun initializeMigrations() {
  Migrations.migrations = listOf(
    InitialMigration,
    AddAdressColumnInTutorTable,
    AddStudentTable,
   )
}

After Migration

Depending on the content of the migration, specific additions in several related files must be made. For instance. If the migrations involved adding an 'address' column to the student table, then add the address property in the toDto() and toDs() functions of the studentTable provided it already exists, in the conversions, or similarly named, package. Then add the property to the studentDto, provided it exists. At this point, your IDE will provide prompts wherever the property must be added. 

For the instance of AddStudentTable migration, a StudentTable object of type UUIDTable should be created. Then, a new Dto must be created for the student table. Besides, a DAO, which includes CRUD operations for the entity is created  (see DAOs and Repositories). The toDto() function discussed above should return a StudentDto object and the toDs() returns a ModelwithId object. 

StudentTable
object StudentTable : UUIDTable("student") {
    val fullname = text("fullname").nullable()
    val address = text("address").nullable()
}

StudentDao 
data class Student(
    override val id: UUID?,
    val fullname: String?,
    val address: String?,
) {}

object StudentDao :
    BasicDao<TalentsAdditionalInfoTable, UUID, TalentsAdditionalInfo>(TalentsAdditionalInfoTable) {
    override fun toStatement(model: student, row: UpdateBuilder<Int>) {
        row[StudentTable.fullname] = model.fullname
        row[StudentTable.address] = model.address
    }

    override fun toModel(row: ResultRow): Student {
        return Student(
            row[StudentTable.id].value,
            row[StudentTable.fullname],
            row[StudentTable.address],
        )
    }
    
... //CRUD operations


StudentDto
data class StudentDto(
    val id: String?,
    val fullName: String?,
  	val address: String?
)


Conversions

The following functions allow conversions between DTOs(Data Transfer Objects) and data structure models. Call the toDs() function on the StudentDto in your controller later when calling CRUD operations on the request DTO on your Controller (see Controllers and CRUDs). Call the toDto function on the response to convert it to a DTO. 

context (Transaction) //within a table Transaction
fun Student.toDto(): StudentDto {
    return StudentDto(
        id = id?.toStringUUID(),
        fullname = bio,
        address = address,
    )
}

fun StudentDto.toDs(): Student {
    return Student(
        id = runCatching { UUID.fromString(id) }.getOrNull(),
        fullname = bio,
        address = address,
    )
}


Finally

In the above example, the student table was newly created and not used anywhere including on the client side. In many cases, a migration will include an alteration to an existing table. The DTO of the table in question will probably already be in use in several places. Ensure that the change is applied everywhere to avoid unexpected bugs later. 

Back to top