
"SQLite is not designed to replace Oracle. It is designed to replace fopen()." - ma2rten
http://www.sqlite.org/whentouse.html
======
techtalsky
Even though SQLite isn't designed to replace Oracle, I'm certain there's
plenty of companies that have purchased expensive Oracle licenses for projects
where SQLite would have worked perfectly well. I've often used it for web-
based custom CMS's of one kind or another where many people would have used
MySQL and the client would never know the difference. I'm sure many people
have used Oracle for web-based internal tools like this with less than a
million rows of data.

~~~
apl
Regardless of how much fun lamenting the assumed technical incompetence of
enterprise software development can be, I strongly doubt that this has ever
happened. Oracle is _very_ expensive, and SQLite is _very_ limited outside its
clearly delineated use cases.

Oracle versus PostgreSQL or possibly MySQL -- maybe. But don't think Oracle
sells snake juice; their reporting tools, for instance, are top-notch and way
beyond what the open source world can provide.

~~~
techtalsky
Believe me, I wasn't trying to have fun at someone's expense. I think factors
other than incompetence can account for this, like just simple ignorance.

I was more speaking to how easy it is to underestimate SQLite's flexibility
and power and not realize how wide its use case actually is.

Of course for many use cases Oracle's tools are the best choice, but SQLite
can handle quite a serious dataset in some use cases. A million records sounds
like a lot. I can easily imagine crews used to working with Oracle using it
for project far below its ideal capacity when they can buy licenses on a big
corporate account.

~~~
StavrosK
I worked with a thirty-million-row dataset for my thesis (after MongoDB
famously choked and ate half my data more than once) with SQLite and it didn't
bat an eyelid. It was fast as hell, too.

~~~
wheels
Size isn't the problem with SQLite databases. It's that it's unbearably slow
with even fairly trivial joins and most of the other operations where it'd
make sense to actually have an SQL database. SQLite is fine for tabular data,
but then, so are a bazillion other things. Basically it's the perfect SQL
database for the times that you shouldn't be using an SQL database.

~~~
microtherion
I don't quite agree with that. One advantage I see for using SQLite is that
you CAN use it as an SQL database for administrative purposes: You can quickly
import/export the data from or into another SQL database, you can quickly
generate an ad hoc report, etc.

So to me, the SQLite use case is often where the PRIMARY purpose of a database
could be served nearly as well with fopen(), but where it's likely that there
will be SECONDARY uses for which having a convenient universal access method
would be useful.

I've even considered writing an external table plugin for SQLite to facilitate
diagnostics and reporting of a complex ad-hoc file format I designed so I
could get those benefits with a custom data structure; unfortunately, doing
that seems somewhat complex in SQLite.

------
larrik
I first used SQLite because I thought (in C++) "Man, instead of writing crazy
datastructures, I wish I could just use SQL on a spot in memory" SQLite came
up in my Google search, and ta da!

Of course, this was back in 2003 or so, and that project never turned into
anything more than a fancy wrapper over SQLite and Lua.

Still, SQLite == awesome, when it is appropriate.

~~~
diminish
SQlite is a gem; when we develop web sites for clients, we first try to use
sqlite, and if not we use MySQL and PostGres; we only use mssql, oracle when
the customer has already a legacy database. What about you?

------
malkia
We use SQLite exactly for that. At some point we were generating +50000 shader
(.hlsl) files for 3 different platforms (so much due to various techsets and
techniques).

Loading them back from the tools/game took a long time, for that reason they
were put in small SQLite db - which is a little bit more complex than this
"CREATE TABLE files (key, value)"

This sped up us significantly. We might be using this idea for more and more
small files lying around.

------
dendory
I use SQLite exclusively in my projects, I love it. Easy to use, easy to
backup (1 file), doesn't rely on a SQL database running, no security concern
with username/password for the database engine (altho make sure your database
file isn't web accessible)..

For any small/medium site it's great.

~~~
DNeb
Agreed. And the database can grow surprisingly large (multi GB with many
millions of rows) and it still performs quite well.

~~~
kellishaver
This surprises me. Though I've used it with great success for several small
projects, my only experience with SQLite on anything remotely large was a
Rails app with maybe a million records in the DB. Over time, performance
became very slow and switching to MySQL made a world of difference.

At the time, I attributed it to SQLite, but now I'm wondering if it was Rails
or (more likely) my inexperience at optimizing performance of the app at that
time.

It was about three years ago, though, so I am sure performance in SQLite (and
Rails, too, for that matter) has also likely improved a lot during that time.

~~~
lsb
In SQLite, autocommit is on, so if you weren't explicitly setting your
transactions, you might have been commit after each update. Also, the standard
disk cache is 2MB, so if you think your DB should have more memory than that,
up it with default_cache_size.

~~~
kellishaver
Ah, I wasn't, so that combined with pilif's comments regarding the file being
locked for writes would make a lot of sense as to why things were slowing
down.

Thanks for the info.

------
alanh
Wow, more projects should have a page similar to this one. Upvoted because
it’s good to keep in mind when thinking of sqlite _and_ when documenting your
own project.

------
reduxredacted
By the use of "an", I learned that I have been pronouncing SQLite wrong. The
maintainers clearly prefer the spelling out of SQL in the title.

I've always been on the "sequel" side of pronouncing SQL (hey, it's no worse
than "scuzzy" for SCSI), which morphed SQLite into "Sequelite". I never
realized how bizarre that sounded (almost more like a material than a
database).

