What is the correct way to open and close a database connection for a Qt worker thread

Question

I'm working on a scenario where we want to asynchronously execute INSERT/DELETE statements on a database table (it's a fire-and-forget scenario). I'm planning to simply fire a signal with relevant data, and have the thread's event loop process each signal similar to the following example:

Worker *worker = new Worker;
worker->moveToThread(&workerThread);
connect(&workerThread, &QThread::finished, worker, &QObject::deleteLater);
connect(signalSource, &SignalSource::dataReady, worker, &Worker::updateMyFancyTable);
workerThread.start();

The thread is supposed to have its own database connection opened as long as it is running. I would do it like this (with added error handling, of course):

connect(&workerThread, &QThread::started, worker, &Worker::establishDatabaseConnection);

When stopping the thread, it is supposed to finish its work and then close the database connection.

As far as I understand, calling quit() on the thread will first process all remaining signals (if any) and then exit the thread's event loop.

How do I properly close the database connection in a scenario like this?

1. Do I emit a signal for closing the connection before calling quit()?
2. Do I close the connection in a slot in the main thread which gets called once QThread::finished has been emitted?
3. Something else?

Answer 1

Option 1 does the job, for cases where you want to have the database connection stay open while the thread is alive (your circumstances might be in favor of a per-query connection instead):

Worker *worker = new Worker;
worker->moveToThread(&workerThread);
connect(&workerThread, &QThread::finished, worker, &QObject::deleteLater);

// "SignalSource" is just a placeholder for whatever class sends the signals
connect(this, &SignalSource::dataReady, worker, &Worker::updateMyFancyTable);

// Open the database connection when the thread starts, close it shortly before the end of the thread
connect(&workerThread, &QThread::started, worker, &Worker::establishDatabaseConnection);
connect(signalSource, &SignalSource::endTriggered, worker, &Worker::closeDatabaseConnection);

// Start the thread and do some work
workerThread.start();

Then when the thread is supposed to be stopped:

// Put an event for closing the database connection in the event loop
emit endTriggered();
// Tell the thread to quit as soon as its event loop is empty
workerThread.quit();
// Wait one minute for the thread to finish
workerThread.wait(60 * 1000);

Edge case: If the thread does not finish within a minute, the database connection will not be cleaned up properly. In our case, this is unlikely and acceptable, in your case this might be different.

Additional note: If you use a database connection in multiple threads, use the QSqlDatabase::addDatabase overload which accepts the driver name rather than an existing QSqlDriver* since the driver would otherwise be used from two threads, which is not supported.