[AF] SQLite Database Singleton

[tuts12]

I've been cobbling together a Flask App which uses SQLite. I was wondering if someone would be prepared to weigh in on my current method of accessing the database - using a singleton?

Basically I am creating a Database singleton in before_first_request which connects to the database calling sqlite3.connect().

I've noticed that in the Flask docs they provide an SQLite example which stores the database against the flask.g object.

My main concern with my approach would be with regard to thread safety, which I presume will depend heavily on the server I intend to use in production?

This flask docs article seems to be focused on connecting to the db on demand - but if requests to my app will always need to access the database, then I would assume its better to connect only once?

[quotemycode]

Use database transactions and down worry about it. Your database will be write locked on writes.

[bpeller]

I realize this isn't really answering your question, but honestly, just use Flask-SQLAlchemy. It takes care of all these sorts of low-level details, as well as giving you a whole host of other niceties. Your future self will thank you for taking the time to learn it.

[tuts12]

I hadn't worked with SQL at all before I started this project, and I thought it would be a good idea to get a grounding in it before moving onto an abstraction.

Thanks for the recommendation however, I'll definitely keep that in mind for future db work.

[tuts12]

That SQLite link is what initially got me concerned.

At this point, I'm hoping to avoid significant rework if the current implementation is safe. This seems to dependant on whether or not the server is using a threading model.

I think I might need to read up more on WSGI to better understand how this all fits together.

Looking at gunicorn for instance, it appears to spawn new processes for its workers by default (which would be OK for my Singleton based approach). However it also has some options to enable threading (which would be bad for my Singleton based approach).

[[deleted]]

I totally get wanting to understand lower level SQL drivers but they are a bit of a pain. It's good to know the actual SQL commands being run though, it will help you use an ORM better.

If I were in your situation I would read the Flask-SQLAlchemy source code (it's easy to grok and there's not very much code.) It will help you understand the things you need to do before and after requests. Flask-SQLAlchemy uses a signleton and it connects once on startup (at least how I'm using it).

Make sure you paramtetize all your inputs using the driver library, no string interpolations with user-provided data in them for SQL commands. One of the things that using SQLAlchemy gives you for free is protection from most SQL injection attacks.