7 ms·

We spin up a docker container running the DB technology we use, run our DB migration scripts on it, and then run integration tests against it. You get coverage

• pgoggijr 4y ago
  I've used this same approach as well. Testcontainers is a nice way to help with this! https://www.testcontainers.org/ https://www.testcontainers.org/
  • JackFr 4y ago
    I rolled my own with docker for a few years and recently made the switch to testcontainers. So far so good - but if you’re in an environment or language where test containers are difficult, rolling your own really it ant to hard. It also keeps you honest with maintaining good migration scripts.
  • jve 4y ago
    What is testcontainer? Reading the first page I don't understand what benefits it buys me: > Testcontainers for .NET is a library to support tests with throwaway instances of Docker containers for all compatible .NET Standard versions. The library is built on top of the .NET Docker remote API and provides a lightweight implementation to support your test environment in all circumstances. Edit: Ok, example helps. https://dotnet.testcontainers.org/examples/aspnet/ https://dotnet.testcontainers.org/examples/aspnet/ I can declare docker infrastructure from my code, right?
    • therealdrag0 4y ago
      Yep instead of starting the container in the build script it’s nice to start it in the test code file itself.
• inetknght 4y ago
  bonus points if you add test data for integration tests with sad paths too
  • csteubs 4y ago
    Absolutely this. I stood up a negative test suite for continuous DB queries late in 2020 and it's caught many potential show stopper integration issues since; about 45% more YoY than pre-suite. Took about a week of duplicating happy path tests, investigating gaps, and switching inputs/assertion types to get everything passing, but less than a week later we had our first verifiable test failure.
• MBCook 4y ago
  This is what we did at my last job. You can catch DB specific issues that a false implementation wouldn’t show and make sure all your code paths work as expected. Every time new issues cropped up we would put new data in the test data designed to reproduce it. Every edge case we would run into. It provided so much confidence because it would catch and trigger so many edge cases that testing with mocks or by hand would miss. Edit: also, it’s great for test/dev environments. You don’t have to worry about losing important data or filling new environments with data. Just start with the full test data and you’re good to go. It’s got stuff for all the corner cases already. Something got screwed up? Blow it away and reload, it’s not precious.
  • jdc0589 4y ago
    same here. every backend service with a sql datastore runs an real ephemeral db instance during CI, runs the normal migrations against it, and uses it for tests of the direct data access/persistence code. everything else in the service with a dependency on the data-access stuff gets a mocked version, but testing the actual data access code against a real datasource is non-negotiable IMO.
  • fatneckbeard 4y ago
    thats a huge amount of work and money. at my company they just told us to stop reporting edge cases. much easier, much cheaper.
    • nicoburns 4y ago
      It’s not that much work
      • organsnyder 4y ago
        Especially in the long-run.
        • fatneckbeard 4y ago
          its a joke people im trying to humorize the bureaucratic existence that most of humanity lives under.
• allanbreyes 4y ago
  This is a great way to test for backwards-incompatible changes if your fleet is running canaries or instances on different versions backed by a singleton database. You apply the migrations, checkout the app from the old version, and then re-run your test suite. Any failures are a reasonably high signal that some backwards-incompatible migration was introduced.
• ddyevf635372 4y ago
  Running sqlite in memory as a test db speeds up your test runner as crazy. You can do this if you use an sql query builder library, because it can translate your queries to the specific database.
  • rc_mob 4y ago
    problem is that its not always compatible with features you use on your production database
    • waste_monk 4y ago
      Can split test regime so that as much as possible is covered with SQLite, and then have a second test phase with a heavyweight db only if the first phase passes. So code errors, malformed SQL, etc. cause it to fail fast and early, and you only test with the real DB once you know everything else is working. Or along similar lines you could divide it such that developers can test things locally on their machines with SQLite, but once it gets pushed into CI (and passes code review etc.) it's tested against the heavy db.
      • dbsmith83 4y ago
        That still doesn't fix the compatibility issues. Postgres has features/syntax that sqlite does not have, so you can't test postgres syntax with sqlite sometimes
        • waste_monk 4y ago
          That's fair enough. I meant in situations like parent comment where you're using an ORM such as hibernate that supports multiple databases, you can test as much of the non-DB specific stuff with SQLite in-memory and then do a separate batch of tests with DB specific behaviour.
  • gardenhedge 4y ago
    So you test against a different database technology than the one you software uses? I understand why that works but it seems odd
