Question 315228 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Is a good idea to create multiple databases for testing?

I have an application that connects to an API and performs some writes and reads in a database. When creating the integration test suite, I create an empty database and for each test, I create the schema (mirroring the production database), add the data required for the test, perform the test, and at the end, clean everything up. This approach worked well for a while, but issues arose when developers ran tests, resulting in tests creating or deleting data that was being used by other developers in their test runs. How can I solve this? Should I create a lock to access the test database? Should I create a new database for each test?

App Stack: Streamlit – API Gateway – AWS Lambda – MySQL running on AWS RDS.

More info: When running the tests, they are executed with an environment variable that changes the API endpoints. As a result, the database connection used by the app points directly to the test database. The parameters for the test, development, and production databases are stored in AWS Secrets Manager

Tags:

2

Answers


  1. 0

    Absolutely! Handling test interference in a separate database setup can be tricky, but there are some practical ways to deal with it:

    • Database Transactions: Think of it as an ‘undo’ button for tests. It
      rolls back changes after each test, keeping the database clean.
    • Snapshots for Reset: Taking a ‘before’ picture of the database and restoring it after each test ensures a fresh start every time.
    • One Test, One Database: Providing each test its own database space avoids clashes and keeps things organized.
    • Database Lockdown: Implementing a lock system prevents multiple tests from stepping on each other’s toes by allowing only one test to
      access the database at a time.

    Pick the method that suits your needs best to maintain tidy tests and a well-managed database!

    Login or Signup to reply.
  2. 0

    LVM and snapshots.

    That lets you create a complete copy of a filesystem (containing all the database and nothing else). Then you do testing with this copy and blow it away.

    Such cloning takes a minute or so, even for a very large dataset.

    You can have any number of such copies live at any time — so different developers can run tests independently.

    Each snapshot takes a small amount of disk space. As the main dataset is being modified and/or the copy is being modified, the disk space grows. (Read about "copy-on-write".)

    When finished with a test, dropping the snapshot is also virtually instantaneous.

    I doubt if AWS can provide LVM snapshots, but you could ask. If not, then periodically (say, weekly) download a dump of the data for setting up as many LVM clones as fast as needed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search