5 ms·

I always seem to be the black sheep in a group of people when I say that I love sqlite. It's seriously so handy. If I need to aggregate or parse big CSV sheets,

by vhost- 10y ago

I always seem to be the black sheep in a group of people when I say that I love sqlite. It's seriously so handy. If I need to aggregate or parse big CSV sheets, I just toss them in a sqlite database and run actual SQL queries against the data and it makes the process much faster and I can give someone their data in relational form on a thumbdrive or in a tar.gz.

kobeya 10y ago
Most people I interact with love sqlite.
m_fayer 10y ago
I thought everyone loved SQLite! It's tiny, no-fuss but full-featured, performs well, and works great as a data-interchange format. I use it in all my simple ad-hoc personal apps, and where would mobile be without it?
- vhost- 10y ago
  Not to mention browsers. Many people are surprised to learn iMessage, Chrome and Firefox all use SQLite.
  - rodgerd 10y ago
    More people would probably be surprised to discover Adobe Lightroom catalogues are sqlite databases (which means you can pull all sorts of info out of your catalogue, should you so desire, or fiddle with it in unsupported ways). It's a shame I don't see Adobe on the list of sponsors/contributers, I have to say. Lightroom makes a huge amount of money for them.
wybiral 10y ago
I'm a fan of sqlite for similar reasons. I also use it frequently to do some data massaging before loading into a larger database since it has a standard Python module and queries are quick and easy.
sbuttgereit 10y ago
I think there could be some instances where people suggest or build stuff with SQLITE when a more robust database solution is called for. Or they start test/develop with SQLITE when the final target is, say, PostgreSQL and delay too long the time they get tests and rigor in place on the real database (or avoid doing so outright). I don't know that you fall into any of those camps (I don't know you after all), but if you did I could understand why you'd find yourself on the wrong side of opinion. However, SQLITE is great for ad hoc data manipulation as you say, and there are application niches, particularly on the client side where it can be great. By itself, everything I know about it is that is an excellent piece of software and the tests are practically a case study in rigor (again, from what I've heard).... so how could you not love that?
- pmontra 10y ago
  > 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 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/ https://www.w3.org/TR/webdatabase/ has died. IMHO it was much better than https://www.w3.org/TR/IndexedDB/ 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.
c4pt0r 10y ago
I love sqlite, and I also love MySQL/MongoDB/PG/HBase/TiDB...just use the right tool for different job.
cyberferret 10y ago
I've been a fan of SQLite for nearly two decades now! use it all the time on my Win32 apps for recording things like local log files and other things that don't require multi user access. I recall even building a simple local network full text search engine for legal documents for a law firm using SQLite with their FTS3 extension once. Built a few iOS app with it as the data store too. Love the 'zero configuration' install. From memory I've even used DOS batch files to manipulate data on SQLite...
- arvin 10y ago
  Me too, nearly two decades now. Started using it for Win32 apps also. Now we're using it for mobile apps.
laumars 10y ago
I'm a recent convert to sqlite - started using it in CLI Go apps as a more robust way of handling large data sets than building my own bespoke in memory databases. It's not often I come across software that impresses and amazes me as much as sqlite does. It's tiny, ridiculously easy to implement, writes 1 flat file, uses comparatively little memory, and is /fast/. I don't even know how they managed to make it so fast given all the other constraints. Seriously impressive database.

by vhost- 10y ago

kobeya 10y ago

Most people I interact with love sqlite.

m_fayer 10y ago

I thought everyone loved SQLite! It's tiny, no-fuss but full-featured, performs well, and works great as a data-interchange format. I use it in all my simple ad-hoc personal apps, and where would mobile be without it?

vhost- 10y ago
Not to mention browsers. Many people are surprised to learn iMessage, Chrome and Firefox all use SQLite.
- rodgerd 10y ago
  More people would probably be surprised to discover Adobe Lightroom catalogues are sqlite databases (which means you can pull all sorts of info out of your catalogue, should you so desire, or fiddle with it in unsupported ways). It's a shame I don't see Adobe on the list of sponsors/contributers, I have to say. Lightroom makes a huge amount of money for them.

wybiral 10y ago

I'm a fan of sqlite for similar reasons. I also use it frequently to do some data massaging before loading into a larger database since it has a standard Python module and queries are quick and easy.

sbuttgereit 10y ago

I think there could be some instances where people suggest or build stuff with SQLITE when a more robust database solution is called for. Or they start test/develop with SQLITE when the final target is, say, PostgreSQL and delay too long the time they get tests and rigor in place on the real database (or avoid doing so outright). I don't know that you fall into any of those camps (I don't know you after all), but if you did I could understand why you'd find yourself on the wrong side of opinion. However, SQLITE is great for ad hoc data manipulation as you say, and there are application niches, particularly on the client side where it can be great. By itself, everything I know about it is that is an excellent piece of software and the tests are practically a case study in rigor (again, from what I've heard).... so how could you not love that?

pmontra 10y ago
> 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 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/ https://www.w3.org/TR/webdatabase/ has died. IMHO it was much better than https://www.w3.org/TR/IndexedDB/ 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.

c4pt0r 10y ago

I love sqlite, and I also love MySQL/MongoDB/PG/HBase/TiDB...just use the right tool for different job.

cyberferret 10y ago

I've been a fan of SQLite for nearly two decades now! use it all the time on my Win32 apps for recording things like local log files and other things that don't require multi user access. I recall even building a simple local network full text search engine for legal documents for a law firm using SQLite with their FTS3 extension once. Built a few iOS app with it as the data store too. Love the 'zero configuration' install. From memory I've even used DOS batch files to manipulate data on SQLite...

arvin 10y ago
Me too, nearly two decades now. Started using it for Win32 apps also. Now we're using it for mobile apps.

laumars 10y ago

I'm a recent convert to sqlite - started using it in CLI Go apps as a more robust way of handling large data sets than building my own bespoke in memory databases. It's not often I come across software that impresses and amazes me as much as sqlite does. It's tiny, ridiculously easy to implement, writes 1 flat file, uses comparatively little memory, and is /fast/. I don't even know how they managed to make it so fast given all the other constraints. Seriously impressive database.