Hacker News Reader

Comment by thechangelog | Hacker News Reader

SQLite all over the place – it's great having a super portable DB format for quick little hits.

There's nothing quite like sending a DB as an email attachment.

SQLite is so pervasive that I'm pretty sure everyone uses it at some point. It's in client applications, it's used by yum (the package manager found on CentOS/RHEL/Scientific Linux systems), it's part of many web applications, it's part of many spam filtering systems, it's part of Android (maybe iOS, too?), it's basically impossible to avoid it if you're a nerd.

Also, it's awesome.

SQLite is indeed built into iOS and Mac OS X as well. Core Data's store uses SQLite as an option (XML and Binary are the others), and it is accessible via third party frameworks like FMDB.

It's also built into Android.

Unfortunately, it's quite slow on Android. And make sure never to put your db on the sdcard, or else your whole phone becomes unusable while doing writes to the db.

SQLite is overall slow if you are writing item-by-item. Does not matter what platform you use. Write multiple items in single transaction. As well understand what you get and what you loose by writing all items in single transaction.

And iPhones, and BlackBerries. It is by far the most widely use relational DB in the world (despite what the MySQL people claim on their website, they're not even close)

Firefox uses it.

Indeed ...

http://berrange.com/posts/2011/06/22/firefox-form-data-histo...

So does Chrome

So does Chrome. Actually, this comes in handy: http://sqlite.org/famous.html

So good to see Sun's logo there :)

They should replace it with Oracle's logo now. Just for giggles.

Ouch, Php got a mention, but they snubbed Rails, despite the fact that it comes bundled ... DHH won't like that one bit!

At this point, it would be faster for them to just list the companies who aren't using sqlite.

Probably couldn't use the Rails logo because of stupid trademarking, so they left it off.

Rails doesn't bundle sqlite3, just lists it as a default dependency for new projects.

For me JSON has replaced SQLLite, with a large reduction in code and complexity. [ Admittedly I've written my own routines to access it more simply/directly on iOS ]

Yeah and like HTML5 has totally replaced our web server.

He means a flat file of JSON records. Your sarcasm is unnecessary and uncharitable.

AND fun

How do you know he means that?

Because it's the only interpretation that makes sense, and charitable discussion demands that I assume my interlocutors are reasonable people who say things that make sense until I have evidence to the contrary.

The sarcasm was perhaps a little mean but he has a point. If a flat text file storing JSON could replace your database, then you probably never needed a DB in the first place.

Lots of people use full blown rdbms's when they dont need to (see blogs), its not a silly point and the sarcasm was unwarranted

Sarcasm is never unwarranted.

If you can use SQLlite, you probably didn't need a real database. So the use cases overlap a bit with flat-files and JSON.

The JSON flat file sounds like variable length records where length is determined by parsing each record with global reader/writer locking?

It's like public storage where you have to sift through everyone else's crap to get to yours, every item is stored in a bulk cargo box and only one customer gets to store their stuff at a time.

If you're interested in db internals, here's a few algorithms that MySQL uses. Note this doesn't cover InnoDB which performs far better under high concurrency loads and offers row level locking, clustered indexes, an excellent caching algorithm, foreign key constraints with cascade, etc..etc...

http://forge.mysql.com/wiki/MySQL_Internals_Algorithms

The way I've stored things in flat files is to make use of the file system's lookup capabilities. I wouldn't suggest storing data that may be requested or written to by more than one client at once with a method such as this, either. I wouldn't try to use it like a C array or anything. One file per user or more makes sense. I've also used stuff like this for system admin scripts.

Something like

   user_id=int(request.GET['id'])
   data_type=int(request.GET['id'])
   json_file="{0}-{1}-chart_data.json".format(user_id,data_type)
   #get the file, decode, etc.

It's a quick and dirty caching method that has good persistence, of course, is relatively performant under low loads and easy to understand.

Thanks, I'll check out the MySQL thing, but I'm not actually intending to build my own database.

what's your cutoff line for something to be a "real database"? sqlite is fully acid compliant.

sqlite is single user and locks a bit too frequently to be very scalable, so while quite useful, I mean sqlite not capable of fully replacing a typical engine such as postgres or mysql.

I think you could make your point better by asking a question about the negative consequences/limitations of using JSON.

Sarcasm doesn't aid in making points online because people who don't know anything about the issue at hand make sarcastic 'points' as easily as an expert.

I left it unsaid that I assumed SQLLite was normally used in places other than the traditional back end server data store - I tend to use postgreSQL / mysql or perhaps a noSQL variant such as couchDB/Mongo for server-side data.

So I meant that instead of using SQLLite on mobile devices, pc based local fat clients or in the browser... I now use JSON now whereas I might have used SQLLite before [ I also use JSON where I might have used XML or windows config files etc ]

I did not intend to say JSON is replacing MySQL / PostgreSQL.

I can sorta see many cases where that would be useful, but personally I am happy letting SQLLite do the heavy lifting extracting things with indexes, grouping and using the where clause.

But yeah, JSON is great for small amounts of data.

It's pretty handy, and an easy upgrade to a 'real' database if the need ever arises - especially mongo/couch.

You're getting jumped on, but you have a good point. It's often easier to just stringify a dictionary and read it back as the object instead of handling the database creation/insertion/selection if all you need is to store and access a few values.

I also tend to use JSON as the main messaging format for Client/Server communications [ where I might have used sockets custom binary protocol previously ].

This means I can reuse the same REST style data 'provider' from within a Javascript web UI and within an iOS or Android client app.

So theres quite a few reasons why using JSON simplifies things for me - particularly I notice that there is a whole layer or ORM style boilerplate code that I dont need now [ whether its SQLLite <-> Object or XML <--> Object ].

I actually like the SQL Lite implementation!

Because JSON is well known for its ACID compliance.

Love SQLite when doing some Django development. Great to easily scrap the database with a quick `rm` and repopulate with good data.

I've been breaking myself of that habit: I try and use the flush command so I don't have to keep doing the superuser creation step.

https://docs.djangoproject.com/en/dev/ref/django-admin/?from...

Good point, as the superuser creation bit does tend to get annoying. But you have to admit–there's something much more fun about getting to rm your db ;)

yeah, but if you are modifying the schema and are not using south or something like that, you will still want to rm & syncdb again....

Agreed. SQLite also has a great copyright notice in its headers:

  ** The author disclaims copyright to this source code.  In place of
  ** a legal notice, here is a blessing:
  **
  **    May you do good and not evil.
  **    May you find forgiveness for yourself and forgive others.
  **    May you share freely, never taking more than you give.

(http://www.sqlite.org/src/artifact?name=a8571665d43ff18f89a4...)

It's also worth noting that SQLite is, for the time being, the underlying store for Membase

You mean the eventual persistance store.

In the microsoft world SQL CE 4 is pretty nice for doing the same sort of thing.

I still much prefer to use SQLite on the MS stack, given how reliable and ubiquitous it is.

In some scenarios I prefer Microsoft Excel over SQLite. Yes, Excel can be used as a database as well!

Do you mean as a database engine using the ODBC driver for Excel or as a database-like application?

Also damn useful when plugged into NHibernate for bringing up test cases using in memory databases for performance.

Reply on news.ycombinator.com