> you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic.
I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Maybe some commercial products still try (choose MS or Oracle!), but it's rare nowadays.
The primary selling point of an ORM is that it abstracts marshaling/un-marshaling rows to/from entities. Instantiating and persisting entities to relational storage.
> And by "drop into", this typically means writing custom stitching code that stitches the SQL cursor results back into the models again. It's rarely straightforward.
That's not typical in most uses I've seen. Far more typical are things like:
- Go straight to SQL for reporting, since that's what SQL does. Useful in reporting contexts, and also for list/filter UI screens.
- Use raw SQL to query a list of entity IDs for updating based on some complex criteria. Iterate over the identifiers and perform whatever logic you need to before letting the ORM handle all the persistence concerns.
> I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s.
Do you use the same database engine for your unit and integration testing as you do production? I don't. I use sqlite for unit and local integration testing, and aurora-mysql for production.
As a side note, I quite literally can't use aurora-mysql for local unit and integration testing. It doesn't exist outside AWS.
Unit testing code that touches the database is not useful, and in fact it indicates there is likely a design flaw. Code that acquires from or changes data in the DB should be self contained.
An integration test that doesn't use the same DB as production is unsatisfactory.
Using a DB inside your unit tests is an antipattern and arguably a violation of the concept of a unit test in the first place.
Integration tests should run against a test environment, otherwise, what integration are you testing? I don't see the value in writing integration tests that test the integration between my code and a one-off integration test DB that exists solely for the purpose of integration testing.
There is no syntactical difference between Aurora/MySQL and regular MySQL until you start interacting with S3.
Also if you’re using only the subset of MySQL that is supported by SQLite, you’re missing out on some real optimizations for bulk data loads like “insert into ignore...” and “insert...on duplicate key update...”. Besides that, the behavior of certain queries/data types/constraints are inconsistent between MySQL and every other database in existence.
Finally, you can’t really do performance testing across databases.
> Do you use the same database engine for your unit and integration testing as you do production? I don't. I use sqlite for unit and local integration testing, and aurora-mysql for production.
I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Maybe some commercial products still try (choose MS or Oracle!), but it's rare nowadays.
The primary selling point of an ORM is that it abstracts marshaling/un-marshaling rows to/from entities. Instantiating and persisting entities to relational storage.
> And by "drop into", this typically means writing custom stitching code that stitches the SQL cursor results back into the models again. It's rarely straightforward.
That's not typical in most uses I've seen. Far more typical are things like:
- Go straight to SQL for reporting, since that's what SQL does. Useful in reporting contexts, and also for list/filter UI screens.
- Use raw SQL to query a list of entity IDs for updating based on some complex criteria. Iterate over the identifiers and perform whatever logic you need to before letting the ORM handle all the persistence concerns.