Testing with Database Fixtures in Jest (TypeScript)

Database fixtures are essential for reliable and repeatable testing. This challenge focuses on creating and utilizing database fixtures within a Jest testing environment using TypeScript. You'll be responsible for setting up a simple database (using an in-memory SQLite database for simplicity) and populating it with predefined data before each test, ensuring consistent test conditions.

Problem Description

You are tasked with creating a utility function that sets up database fixtures for your Jest tests. The function should:

1. Initialize an in-memory SQLite database: Use better-sqlite3 to create an in-memory SQLite database.
2. Execute SQL statements: Accept an array of SQL statements as input. These statements will be used to create tables and insert data into the database.
3. Ensure data consistency: The function should execute the SQL statements sequentially, ensuring that tables are created before data is inserted into them.
4. Provide a cleanup mechanism: The function should return a cleanup function that, when called, closes the database connection. This is crucial to prevent resource leaks and ensure tests run independently.
5. Handle errors gracefully: The function should catch and log any errors that occur during database initialization or SQL execution.

Expected Behavior:

The utility function should successfully create and populate the database with the provided data. The cleanup function should properly close the database connection. Tests using this fixture should be able to reliably access and query the data.

Edge Cases to Consider:

• Invalid SQL statements: The function should handle invalid SQL gracefully, logging the error and potentially preventing further execution.
• Database connection errors: Handle potential errors during database initialization.
• Concurrent test execution: Ensure the fixture setup and cleanup are thread-safe (though in-memory SQLite generally handles this well).
• Empty SQL array: The function should handle an empty array of SQL statements without errors.

Examples

Example 1:

Input: [
  "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);",
  "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');",
  "INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');"
]
Output: {
  db: SQLite3.Database, // An instance of the in-memory SQLite database
  cleanup: () => void // A function to close the database connection
}
Explanation: A database is initialized, a 'users' table is created, and two users are inserted. The returned object contains the database instance and a cleanup function.

Example 2:

Input: []
Output: {
  db: SQLite3.Database,
  cleanup: () => void
}
Explanation: A database is initialized, but no tables or data are created. The returned object contains the database instance and a cleanup function.

Example 3: (Error Handling)

Input: ["CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);", "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');", "SELECT * FROM users WHERE NOT EXISTS;"] // Invalid SQL
Output: {
  db: SQLite3.Database,
  cleanup: () => void
}
Explanation: The database is initialized. The first two SQL statements are executed successfully. The third statement is invalid and an error is logged. The returned object contains the database instance and a cleanup function.

Constraints

• Database: Use an in-memory SQLite database for simplicity. better-sqlite3 is recommended.
• SQL Statements: The input SQL statements should be strings.
• Performance: The fixture setup should be reasonably fast (under 100ms) to avoid slowing down test execution.
• TypeScript: The solution must be written in TypeScript.
• Dependencies: You are allowed to use better-sqlite3. No other external dependencies are permitted.

Notes

• Consider using try...catch blocks to handle potential errors during database initialization and SQL execution.
• The cleanup function is crucial for preventing resource leaks. Always call it after your tests are complete.
• Think about how to structure your code to make it reusable and easy to maintain.
• The SQL statements are assumed to be valid and safe for execution in the context of your tests. Sanitization is not required for this challenge.
• Focus on the core functionality of setting up and cleaning up the database fixture. Complex data modeling or query logic is not required.