How to correctly use database

[Thank You]

I have asked many questions related to database where I got many important information.
Now I want to get your thoughts on how to manage database connections properly for better speed security. I will list my questions below:

• How many connections should I create in application(application with atleast 10-20 pages) which means 60 different connections(or 60 diff queries). I guess @JonB had said about it a bit. But
  • Should I create one main connection and use it for all queries?
  • It consists of 10 tables(tableview or model NOT of database) for which now it consists different connections
• I am currently opening database in constructor and removing in destructor. Is it good way to do this?

myClass::myClass(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::myClass)
{
    ui->setupUi(this);
    filledTable = QSqlDatabase::addDatabase("QMYSQL","filled-table");
// filledTable is created as private member of this class

    filledTable.setDatabaseName(databaseName);
    filledTable.setPort(databasePort);
    filledTable.setHostName(databaseHostname);
    filledTable.setUserName(databaseUsername);
    filledTable.setPassword(databasePassword);

}

myClass::~myClass()
{
    QSqlDatabase::removeDatabase("filled-table");
    delete ui;
}

And supose if I have to connect database for any other purpose I am using different connection. But In my thought I should create two connections for page One for filling tables and another for filling all datas except tables

https://forum.qt.io/topic/127056/using-model-to-fill-tableview-with-qmysql/
This is one which I had solved using the same concept

Or Should I create a single connection for whole application and use it for every task

Btw, I am using MYSQL as database

[jsulm]

@Thank-You said in How to correctly use database:

Should I close the connection in destructor?

Close it when you don't need it anymore. Destructor is one place where you can do it.

"And I have the connection in first" - you do not have it in first. A connection is dedicated to one thread and you can use it everywhere in that thread (in your case in UI thread). So, you can use it in first, in second and whereelse in your UI. You can call QSqlDatabase::database() everywhere in your code to get the connection.

[jsulm]

@Thank-You said in How to correctly use database:

which means 60 different process

What do you mean by "process" here? One running application is one process, doesn't matter how many "pages" (whatever you mean by that) it has. Do you maybe mean threads instead of processes?
There is absolutely no need to have one connection per table. A connection is for one database, doesn't matter how many table the database has.
You also should not store the database connection in variables, this is explained in documentation:
"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."
I'm reffering to this:

filledTable = QSqlDatabase::addDatabase("QMYSQL","filled-table");

[Thank You]

@jsulm
Yes Sir, I have corrected it

What do you mean by "process" here?

Maybe query would make sense. Sorry, I don't mean threads

There is absolutely no need to have one connection per table.
I mean tableview not database table, Sorry to have vague question.
You also should not store the database connection in variables, this is explained in documentation:
No these variables are used as private variable , So I can't access from others.
If that is the problem , What techniques should I use it

https://forum.qt.io/topic/127056/using-model-to-fill-tableview-with-qmysql/21
I got some concepts from this post points

• Don't remove the database while you have a view/model using it.
• Don't re-add the same database while you have previously added it and not yet removed it.

[jsulm]

@Thank-You said in How to correctly use database:

No these variables are used as private variable , So I can't access from others.
If that is the problem , What techniques should I use it

Why do you think you need these variables? How to use a database connection is explained and shown in documentation (https://doc.qt.io/qt-5/qsqldatabase.html):

// Initialize connection and open:
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
db.setHostName("acidalia");
db.setDatabaseName("customdb");
db.setUserName("mojito");
db.setPassword("J0a1m8");
bool ok = db.open();

// Later somewhere else use the connection:
QSqlDatabase db = QSqlDatabase::database();

So, you simply call QSqlDatabase::database() to get the connection by name you want to use. There is really no need for any variables, private or not.

[Thank You]

@jsulm

// Later somewhere else use the connection:
QSqlDatabase db = QSqlDatabase::database();

Wow <3
It is absolutely new concept for me. I haven't seen this before , Amazing
Some simple question,
Should I close the connection in destructor?
What if I have two widgets

• first
• second
  And I have the connection in first , Can I use the same connection in second widget If both of them are two non related separate files

Then I should never put database connection in private/public variables. just create connection in constructor and use it as you have mentioned in answer . Just close the connection in destructor.
And one connection is perfect for one database, There are no need of different connection on different widgets

[jsulm]

@Thank-You said in How to correctly use database:

Should I close the connection in destructor?

Close it when you don't need it anymore. Destructor is one place where you can do it.

"And I have the connection in first" - you do not have it in first. A connection is dedicated to one thread and you can use it everywhere in that thread (in your case in UI thread). So, you can use it in first, in second and whereelse in your UI. You can call QSqlDatabase::database() everywhere in your code to get the connection.

[Thank You]

@jsulm

Thanks it helped a lot