Support for foreign key and other constrains.

[eko234]

I think it would be useful to support foreign key definitions for data integrity, You can always define your tables manually, but being able to do it whit mito would be cool.

[Mr-Dispatch]

+1, if creation of tables / migration is supported, foreign keys should be supported, especially for ON UPDATE / ON DELETE support (it is quite a bit of error-prone work to "roll your own" recursive cascading deletes or updates).

[eko234]

Lua has a web framework called Lapis, it has an interesting concept to bypass its limitations, after the field declarations, there can be a last string that is also run as part of the sql, maybe mito could include a key argument to allow something along those lines like :finally :after :extra-sql

[eko234]

It could be a way to also define triggers or stored procs related to that table.

[Mr-Dispatch]

I agree that it [":extra-sql"-like mechanism] would be a nice addition, but IMO foreign keys and [potentially multi-field] indexes & constraints should be supported out of the box (as a bonus, the DSL has a fat chance of being more concise than DDL).

I understand not supporting triggers (not every DB supports them, a lot of projects don't require them), but foreign keys, indexes and constraints are both "modelling tools" and documentation.

[eko234]

I lately have been running in this kind of stuff with other ecosystems, and surely others do, aiming to cover many sql dialects/dbs implies suporting a small compatible subset, an interesting idea outside the scope of mito would be a framework to declare wrappers or build abstractions for databse interaction, maybe there is one out there.

[Mr-Dispatch]

@eko234 Well, Mito's claim is "Supports MySQL, PostgreSQL and SQLite3".
SQLite3 even doesn't enforce foreign keys (AFAIR) unless forced by a PRAGMA.
But it still supports them on the "declaration / documentation" level.
And from observations, quite a few (open source [not interested in closed-source variants for boring languages]) ORMs were able to support the holy trinity (Foreign Keys, Indexes & Constraints) while supporting (in addition to at least one "truly SQL Compliant fully ACID DB") oddballs like MySQL or SQLite...