Forums

Advising using SQLite

Hello all, I am hosting a Thinkful project called "SomeCode" on PythonAnywhere. It is a snippet service that is from two Thinkful projects (the Python/Flask class and the AngularJS class). The Flask class asked us to use the SQAlchemy ORM on the SQLite RDBMS as the backend DB. Not knowing much about the various SQL db's out there I just went with their suggestion. Turns out you can't host it on Heroku, since they don't support SQLite (only Postgres). SQLite actually works (since the db is just a file on the file system), but as soon as the Heroku Dyno is rebooted/refreshed, the db is gone.

I know that PythonAnywhere supports MySQL. So now I realize that although SQLite is a fine database, choosing a more advanced open-source db like MySQL or Postgres that works through a network socket rather than a file on the file system is the way to go.

My question is - can I still just use the SQLite db and trust that my db file will not get blown away (like it does on Heroku), or do I really need to go with a network oriented db like MySQL? If I can use SQLite, then are there backup schemes that I could use to save away the SQLite file? Or do I just need to get my ORM (SQAlchemy) to talk to MySQL rather than SQLite?

By the way, one of the reasons I like SQAlchemy on SQLite is that is has a nice Flask-WhooshAlchemy library that allows full-text searching on the db models. It's so easy to use. Unfortunately the Whoosh extension doesn't work with Postgres. I'm not sure if the Flask Whoosh extension works with MySQL, so that is why I haven't tried out SQAlchemy on the MySQL db.

You can definitely use sqlite on PythonAnywhere, and your data is safe -- we support a persistent filesystem, unlike Heroku.

The only downside is that it's slower (at least on PythonAnywhere) than MySQL or Postgres, but if that doesn't matter for you then it should be fine. (It's just slower, and won't work well for heavy-traffic sites -- it'll be fine for a low-traffic site.)

Re: backups -- we do have a replicated filesystem, so your data is safe against hardware failures. And we don't trash your files either. But if you want point-in-time backups (say, to protect yourself in case a bug somewhere loses data) then you can set up a scheduled task (check out the "Schedule" tab) to copy it once a day.

Giles, thank you so much for the super quick turnaround on my question. So far, I am really enjoying working with PythonAnywhere. Getting my Flask server running was so straight forward and simple.

So, my project is in an MVP phase, where I don't think it'll get much use. I am about 90% finished with it, but I wanted to figure out where I was going to host it before I completed version 1. Therefore, I don't think performance will be a big issue right now, but I would like to go with the more advanced support just to get better performance. Then, for the next version, I will port over to MySQL.

By the way, moving from my local machine to PythonAnywhere was very smooth. I just put my gulp build on github, and then in a Bash shell on PythonAnywhere I just did a 'git clone' - very nice. The only issue I have is that my "Twitter" OAuth sign-in is not working. I have three OAuth sign-ins (Facebook, Google+, Twitter). I'll start debugging the twitter now.

Thanks again for your help, John

Hi John -- thanks for the kind words! That sounds like a good plan. Start with sqlite, because you're familiar with it, then port to MySQL when you've got the basic functionality working :-)

Re: oauth -- let us know if and when you have more information. Problems like that can sometimes happen with free accounts if one of the servers involved in the oauth transaction isn't on our whitelist. I think we have all of the appropriate ones for Twitter on there, but it's possible we've missed something, so any detailed errors would be helpful.

Today, I have been unable to add or delete anything to or from my sqlite3 database file from the Bash console using SQLAlchemy's Database URI via Flask's app.config. Yesterday, everything was working just fine. Any clue on what could be wrong? There are no errors to be found in my logs. Thanks ahead of time.

What's the error message?

Conrad, there was no error message. And actually there was no error. It was all in my mind. Everything is working fine. I was failing to call "SQLAlchemy(App).session.commit()". I created and solved my own problem. Thanks again.

OK -- glad you got it working :-)