testing with per tenant databases

[James4645]

Recently I've been wanting to be more purposeful about testing my app. Up til now I've had a few tests that I usually don't run. Last time I ran them everything works fine. Now I get all sort of database errors causing tests to fail.

I have a multitenancy app using this package and a unique db per tenant. I had a custom setup that would prefix all my database names with the enviroment so i have local_ and testing_ for each database on my machine. I had a custom setup method thst would migrate fresh the testing_landlord database before each test. And each test would create a tenant or two that it needed. Then if my tearDown I would iterate though each tenant record found in my landlord database to run my Job to delete the tenant db.

As you can imagine the process was slow. I'm talking about a minute just to run 22 tests. But it used to work. Now I have some jobs failing to delete the tenant db saying it's in use which means the following test fails to create it. It's a mess.

I did some reading and came to the conclusion I'd be better off having 2 or 3 tenants that are perpetual. So I would have a onetime setup at the beginning of the suite that would setup the testing_landlord database migrate fresh to it and create the tenants needed for all the tests. Then each test could use transactions to rollback their change each time to the inital state.

Problem is I'm not sure how to achieve this. AFAIK Laravel's trait to use transactions only seems to work on the default connection (the landlord database). And even if I could get it to work accross connections I only technically have 2 connections and the multitenancy package is swapping out the db in the second connection to the db for the tenant assigned to the currently authenticated user.

Any ideas on how to properly reset the world for each test when using this with per tenant databases?

[Muffinman]

I'm currently trying to achieve something very similar, did you ever find a clean way to handle this?

It's kind of a pain that sqlite doesn't support switching DBs, as I'd rather keep all this testing stuff out of MySQL entirely.

My idea was to implement my own TenantAwareRefreshDatabase trait (extending RefreshDatabase), for the purpose of testing, without messing with my local data already installed into MySQL. The idea being that I would just start a transaction on each connection BEFORE it ran any refresh commands, and they would roll themselves back at the end leaving my local DBs in the initial states.

I haven't gotten to the bottom of exactly why this doesn't work, but I think it's something to do with switching tenants while a transaction is in progress.

[James4645]

Sort of. The key was to discover you can use transactions on multiple connections not just the default using the property connectionsToTransact. Then I wrote a custom bootstrap that I setup to use in my phpunit.xml it calls vendor/autoload.php, boots up the laravel app to call migrate:fresh on both the landlord and a tenant DB as well as a Testing Seeder for the landlord database, which will suppress the event that I use to trigger the creation of a tenant db but add the Tenant record to the landlord DB.

The limitation of this is you only have 1 tenant to use in tests (though there may be a way around this). But I find that's good enough for virtually all tests. And most everything I can think of that would need multiple tenants to test (at least in my app) can be done at the Unit level with no DB calls. The one notable exception is the test for the happy path for registering which creates a new (second) tenant. But i have that test finish by calling a job that deletes a specific Tenant's DB for the tenant created by that test. I use that job in production to delete a Tenant DB when a tenant closes their account so it also confirms that job works too. That test is a bit slower than normal tests because of creating a deleting a DB but its only 1.

Now it sounds like you are trying to use the same DB for your local environment as for testing. I promise you it'll be much easier if you don't. Just setup dedicated testing DBs. Have a setup script to create the DB's that you run once when you setup the app on a new system then override the db names in phpunit.xml for when tests run to use those.

[kassah]

Using MySQL we ran into issues around MySQL silently dropping transactions when any table or db creations happened. I worked around this with these steps

A quick description of those steps are basically to create a test database service that pre-sets up a few tenant databases with migrations in place. It hooks this via phpunit Bootstrap and ParallelTesting events. For it to work with tests that deal with multiple tenants, you need to use a SwitchTenantDatabaseTask that effectively does a USE statement instead of reconnecting (reconnecting drops transactions, breaking RefreshDatabase traits expectations). For all this to work, you need to override your database creation code when a tenant is created, and instead using a pre-built, pre-migrated testing database.

[valh1996]

Thanks a lot, it sounds interesting! I just need to start setting up tests, I feel like it will help me a lot... thanks!

For the time being, even if the package is meant to be as simple as possible, I think it could be nice to bring built-in support for tests.