QSqldatabase "correct" usage

[sairun]

I've been using QSqlDatabase as a member of a class in many small programs that I develop for personal projects, like

class DbManager {
    DbManager();
    ~DbManager();
    ...
  private:
    QSqlDatabase connection;
    ...
}

These programs are meant to use mostly SQLITE3 databases and they often need to open a single database during their execution.

Recently, it came to my attention that in Qt documentation there is a warning stating that "It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database(). If you chose to have a QSqlDatabase member variable, this needs to be deleted before the QCoreApplication instance is deleted, otherwise it may lead to undefined behavior."

So I suppose that it's OK to initialize the "default" database connection in main

int main(...) {
  
  QApplication app( argc, argv );
  
  QSqlDatabase connection = QSqlDatabase::addDatabase( "QSQLITE" );
  
  MainWindow w( nullptr );
  w.show();
  return app.exec();
}

and then use functions such as QSqlDatabase::database() or QSqlDatabase::database("conn_name") to get a handle to the current connection. With this handle one would use open() and close() to change the underlying database. However, I haven't seen this approach in any of the examples I come across in Qt forums. What I found - and is quite widespread - is precisely the approach not recommended by the official documentation!

As far as I can tell both approaches work. Should I revert to the recommended one? Why?

[JonB]

@sairun
Exactly as you write --- do not maintain any QSqlDatabase connection instance around as e.g. a class member or global variable. Local variable is OK. use static QSqlDatabase::database() to retrieve a reference to the instance you created via addDatabase().

The reason is to do with the shutdown/destruction of QSqlDatabase stuff not wanting some other reference to be lying around. In your code you have an instance variable whose scope extends to the end of main()/after the app.exec() has exited. I am unsure whether this is an issue: it may be that your instance should not outlive the app.exec(), depends whether the clear up happens when that exits or, say, when the QApplication app goes out of scope. Which may happen here after the QSqlDatabase connection is first destroyed and hence be OK. Or you might move the QSqlDatabase connection = QSqlDatabase::addDatabase() into, say, your MainWindow class.

[sairun]

Thanks! I think I'll adopt the "new" pattern.

[SGaist]

Hi,

In addition to @JonB, most of the times, people only use the default connection so all their use case are already covered by how the API has been implemented (i.e. the QSqlDatabase parameter is set to the default connection).

[sairun]

I'm now defining the connection on the main function as

int main( int argc, char *argv[] ) 
{
    QApplication app( argc, argv );
    
    QSqlDatabase db = QSqlDatabase::addDatabase( QSQLITE );
    
    if( db.isValid() ) qDebug() <<  db.isOpen();
    
    MainWindow mw( nullptr );
    mw.show();
    return app.exec();
}

After the creation of the connection db.isOpen() returns false in the code above. The connection is valid but no database is yet opened. This is as expected. Now the user can open a database or create a new one through the GUI (with File->Open or File->New). However, at least for the case of external SQLITE3 databases, there is no way to know if a connection to a given database is already established except for the db.databaseName() method, which returns an empty string if no database was selected.

In a method inside the GUI fired by File->New action

bool MainWindow::newDatabase( fileName )
{
  QSqlDatabase db = QSqlDatabase::database();
  
  qDebug() << db.isOpen() << db.databaseName();
  
  db.setDatabaseName( fileName );
  
  if( db.open() )
  {
    ...
  }
}

the method db.isOpen() returns true even though no database has been created yet! Why? The db.databaseName() returns the empty string as expected. So the only way to know that there is a database already opened is through the name of the database in the connection (empty means not opened)?

I'm asking this because a user may want to create a new database while using an already created one. In this case, the program needs at least to close the current database. But can it keep the current connection, or should it destroy it? Can it use db.close() followed by a db.setDatabaseName( fileName ) or do it need to do a db.removeDatabase() and start with a new db.addDatabase(QSQLITE), etc? In the former case, the database connection would still be the one instantiated in main(), whereas in the latter the new connection would be created outside main().

[Christian Ehrlicher]

@sairun said in QSqldatabase "correct" usage:

QSqlDatabase db = QSqlDatabase::addDatabase( QSQLITE );

if( db.isValid() ) qDebug() <<  db.isOpen();

What should this help in the fiorst place? There is no need to do something like this when you don't want to open a database. Open the db where you know all things.

[sairun]

@Christian-Ehrlicher

I guess You did not understand my question. The line

if( db.isValid() ) qDebug() <<  db.isOpen();

is just a debug line that I inserted in the code to better understand the meaning of dn.isOpen(). In the Qt manual, you can read that bool QSqlDatabase::isOpen() const

Returns true if the database connection is currently open; otherwise returns false.

This line of code returns "false" which is what is expected. However, why does it return "true" before any established connection with a SQLITE file, as in the second code snippet above (MainWindow::newDatabase)? Because at that stage in the program, nothing has changed regarding the database connection. It is a valid connection (meaning the SQLITE driver is installed) but no database file has been selected yet. So db.isValid() should return "true" (which it does) but db.isOpen() should return "false" (which it does not).

[Christian Ehrlicher]

"SQLite also supports in-memory and temporary databases. Simply pass respectively ":memory:" or an empty string as the database name."

Since you did not provide a db name, an in-memory db is created as written in the documentation: https://doc.qt.io/qt-6/sql-driver.html#qsqlite-for-sqlite-version-3-and-above

[sairun]

@Christian-Ehrlicher