• sssspppp 4y ago
  would love to do this, but how does one spin up a redshift cluster inside of a docker container?
  • contravariant 4y ago
    Well the first step is to get Amazon to part with their lucrative closed source software.
  • ntr-- 4y ago
    try not to cry, cry a lot, and then resolve not to vendor lock yourself to a black box data store next time. jokes aside, redshift is based on pg^1, you can try an older version to get some semblance of it running locally. 1. https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-an...
  • danmcs 4y ago
    Redshift speaks the postgres protocol so you might be able to use postgres. There are a few purpose-built docker images (googleable) that may replicate Redshift slightly better than just `docker run -p 127.0.0.1:5439:5432 postgres:latest`, but if you're at the point of having a test suite for your data warehouse code, you're likely using Redshift-specific features in your code and postgres won't suffice. I have seen teams give each developer a personal schema on a dev cluster, to ensure their Redshift SQL actually works. The downside is that now your tests are non-local, so it's a real tradeoff. In CI you probably connect to a real test cluster.
  • nicoburns 4y ago
    And this is why you don’t rely on a closed source stack if you have any alternative.
  • atwebb 4y ago
    Would redshift serverless help at all? I realize that it may not have parity with the existing functionality, just a thought.
• MuffinFlavored 4y ago
  Do you do this in place of unit tests (where you have to mock/stub the DB interactions) or do you do both?
  • nishs 4y ago
    Sorry for the digression first. (If anyone has different definitions for the ideas here, I would love to learn.) I think the answers would depend on the types of tests that the term "this" encompasses. From how I understand it, calling something a unit test or an integration test depends on the context of what is is being tested. For example, if a developer is writing a unit test for a HTTP handler, and the handler implementation includes calls to an external database, then one would have to use a mock for the database, in order for the test to be deemed a true unit test [1]. If the developer is writing an integration test for the same HTTP handler, then the database would have to be a real database implementation [2]. On other hand, if the developer were testing SQL queries for syntactical or logical correctness against a database, these tests would want to use a real database implementation. Note that though the test uses a real database, it is still a true unit test [3]. Additionally, note that using a mocked database here would not serve the purpose of the tests, which is to catch syntactical and logical errors against a database engine. This can, of course, only be achieved by using a real database—or, if you insisted on using a "mock", then, by implementing an entire SQL engine, with the exact same quirks as the real database's engine, inside the mock! On the original question: > Do you do this in place of unit tests (where you have to mock/stub the DB interactions) or do you do both? I guess the answer would be: It would depend on the objectives of and types of tests. Do both of them, because some tests, such as unit tests on the HTTP handler, would use use mocks, while other tests, such as the SQL query correctness tests, would use the real database. [1] A true unit test is one that has no external interactions, neither directly nor transitively, besides interactions with the system under test (SUT). The SUT here is the HTTP handler. [2] An integration test should include external system interactions, if any. That's what integration means. [3] The SUT is each SQL query and the its interaction with the database. There are no interactions in the test with systems outside the SUT, so it is still a true unit test.
    • timrichard 4y ago
• atmosx 4y ago
  Yeah. That’s it. I have seen method with sample data (low fidelity) and/or performed against production copies of RDS DB (high fidelity). It’s still hard to catch some migrations or other operations under workload, but you can emulate that as well to a certain point.
