Many people recommend using a database like Postgres for production and SQLite for testing.
How can you use two different databases without an ORM when they have different SQL dialects?
#Using Postgres and SQLite without an ORM
fleet agate
drowsy pecan
i use postgres for my prod and dev. my dev postgres runs on my dev server at home that isnt reliant on my pc being on
you can make postgres/sqlite work if you write service functions that you can call independently of whatever youre running. just make sure you use the pure-go implementation of sqlite instead of the cgo version
snow lion
Recommended by who? Using a database like Postgres for production and SQLite for testing is a nonsense.
final wasp
an interface for your DAO methods, and one implementation for each supported database, i.e.
spice iris
Recommended by who? Using a database like Postgres for production and SQLite for...
Me, and a number of others in this community
I do that both for personal projects and at work as well. Wasn't even the one that introduced it at work either. It's a fairly established practice
I'm assuming it comes from RoR but it works really well all around, and it makes sure you have a sturdy and storage agnostic architecture
final wasp
so you just wanted two different DBMS's?
spice iris
Yes, it could be MySQL and SQLite or even Cassandra and SQLite
The entire point is to make testing locally fast and simple, and to assert that your interface is correct
final wasp
you can achieve that with the same DBMS too
spice iris
Ideally you assert both storage impl at once, e.g in CI by writing DBMS agnostic tests and make sure they return the same values
spice iris
you can achieve that with the same DBMS too
If you use SQLite in prod you can, but spinning up and down postgres is much more of a hassle
final wasp
not everybody has to sping up and down any time they run tests
spice iris
So you run a long running dbms that you seed and now you need to do migrations for and all? That's so much maintenance
final wasp
well, no reason has been provided to contradict @snow lion
final wasp
the amount of time that a test of a traditional DAO method lasts does not really warrant an embedded version over the real thing
without ORM, writing two different dialects is an overhead that can't be really be justified
and you can't run the test dialect to test the queries in the main dialect
spice iris
The overhead to writing the same storage impl twice is very small for the things you gain imo
Especially with a decent query builder, but even without it's very very simple
final wasp
OP said no ORM, that may imply no query builders
snow lion
I do that both for personal projects and at work as well. Wasn't even the one th...
If your test application is using sqlite, but production is using postgres, then your production application is basically untested. Sure postgres and sqlite are mostly compatible but they are still different database engines, with special gotchas.
There is no reason for anybody not to run the same database engine for both production and testing, especially since docker made it trivial.
spice iris
You can test against SQLite locally for easy of use, both in CI and only postgres in prod?
spice iris
You can also have tests that rely on storage backends rely on SQLite so they're simple to run locally and in CI while you deeply test both impl elsewhere
final wasp
it does not matter who simple it is, it is just useless to test the other database
so, coming back to the OP question: you can use an interface for your DAO methods, and one implementation for each supported database, and please test each implementation with its own real DBMS. You can always write unit tests for the business logic mocking the given interface.
long urchin
Yeah, like in Postgres ur primary key is always secondary index
But in sqlite its not the same
The whole engine is different
fleet agate
Recommended by who? Using a database like Postgres for production and SQLite for...
I started my journey with Django a year ago, and obviously it uses ORM, so it is trivial to use 2 DBMS. I think every Django user writes this way and this is what they will recommend to you. I am not talking about whether it is sensible rn but that was my experience.
I don't use ORM or Query Builder. I want to type SQL myself, so it does not work anymore.
fleet agate
If your test application is using sqlite, but production is using postgres, then...
How to use Docker to make testing trivial with Postgres?
fleet agate
The whole engine is different
Yeah not only do you type twice (imo it sounds like first step to writing a bug), but also engines are different.
final wasp
How to use Docker to make testing trivial with Postgres?
you let your code connect to an instance of postgresql running in a container; with official images you just have to run one line of docker run and you get your dbms without having to go through installers or lengthy processes; really awesome
fleet agate
you let your code connect to an instance of postgresql running in a container; w...
Do you embed docker run and config into your test suite so when you run tests, a new instance is created and destroyed after tests are run?
final wasp
You can do that, but you can also keep the server running, and then tests connect to it and write and read as they need.
plush plover
My integration tests use docker compose to start the db, the system under test and a separate container to run the tests. This also allows testing the migration scripts.