Is it okay to always leave a database connection open?

I would say it's fine in this case, since there will only ever be one user and the database is hosted on the same machine (more likely in the same memory space, as I think SQLite just loads as a DLL with the main application) as the application. Constantly opening and closing the connection is unnecessary.

One possible exception might be if you need to have multiple threads of your application accessing the database at the same time. Then you could either force them to wait and share a single connection object, OR you could try to create new connections for the different threads. I have never actually tried this in SQLite. This is one situation where closing the main connection and opening/closing multiple connections might be better for a desktop app.

For web applications, or client/server desktop apps, I'd suggest against leaving connections open.

Connection pooling should make this a moot point. The pool should keep a connection around and open for you to reuse. This should allow you to follow the generally best practice of using resources for the shortest reasonable amount but without sacrificing performance.

Imagine you have 1000 users accessing your application at the same time. That would means 1000 open connections. Eventually you could run out of connections. So let each user open a connection, use it, and then close it so that connection is free for others to use.

Further Clarification

Imagine him having multiple modules that would simultaneous need the same connection? Image simultaneously running controls needing the connection. What is he going to do? Have a global connection object? Use a Singleton Pattern? Tell me if I am wrong

Typically the connection is closed after use; freeing it back into the pool of available connections. If there are a high number of transactions taking place on a single client it makes sense to leverage a single connection instead of creating multiple connections only to immediately close them.

It is somewhat circumstantial however the typical best practice is to close it after use so that it becomes available within the pool again.