Hacker News new | past | comments | ask | show | jobs | submit login

> I think there could be some instances where people suggest or build stuff with SQLITE when a more robust database solution is called for.

They do, unfortunately. I inherited a web2py application using SQLite as database. It was maybe OK when the original developer wrote it but it's pretty clear now that we should move to a full fledged database.

There already were "fossil" columns in the database because there is no DROP COLUMN and no RENAME COLUMN. The lack of those basic features greatly hinders development because they must be implemented by creating a new table with the new schema and copying all the data in there. Luckily we can stop production to perform those operations, but the extra developer time means that any of those operations costs an unreasonable amount of money to the customer (compared to ALTER TABLE DROP COLUMN). It's not a surprise that they decided to keep those fossils into the database for now and "we'll see what to do".

Another problem I run into was the typelessness of the storage. Basically SQLite has storage classes and maps SQL types to them (Edit: see the note at the end) I quote https://www.sqlite.org/datatype3.html "The important idea here is that the type is recommended, not required. Any column can still store any type of data" and "If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored." I won't go into details but I had some fun with dates, stored in different formats in different TEXT fields and even in the same column of the same table, because different versions of a controller had different ideas about how to parse the dates received from the browser.

For these and other reasons we should migrating to PostgreSQL (even MySQL will do). That would mean rewriting parts of the application and testing it all (guess if the original developer wrote any test?) That costs too much upfront so we're sticking with SQLite for the time being, until we hit some showstopper.

At least a problem this application won't ever have is scaling. There will always be one server and one process writing to the database. SQLite is OK in this scenario. A different web app for a different customer (also inherited) is using SQLite and maybe it will have to scale horizontally in the next months. That means we'll have to replace SQLite with PostgreSQL but again, it costs and will do it only when necessary.

TLDR, my suggestion is to never start a web application with SQLite. It's not its natural environment and there is little to gain because setting up a traditional database is not that difficult anyway. Use SQLite only as embedded db for desktop or mobile applications. I wish we had it in the browser too. I'm sorry that https://www.w3.org/TR/webdatabase/ has died. IMHO it was much better than https://www.w3.org/TR/IndexedDB/

Edit: I read once again the page I linked and concluded that this is because of the choice of the developer. He used varchar for some date fields and numerical fields for others. The problems were in the varchar fields. No blame to SQLite here. Still, automatic data conversion is not a good idea for a database IMHO.

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact