Drizzle and unit testing using SQLite / MySQL

[SamyPesse]

I'm overall wondering what the best strategy to run isolated unit tests using Drizzle is. My original thinking was to use in-memory SQLite databases instead of a shared MySQL since Drizzle seems to support both SQLite and MySQL.

But the main issue I'm facing is that Drizzle supports both by basically acting as different libraries. In short, Drizzle is not a single ORM library to work with these databases, it's a collection of independent libraries that have a somehow similar API. But "somehow similar API" doesn't work well with Typescript.

How are people writing tests for their logic? (Tests that can be isolated, not complete integration tests).
Is the goal of Drizzle to converge toward a single API or is Drizzle not going to be designed for this?

[205g0]

There's nothing like "Isolated tests" when doing db tests.

The reason: You need to have a db with the current schema and mock data. Either you create aforementioned for every unit text, or every unit test group or not.

The best way is:

• Assuming there's a always db with the current schemae (your dev instance), so this doesn't need to be recreated for testing; having a dev instance around which has the current schema reflects dev reality
• If you have data branching from production, you would have real production data but since this feature is pretty new and also expensive we assume that the db isn't prefilled; so every test group should be populated with some mock data, otherwise everything gets to cumbersome and slow if we did it for every tests
• Write your "isolated tests" which aren't really isolated because yeah

In general, you should be cautious with doing too many unit tests. Many unit tests (something dynamic langs like RoR do a lot) are not really required if you have end-to-end type safety which you have with TypeScript and Drizzle; this is the main reason we are using TypseScript and Drizzle, otherwise why are you using them? Also doing too many unit tests slows down development (in particalur when refactoring) and are not DRY (because you just double the static typing already in place by TS and SQL).

Rather focus on the right and few integration tests (reflecting crucial processes, e.g. sign-up, purchase) because your "isolated tests" are defacto integration tesst if you consider the preconditions you need to have.

Bonus: Always test against the dev instance of your production database; so, if your have MySQL as production db, you can't use sqlite for testing; both are entirely different dbs only sharing a similar API layer ("SQL") which is too different, even when used via Drizzle.

[fermentfan]

I totally disagree about using a real database for unit testing as they should be 100% stateless. It's standard behavior to have unit tests in place for example to check for the number of calls to a specific database operation. How would you test how many times an update has been called to the database in case of some loop action when you're in an integration test suite? You lose so much clarity if you don't do it with some mocked Drizzle instance in an isolated test case.

Drizzle needs to support developers here. I see multiple people asking for this and they seem to never get an answer.

[Phoenixmatrix]

Agree with @fermentfan here. I was surprised not to see a standard story around this when evaluating Drizzle. While yes, the different databases are very different, a lot of teams like to test their code without needing to mock the database. Sure, it means there could be database specific bugs that don't get caught or false negatives, but you at least know the non-database code works without the overhead of mocking the data layer (if you should mock it or not is up for debate and should be left to individual teams to decide).

Testing in SQLite regardless of the real database engine is still pretty common practice, and it would be nice to have that option. It could be as simple as a function that generates an SQLite schema from any of the other database schemas produced for drizzle-kit migrations, throwing errors for unsupported on obviously incompatible functionalities, leaving the rest to the teams to build their test pipelines.

Even if I didn't care for this type of integration tests, in a large enough org, SOMEONE will want to do it and it will rule out drizzle right there and then, forcing us into other alternatives (eg: Prisma, where this is pretty simple to do, for better or worse).

[jwatte]

There's nothing like "Isolated tests" when doing db tests.
The reason: You need to have a db with the current schema and mock data.

It's totally possible to have the tables and schemas set up as in-memory objects. An array per table, each element is a map with the row data, and each SQL operation is a simple for loop (joins on large data will be slow, but you don't unit test on large data.)
Each test gets a brand new world, by simply cloning the mock data in memory. You can even run them in parallel with no worries; something which a "real" database has real challenges doing.

This pattern is important enough that I'm quite surprised a popular ORM hasn't done it yet. You even have all the query construction mechanisms right there; no need to write a SQL parser because you know what the query is supposed to do semantically.