Foreign & Indexes support

[viktorhrekh]

Use case

https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html
https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

Describe the solution you'd like

Annotations like @KomapperIndex and @KomapperKey.

[nakamura-to]

Hi,

The syntax of the commands that create indexes and foreign keys is very flexible and unsuitable to be expressed in annotations.

To create indexes and foreign keys, please use the executeScript function:

val query = QueryDsl.executeScript("CREATE INDEX part_of_name ON customer (name(10));")
db.runQuery(query)

[dave08]

Doesn't have to be annotations... (although it would be nice...), it could be some kind of DdlDsl or maybe a builder on the createSchema (which would make creation of a table in one statement, which could be nicer...).

Otherwise, we loose some of the beauty of Komapper's support for multiple dbs... we can't use the same code to create the indexes between dbs (like for a production/staging or testing db -- testing dbs by us are in H2 for speed unless specific db features are needed...)

[nakamura-to]

I believe that the development environment should use the same type of database as the production environment.
Testcontainers doesn't meet your needs? Is it too slow for you?

[dave08]

I use Testcontainers before a release... but while doing TDD it's too slow. H2 is sufficient for TDD and before a release, running with a real db just to make sure.