Exposing `DB::Connection#last_insert_id`

[Blacksmoke16]

Extracted from #227:

---

I wanted to propose a few ideas/suggestions based on my experience working on https://forum.crystal-lang.org/t/athena-orm-alpha-version-testers-wanted/8904. Most of these are things I just had to work around/monkeypatch in, but are worth discussing if it'd be useful implementing them upstream natively.

DB::Connection#last_insert_id

One of the first things I ran into was dealing with knowing the PK of a record that was inserted. Each DB has its own standards so there is no super clear way to handle this. E.g. Postgres relies on RETURNING and always returns 0 for DB::ExecResult#last_insert_id, while MySQL doesn't support RETURNING. For some of them you can make use of like SELECT LASTVAL() (or similar), but then others don't support something like that at all.

MOST databases do seem to support some form of SELECT LASTVAL() so it felt like a reasonable default, which I ended up going with. However because it's only exposed on DB::ExecResult it wasn't the easiest to use. It would be more useful to add DB::Connection#last_insert_id that essentially does self.scalar("SELECT LASTVAL()").as Int64 | String (or whatever the exact statement is for the DB). This would at least be a good fallback for DB's that don't support something better (like RETURNING).

DB's that do NOT support this should raise a specific exception, or if the result in not a valid identifier (like it returns false or nil or something).

[Blacksmoke16]

@ysbaddaden In regards to #227 (comment)

I guess my thinking is that the ORM should of course encourage/use the best solution given the DB driver/version being used. However it shouldn't be the base DB shard's job to dictate that. Doctrine has been using SELECT LASTVAL() for like a decade and its been fine. Are there better options? Yes of course, but I don't think it's as big of a deal that would make gatekeeping this that critical.

[ysbaddaden]

I don't think Doctrine's a good example to follow. It makes assumptions that usually work, but are easy to break, and then hard to debug.

IMO: DB shouldn't encourage to use #last_insert_id in any way. It should even fail to compile, except for the MySQL driver. If someone want SELECT LASTVAL() then they must explicitly call db.scalar("SELECT LASTVAL()"), but what they should really do is use RETURNING that is supported by every database, including MariaDB.