I don't want in memory databases. I want named SQLITE databases that can be portable. The user should provide a new file name or an already existent file name. Since he/she can use/create several databases during one session, I have to know when a file database is being used to close it before opening/creating the following one. But I can't use any of the methods of QSqlDatabase - except for the fileName() - to infer that a file is being used. As I see it now, db.isValid() and db.isOpen() are redundant.

[Christian Ehrlicher]

@sairun said in QSqldatabase "correct" usage:

As I see it now, db.isValid() and db.isOpen() are redundant.

Only for sqlite for the reasons wrote above.

[sairun]

@Christian-Ehrlicher said in QSqldatabase "correct" usage:

Only for sqlite for the reasons wrote above.

I see what you mean! Still, there are things that don't make sense in all this.

It took me a while to reply to you because my post was being flagged as SPAM. I've put the relevant code in PASTEBIN if you want to replicate it. The example in PASTEBIN is very simple but it explains what I want to to achieve. I'm not saying that it is correct, but it works as expected.

During a single run, you can create or open several SQLITE databases. Each time you create one database it inserts a random number in a record. Every time you open it again it inserts another number. The only thing that does not work is overwriting an existent file. Other than that the behavior is exactly what I want, but I'm not sure if it's leaking stuff. Valgrind says that there are probably some lost memory blocks (still reachable) but that's normal with any Qt executable.

Now what puzzles me (related to my original topic) is that if I replace

QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );

with

QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" , "connection" );

in main.c and

QSqlDatabase db = QSqlDatabase::database();

with

QSqlDatabase db = QSqlDatabase::database("connection" );

in void MainWindow::newFile() and void MainWindow::openFile() (both in mainwindow.cpp) the program stops working! It throws the following error:

qt.sql.qsqlquery: QSqlQuery::exec: database not open

I was expecting no problems when using named connections. Somehow the code fails to open the databases that it created or fails to create new ones. The "default" connections works.

[Christian Ehrlicher]

Please provide a minimal, compilable example of your problem. I don't know what you are doing or what you are trying to achieve...

[sairun]

This post is deleted!

[sairun]

Hi @Christian-Ehrlicher

You may have missed it, but I did provide the code for a simple executable in my previous post! The thing is that the original content was flagged as SPAM and I had to move the code to a link in PASTEBIN. Maybe that's the reason you didn't pick it.

[Pl45m4]

@sairun said in QSqldatabase "correct" usage:

QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );

I don't understand why you want to call this in main.cpp at all... since you don't do anything with the local db variable there...
So why adding a database there and probably messing up the rest of the workflow?

As said before you should only access the currently opened db using for example the static QSqlDatabase::database() locally.

When working with multiple connections, you can pass the connection (connection name) you've specified when adding/creating the database/connection.

QSqlDatabase db;
QSqlDatabase::addDatabase( "QSQLITE", "my.db" );
QSqlDatabase::addDatabase( "QSQLITE", "mySecond.db" );
// db operates on "my.db"
db = QSqlDatabase::database("my.db");
db.open();
db.close();
// switch to second.db
db = QSqlDatabase::database("mySecond.db");

[sairun]

@Pl45m4 said in QSqldatabase "correct" usage:

@sairun said in QSqldatabase "correct" usage:

QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );

I don't understand why you want to call this in main.cpp at all... since you don't do anything with the local db variable there...

This probably comes from my ignorance in using the QSqlDatabase object. In the Qt Manual one can read

Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around
as a member of a class, as this will prevent the instance from being correctly cleaned up on
shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database().
If you chose to have a QSqlDatabase member variable, this needs to be deleted before the
QCoreApplication instance is deleted, otherwise it may lead to undefined behavior.

As far as I could understand, you create an instance of the connection with that statement in main. It also allows you to check if the QSQLITE3 driver is available. If by some reason it is not installed or available the program quits. Other than that, I've provided a minimum compilable demo to show what I want to achieve in PASTEBIN. The program allows one user to create "databases" (by this I mean sqlite file based databases) on the fly. Each database gets a record with a random number upon creation or when susequently reopened. What I'm not sure is that whenever you replace an already established connection with a new one for a recently opened file the program leaks something! Other than that it works as expected. The only thing it does not do is to overwrite an already created file (but that I know how to fix).

[Christian Ehrlicher]

You should close the db instance before creating a new one

QSqlDatabase::removeDatabase("DEFCON");
QSqlDatabase db = QSqlDatabase::database("DEFCON");

Otherwise you will get runtime warnings about an already opened connection.

[Pl45m4]

@sairun said in QSqldatabase "correct" usage:

Other than that, I've provided a minimum compilable demo to show what I want to achieve in PASTEBIN.

Yes you said that twice :)
I was referring to your main.cpp from your example.

If connectionName is not specified, the new connection becomes the default connection for the application, and subsequent calls to database() without the connection name argument will return the default connection. If a connectionName is provided here, use database(connectionName) to retrieve the connection.
( https://doc.qt.io/qt-6/qsqldatabase.html#addDatabase)

[sairun]

@Christian-Ehrlicher said in QSqldatabase "correct" usage:

You should close the db instance before creating a new one

QSqlDatabase::removeDatabase("DEFCON");
QSqlDatabase db = QSqlDatabase::database("DEFCON");

Otherwise you will get runtime warnings about an already opened connection.

The funny thing is that with the example I have provided I receive no error or warning messages if I use unnamed connections. If I put an name in QSqlDatabase db = QSqlDatabase::database() the program complains about not being able to open the files already created!

[Christian Ehrlicher]

Because it's already open and you don't close it...