search

fukamachi / mito

An ORM for Common Lisp with migrations, relationships and PostgreSQL support
284 stars 31 forks source link

Support for foreign key and other constrains. #104

Open eko234 opened 3 years ago

eko234 commented 3 years ago

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 commented 2 years ago

+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 commented 2 years ago

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 commented 2 years ago

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

Mr-Dispatch commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

@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...