• hot_gril 4y ago
  Same (though I don't use Docker). Did TDD for a while like this, and it wasn't perfect, but it worked better than anything else. I didn't even know how to run the frontend; that was a separate team.
• claytonjy 4y ago
  Yup, same. Last time i set this up i used Sqitch¹ for migrations, which encourages you to write tests for each migration; caught a lot of bugs early that way, all in a local-first dev environment. Worked especially well for Postgres since plpgsql makes it easy to write tests more imperatively. ¹: https://sqitch.org/ https://sqitch.org/
  • Dowwie 4y ago
    I resented writing the verify scripts for my migrations, after writing unit and integration tests, but yes it is valuable
  • grilledcheez 4y ago
    At my job, we're breaking down a monolith into services with a hand-me-down database schema. DB changes are manual, every dev runs against a shared test DB, and everybody dreads doing schema changes. I've been looking for a way to transition into version controlled migrations and it looks like sqitch might be a solid option, as the language-specific frameworks are too opinionated. Thanks for recommending!
    • claytonjy 4y ago
      You're very welcome! FWIW there are other general migration frameworks worth considering; the two most popular seem to be Flyway and Liquibase. I've heard good things about both, and don't have a particularly strong defense for my sqitch preference. I like that it's simple, has great docs, and has verification as a natural step in the workflow.
      • tianzhou 4y ago
        We built a GUI based solution called https://github.com/bytebase/bytebase https://github.com/bytebase/bytebase. You may also take a look.
• unboxingelf 4y ago
  This is the way
• deleted 4y ago
  [deleted]
• feike 4y ago
  We do this too for PostgreSQL: to ensure the tests are really fast: - we create a template database using the migrations - for *every* integration test we do `CREATE DATABASE test123 TEMPLATE test_template;` - we tune the PostgreSQL instance inside Docker to speed up things, for exampling disabling synchronous_commit On a successful test, we drop the test123 database. On a failed test, we keep the database around, so we can inspect it a bit. The really great thing about this approach (IMHO), is that you can validate certain constraint violations. For example, exclusion constraints are great for modelling certain use cases where overlapping ranges should be avoided. In our (go) code, the test cases can use the sqlstate code, or the constraint name to figure out if we hit the error we expect to hit. This approach is pretty much as fast as our unit tests (your mileage may vary), but it prevents way more bugs from being merged into our codebase.
  • neonate 4y ago
    Out of curiosity, how fast is really fast?
    • feike 4y ago
      Just did a sequential run (to get some better measurements), and this is an excerpt of the things happening in the PostgreSQL instance inside the Docker container, for creating and dropping the databases: 08:25:37.114 UTC [1456] LOG: statement: CREATE DATABASE "test_1675239937111796557" WITH template = test_template [noise] 08:25:48.002 UTC [1486] LOG: statement: DROP DATABASE "test_1675239947937354435" Start time of first test: 2023-02-01 08:25:03.633 UTC Finish time of last test: 2023-02-01 08:26:13.861 UTC 82 tests, or 0.856 seconds per test (sequentially). In parallel, we take 6.941 seconds for 82 tests, or 0.085 seconds per test.
      • kakwa_ 4y ago
        We apply the overall same strategy (individual DB for each test created from a template). Our whole test suite (integration + unit tests) takes ~80 seconds to run for ~800 integration tests (each with their own DB) and 300 unit tests. And that's on my dev laptop (T14s, cpu: i7-1185G7) without much optimization (mainly fsync = off in postgresql.conf). In fact, I just ran a quick test, and just putting the DB on a tmpfs cuts that time to ~40 seconds. So overall 0.1 to 0.05 second per test on average, same ballpark as parent (and it's kind of an over estimation actually since we have a dozen or so of slow tests taking 5 to 10 seconds). Note that the tests are run in parallel however.
• kakwa_ 4y ago
  We do that too, in fact it's not only the DB that run in docker, but the whole build + CI process. Our overall strategy is to create a master "test" DB with a test dataset, and for each test, copy this master DB to a test specific DB (CREATE DATABASE <testdb> TEMPLATE <master>) so that tests can run in parallel without interfering with each other and without the significant overhead of a "from scratch" DB initialization. For schema migrations, we build the "branch/PR" version and the "main" version, then we check that 'init DB with "main" + migration with the "branch/PR" version' results in the same schema as 'init the DB directly with the "branch/PR" version' using apgdiff. This strategy could probably be extended to migrating from every older version by building each tag, but we don't have that need. We could also probably improve checks on the data itself however as for now, we only check the schemas. Few things to note: * it's still possible to run the tests outside of docker and use a local DB instead with some light setup (it's faster than running everything in docker when developing) * docker argument --tmpfs <dir/of/db> is quite good, assuming you have enough ram for your dataset * few configuration tweaks on the DB, like max connection might be necessary. Overall, we are quite happy with this setup as it permits to implement end to end integration tests quite easily without spending too much time mocking dependencies. As a general pattern, I find instantiating dependencies internal to your service (like a DB or Queue) to be the way to go, with mocking only for external dependencies (like external APIs) or exceptionally to reach a specific code branch (specially error handling sections).
• totalhack 4y ago
  Same. That's one of the benefits of being able to replicate your environment with something like docker compose and then use a tool like alembic to manage migrations both locally and in production.
• crabbone 4y ago
  This may work for something simple, but a typical database cluster setup will be impossible / impractical to try to emulate in containers because you'd need to configure a lot of things not normally available inside containers (s.a. how storage is attached, how memory is allocated). Since OP mentioned DBT (kind of weird, hopefully, it's at least DBT2, since DBT is very old), they mean to test the productivity of the system rather than correctness of some queries (typical tests that deal with workloads similar to DBT2 are, eg. pgbench). Running pgbench over a database setup in a container will tell you nothing / might just confuse you, if you don't understand the difference between how database is meant to be setup and what happens in container.