Local Integration Testing

[amerryma]

Hi, I'm working through a strategy for integration testing of a local Supabase instance. My integration suite is written with Jest and it uses the node pg driver to connect to the database and run assertions. It takes care of seeding and resetting the database. Right now my tests essentially use the Supabase CLI to start Supabase.

My problem comes from the fact that the Supabase CLI can only interact with a single database at a time, so if I want to run integration tests, it will affect my local development environment. I want to explore options where my integration tests don't need to worry about dealing with a shared database.

Options I've considered:

1. Run Supabase within a docker container (docker in docker). This way I can run multiple Supabases on the same machine pointing to the same local directory. Not sure if this is entirely feasible.
2. Create a different directory for supabase-cli to run in, and modify the config.toml to use different ports (symlink to share migrations, etc from the main project)
3. Just share the db between integration tests and local dev. All data generated with the integration tests should be tracked (prefix or something) so that I can delete them after the tests are done.
4. Migrate everything to pgTap. This really isn't what I want to do as I like testing the supabase system in it's entirety using the supabase js client. I'm focused on full integration testing not just unit testing the database.

I've seen the discussions here and wondering if this is still the preferred method.

[brahimmouhamou]

Hey Aaron, it has been a while, what did you actually choose and would you still recommend it?

[amerryma]

We went with option 3 for most of my Supabase projects when I want to use the supabase js client to make assertions.

Although, I've written most of my tests just connecting to the db via node postgres and running assertions within a transaction, then roll it back.

[Slyracoon23]

I actually experimented with all the different approaches you, @amerryma, mentioned, and ended up finding the prefix method (option 3) to be the easiest and cleanest solution for my use case.

The prefix approach let me keep things simple.

The keys were:

• Prefixing all test data with something like "test_" to easily identify it.
• Creating a custom RPC function that did a cascading delete of all prefixed data after test runs.

This let me share the database between tests and local dev, while keeping things tidy. Just had to be disciplined about cleaning up after each test run.

In the end, the prefix method was the most straightforward way to get full integration tests running without too much overhead or risk of polluting my local env. Still requires a custom RPC function that needs to be modified to the size of affected tables. Also, it must maintain the order in which they reference each other.

Still best possible solution.

[devsheva]

This seems a good solution, but what if you have a method that does a simple select? How will you exclude the "non" test prefixed ones from being affected? Imagine you're doing a bulk update inside that method

[amerryma]

So, one simple option I thought of was just simply changing the project name within config.toml. It will give you an entirely separate database and docker images.

[devsheva]

What I did is creating a different test schema, so you have a completely different dataset while using the same project DB