ticdc: Difficulty in Mocking Database Connections in Unit Tests

[wlwilliamx]

Before asking a question, make sure you have

• Searched existing Stack Overflow questions
• Googled your question
• Searched open and closed GitHub issues
• Read the documentation

What is your question?

Description:

During the process of creating a connection to a downstream database using a Sink URI, two distinct connections are established:

1. Temporary Connection: Initially, a temporary connection is created to query important information from the downstream database, such as version, charset, and other details. Once this information is retrieved, the temporary connection is closed.

2. Standard Connection: The information gathered from the temporary connection is used to populate additional parameters into the Sink URI. This refined Sink URI is then used to create the standard connection that the Sink will use for subsequent operations.

While this process of creating two connections works perfectly fine in normal system operations, where the same method can be used to establish both connections, it presents a significant challenge in unit testing. In unit tests, we cannot start an actual downstream database; instead, we need to mock the database connections. Since both connections will execute SQL queries, the unit tests require mocking two different connections to handle each phase of the connection creation process.

The current approach, where both connections are created using the same method, falls short in unit testing scenarios. This limitation has led to the use of highly tricky and complex workarounds in unit tests, as shown in the example below:

dbIndex := 0
mockGetDBConn := func(ctx context.Context, dsnStr string) (*sql.DB, error) {
	defer func() { dbIndex++ }()

	if dbIndex == 0 {
		// test db
		db, err := pmysql.MockTestDB()
		require.Nil(t, err)
		return db, nil
	}

	// normal db
	db, mock := newTestMockDB(t)
	mock.ExpectClose()
	return db, nil
}

Proposed Solution:

To address this issue, we can leverage an interface to manage the creation of these connections more effectively. By defining an interface with two methods—CreateTemporaryConnection and CreateStandardConnection—we can implement different strategies for creating the temporary and standard connections. This approach allows us to mock and test the connections separately, reducing the complexity and improving the maintainability of our unit tests.

This issue aims to implement such an interface to simplify unit testing by providing distinct methods for creating the necessary database connections.