Few questions

[ashokgelal]

I've couple of questions regarding the usage of Ktorm:

1. Is SQLite supported? (Both file and or memory based)
2. How does one create the tables (equivalent of Exposed's SchemaUtils.createMissingTablesAndColumns or are the tables automagically created on the first invoke?
3. Any plan to support entity relationships?

Sorry to add multiple questions in one issue (if you want, I can close this and create separate issues).

[vincentlauvlwj]

Sorry for the late reply.

1. SQLite is supported. You just need to add the following dependencies to your Gradle build file:

implementation "me.liuwj.ktorm:ktorm-support-sqlite:2.4"
implementation "org.xerial:sqlite-jdbc:3.28.0"

2. Ktorm doesn't create tables. It's encouraged to create your tables manually on your DBMS first, then write Ktorm's table definitions with Kotlin. This may be a little bit annoying, so I'm planing to develop a plugin for IntelliJ IDEA to generate those table objects.

3. Entity relationships are now supported to some extent. See the function Table.references. Here is a usage example:

interface Department : Entity<Department> {
    val id: Int
    val name: String
}

interface Employee : Entity<Employee> {
    val id: Int
    val name: String
    val department: Department
}

object Departments : Table<Department>("t_department") {
    val id by int("id").primaryKey().bindTo { it.id }
    val name by varchar("name").bindTo { it.name }
}

object Employees : Table<Employee>("t_employee") {
    val id by int("id").primaryKey().bindTo { it.id }
    val name by varchar("name").bindTo { it.name }
    val departmentId by int("department_id").references(Departments) { it.department }
}

Here we use references function to bind the foreign key departmentId to the table Departments. With this reference binding, when we obtain employee objects from the database, Ktorm will auto left join the referenced table, obtaining the departments at the same time, and filling them into property Employee.department.

val employee = Employees.findById(1)

Generated SQL:

select ... 
from t_employee left join t_department _ref0 on t_employee.department_id = _ref0.id 
where t_employee.id = ? 

That's how Ktorm supports one-to-one and many-to-one relationships. As for one-to-many, we can also implement it by a property with a custom getter, for example:

interface Department : Entity<Department> {
    val id: Int
    val name: String
    /** Return the employees in this department. */
    val employees get() = Employees.findList { it.departmentId eq id }
}

Hope this might help you.

[ashokgelal]

Thank you! I'm wondering any reason why you'd rather spend time on creating a plugin rather than maybe adding some kind of migration support or at least Exposed like createMissingTablesAndColumns functionality?

[lamba92]

Thank you! I'm wondering any reason why you'd rather spend time on creating a plugin rather than maybe adding some kind of migration support or at least Exposed like createMissingTablesAndColumns functionality?

I totally agree with you. A method like this is incredibly helpful.
In my particular case I am creating a RestRepositories Feature for Ktor using Ktorm and being able to not care about creating those table would be really great.

[yuzd]

about Table.ColumnRegistration.references example above,

val employee = Employees.findById(1)

select ... 
from t_employee left join t_department _ref0 on t_employee.department_id = _ref0.id 
where t_employee.id = ? 

how about add code to clearly stated for do refereences search！
for example:

Employees.loadWith(it.departmentId ).findById(1)

select ... 
from t_employee left join t_department _ref0 on t_employee.department_id = _ref0.id 
where t_employee.id = ? 

and if not use loadWith will not auto add references search.

[vincentlauvlwj]

@yuzd

By default, the find* functions and Sequence APIs will auto left join the reference tables. If you don't want to join them, use asSequenceWithoutReferences to disable the auto-joining behavior:

val employee = Employees.asSequenceWithoutReferences().filter { it.id eq 1 }.single()

Generated SQL:

select ... 
from t_employee 
where t_employee.id = ? 

[yuzd]

@vincentlauvlwj i got it, thanks