Monday, July 31, 2006

Sqlite sucks

I'm losing patience with sqlite. I've been working on Spyce examples using postgresql, but now that I'm getting close to releasing Spyce 2.1, I figured I'd better convert the examples to use sqlite since that's such a no-brainer to set up.

It has been a frustrating experience.

Weird-assness I've run into includes

And I didn't think I was doing anything very complicated! My examples have three tables at most!

Really my overall impression is one of a "0.9" product at best. I'm amazed that so many people appear to use this festering pile of gotchas in production.

10 comments:

Robert Brewer said...

Don't forget NUMERIC (decimal) values are actually stored as REAL (binary) values...make sure your app can round-trip decimal.Decimal("1111.1111") (or fixedpoint, if you prefer).

And there's no DROP/RENAME COLUMN, no CREATE/DROP DATABASE, no date functions, no RIGHT JOIN, no nested JOINS, and *very* confusing differences between quoting with '', "", and []. Also, schema changes are bound to the database connection in which they are performed, which hampers database connection pooling.

Just a few from scrounging my code. ;)

Mathieu Fenniak said...

Having said just this morning, "SQLite rocks", I was a little surprised to see "SQLite sucks" appear in my aggregator. :)

You're right, of course - SQLite has gotchas. Robert has pointed out a bunch more. I would hate to write an application that only targeted SQLite, as you would have to program in application code a lot of functionality that should be provided by the database.

That being said, I haven't seen a good alternative for administration-less embedded databases yet.

Anonymous said...

You can get the columnn default, among other things, using

pragma table_info('tablename')

which you can call use like any other SQL query.

Really this is all rather churlish. We're talking about an under 200K download here. I'm amazed that sqlite can do as much as it does.

Lawrence Oluyede said...

About INT vs INTEGER. Is a documented feature. See: http://www.sqlite.org/datatypes.html and http://www.sqlite.org/datatype3.html

Jonathan Ellis said...

Anonymous: I believe I said _useful_ default info. If you have, for instance, CURRENT_TIMESTAMP as a default, table_info will report the default is "2006-08-01 00:59:26". A few seconds later it will report "2006-08-01 00:59:26."

I'm okay with features that are legitimately missing and documented as such. It's features that should work, but don't, that piss me off. Especially in something that holds my data.

Kevin Dangoor said...

sqlite is most certainly the best SQL database you're going to find that is embeddable with such a small footprint.

That said, it's not well advertised but Firebird can be used as an embedded (serverless) database and is much more powerful than sqlite. (It's also much bigger, but not unreasonably so.)

Anonymous said...

Here are step-by-step instructions on using Firebird in embedded mode with Python.

Simon said...

If you've got a JVM handy, have a look at Derby http://db.apache.org/derby/

Flo said...

If you need column renaming you can use the graphical browser: http://sqlitebrowser.sourceforge.net/

Wyatt said...

I took a look at sqlitebrowser. It had a problem renaming a column because of a multi-column primary key (or something like that). And the interface is... not very pleasant. And... it doesn't help if you're trying to do something programmatically (like I'm trying to do right now with sqlalchemy-migrate).