~~~
ebzlo
So does that become S-Q-L-ite or S-Q-Lite, you think?

~~~
reduxredacted
Good point. I was going for SQL-Lite. But being wrong (and hearing enough
people say "lie-nux" [and to be clear, I am not mocking ... we read things and
form our own pronunciation because we often don't hear them said] ...) I
decided to google it.

This isn't necessarily authoritative or well researched, but it appears that
S-Q-L-Lite is correct: <http://blog.cleverly.com/permalinks/247.html>

~~~
MostAwesomeDude
As an aside, to follow Linus' pronunciation, it is Lee-nucks, from the Finnish
way of pronouncing "Linus." Americans are used to a different pronunciation of
the same name and thus generally adopted a different way of saying "Linux."

Language is fascinating!

~~~
Muzza
Linus is a native speaker of Swedish, not Finnish.

~~~
k33l0r
The pronunciation of 'Linus' is pretty much the same in Swedish and Finnish,
though.

~~~
Muzza
Surely the U differs. To Sweden-Swedish ears at least, Linus in Finnish sounds
like Linos.

------
peterwwillis
I suppose it depends on your definition of "simplicity". My go-to simplified
data storage container is Berkeley DB. (Little did I realize Oracle recently
shipped a Berkeley DB with SQLite inside it... heh)

------
derrickpetzold
Can anyone say when Oracle is justified? I have yet to encounter a problem the
postgres or mysql didn't support but alas I have not worked on everything.

~~~
tomjen3
Support. You can properly find some solution to your issue with postgres
online. Oracle can send an Oracle certified engineer who can solve your
problem.

Consistency. Your enterprise depends on terabytes of hyper valuable
information (Wallmart with their sales data), can you guarantee that you won't
end up with corruption issues? Or that the next version will work with your
system too?

That said as long as you make less than 20 mil/year, Oracle isn't likely to be
the best solution.

Just a quick question: which engine do you use for your MySQL system?

~~~
derrickpetzold
InnoDB. I understand your support viewpoint but both of those dbs are
opensource thereby leaving support open. From what I understand when the
support comes into play is when you can sue the other party for not providing
the service expected but there is company support for mysql and postgres so
that still leaves me in the dark and I have never experienced corruption
issues with either. Though my db experience is limited and I don't claim to be
an expert.

~~~
StavrosK
I had experienced db corruption with MySQL years ago, when _the disk became
full_. To be fair, I _think_ it was MyISAM.

------
pacaro
I love the example that this provides with respect to the conflation of
"Storage" with "FileSystem", while data may indeed be stored on a filesystem
somewhere, the habitual thinking that appends " to file" to the phrase "I need
to save these data" seems unintentionally limiting to me.

Instead of trying to answer questions like "what should our file format look
like?" It seems more interesting and valuable to ask first, "what storage is
most appropriate", the answer may be REST, database, file, /dev/null, or any
abstraction over one or more of these. You may then find that questions about
file format, or structure simply disappear, it is the role of the storage
mechanism chosen to efficently store and recover the data, use the hard work
that the team that built that solution invested, and apply your own hard work
to solving your own problems.

With cloud and mobile being added to the set of common target platforms
(however ill-defined), I hear/read people asking "how do I read/write a file"
increasingly often, when on those platforms (and many other) the concept of a
file (at the application layer) may not be useful at all...

------
smharris65
Can anyone provide more info when the site says "...the file locking logic of
many network filesystems implementation contains bugs (on both Unix and
Windows)"?

~~~
grandpa
They generally relate to NFS - e.g., Linux fcntl() used to lock NFS files only
locally. <http://en.wikipedia.org/wiki/File_locking#Problems>

Windows CIFS shares are fine and always have been AFAIK.

------
alleri
Laughed a lot! :D

------
rbranson
... because the only use for file I/O in applications is structured tables of
data? What about audio/video, documents, graphs, log-structured data, object
caches, unstructured flat files, etc? Don't get me wrong, SQLite is awesome,
but this is a classic example of the relational database world's pervasive
attitude that they are the superior solution for any data storage problem.

~~~
owenmarshall
Wow. Not only did you manage to completely miss the point of that statement,
but you cast it up as a hasty strawman to boot. I'm quite impressed.

The choice of fopen() and Oracle was meant to convey simplicity: SQLite solves
problems on the simple end of the difficulty spectrum -- problems you would
normally solve with fopen(). It is _not_ for problems at the complex end, like
Oracle. It's a simplified expression; reading it literally is foolish.

~~~
rbranson
Damn, you are so right bro.

