Cookbook: using the database in a test

[Burgov]

I'd like to see a cookbook article on how to use Database interaction in a Test without using the actual database. For example set up a sqlite connection and load some default tables and fixtures into in before every test. I have no idea how to do this in a correct way

[weaverryan]

I think the deliverables on this should be to:

a) Check that we're advising people that they should customize their test database settings by overriding things on config_test.yml

b) Showing how you might load some basic setup data before a test (perhaps using setUp), though we should advise against over-doing this. Imo, if you need data in a test, you should create it in your test to avoid loading heavy fixtures all the time.

[Sgoettschkes]

@weaverryan This totally depends on what you are doing. We e.g. use MySQL in production and SQLite in our tests. Before we execute any tests that need the database, we create the database once (including fixtures) and then save the sqlite file to a backup location. Everytime we need a clear database we move the sqlite backup to the original location and have a fresh database. When executing 1000 tests, this is way faster than writing fixtures for each test.

One thing I commonly do in unit tests is mock the entity manager & repository so there is no database involved at all. I could think the structure of the cookbook as follows:

• Unit Tests (describes mocking of the em and repo)
• Functional Tests (both describing the usage of config_test.yml, maybe with sqlite, and how to clear the database)
• Fixtures (maybe describing more than one way on how to do things)

[wouterj]

It's a best practise to use the same database in testing and production. That way you are testing what you are going to use.

[Sgoettschkes]

@wouterj I guess but it works for us. That's no reason to add it to the docs thought.

@weaverryan Regarding your 2: What's the best practice to ensure the database is in the correct state? For one of my silex projects with the doctrine mongodb odm, I did something like this:

$schemaManager = $documentManager->getSchemaManager();
$schemaManager->dropDatabases();
$schemaManager->createDatabases();
$schemaManager->createCollections();
$schemaManager->ensureIndexes();

// load fixtures

I usually try to build the database from the ground to ensure that 1) the schema matches the current code base (this needs to be done once for every execution of the test suite) and 2) that no data which is in the database gets in the way.

My questions regarding the loading of fixtures are:

• Should the cookbook show a way to update the database schema, and if so, in which way (drop/create Databases or just updateDatabases)?
• Should the cookbook go into detail about the correct state of the database?
• Should the database use fixtures or rather create entities on the go?

[weaverryan]

@Sgoettschkes good questions :). I tend to think that dropping the whole database is too extreme, however you do end up with potential foreign key problems when emptying out data. So, my recommendation is just to lightly show how a functional test that uses the database would look, and also an example of simply how you can manually clear out the entries in a table beforehand. For example, suppose we're querying for Posts. Clearly we need to empty the Post table and then add 3 posts before testing that we see 3 posts. I think this is enough. We can also add a note about how controlling data is very important, and that you may consider clearing out all your data or a subset of data automatically before each test. We can show this via the setup method.

I've merged in #2407 to get most of this issue closed - so we can just open up a new PR for this stuff.

Thanks!

[weaverryan]

Oh, and if people have strong opinions on clearing of data before each test, then we can discuss that in more detail on the PR.

Cheers!

[ricardclau]

This is always a tricky one... from my previous experiences what we did was to use some development database instead of using sqlite. The reason is that all database engines work slightly different, specially if you use things like Postgre with the array types.

And then in each test file, we used the static setupBeforeClass method to reset some database records to a known state and cleaning it in the static teardownAfterClass. So cleaning on every test file (which could be a full controller for instance) but not on every test itself

I know this violates many principles of unit testing (in fact these are functional tests with a headless browser) but this has proven to be reliable in the real world :)

Just my 2 cents!

[wouterj]

See also #4049

@ricardclau great to see you back in commenting on our older tickets!

[ricardclau]

@wouterj just catching up, back from the dead :)

[weaverryan]

@ricardclau I think that's exactly the type of setup that we can "show" people - I think it's very pragmatic (and a difficult problem to solve in reality).

[ricardclau]

Ok I will work on a cookbook around this topic @weaverryan @wouterj

We will need to be clear about the tradeoffs and pragmatism so that people can understand our point and maybe not pick everything but just some of the ideas presented

[javiereguiluz]

@ricardclau Ricard, do you think you'll have time in the coming days to take care of this? If you don't have time, could it be possible to get an outline of your original ideas about this cookbook so we can write it? Thanks for let us know!

[ricardclau]

Well, this has always been a hot topic between testing purists and pragmatic people :)

Back in the day, my idea was something like (I know it is a bit vague):

• Start with some paragraphs explaining the different options, why it is a good idea to setup the same database as the one in production, mention that this is mainly a functional test, etc
• Show some config_test.yml example overriding your production settings
• Show some setup / teardown methods creating tables / fixtures and cleaning them after the test (we should also talk about where is the best place to do this, if in setupBeforeClass or in setUp)
• Think about a not so dumb example, which should involve a controller + repository + entities or even more files if we want to do it properly. Or maybe a simple CRUD is enough?

Even though I am not working with PHP these days, I will have some spare time during the Christmas period to work on this but it is a significant piece of work and would like to have some agreements / guidelines before starting with it.

We should also check the differences between 2.3 and newer versions (if any) in order to create slightly different content in each branch

Thoughts / comments @javiereguiluz @weaverryan @wouterj ?

[redstar504]

I'm working on this and will post a draft today. I've got lots of experience testing repositories.