SQLite Forum

Interesting question: Why aren't you using SQLite more?
Login

Interesting question: Why aren't you using SQLite more?

(1) By anonymous on 2020-12-24 15:35:48 [link] [source]

Why aren't you using SQLite more?

Because

  • Without any worked examples in the documentation the learning curve is very steep, almost vertical, even with in-depth knowledge of other RDBMSs.
  • Proactive Google searches for solutions are helpful only some of the time; most of the time the searches are fruitless as they relate to different consumers of the SQLite library.

Fortunately, searches provide enough clues to make things work given prior programming knowledge. The forum, patronising as it can be, is a great asset for airing issues especially for those who persevere.

Summary:

  • SQLite is a formidable library, without any shadow of doubt but it requires tenacity of biblical proportions to use it to its full potential.
  • SQLite is a cross platform library maintained to the highest standards, not least those of version control and transparency but most users are interested in documented solutions for their platform only; instead, they are faced with generic (very terse) authoritative documentation.
  • The missing worked examples (or, failing that, pseudo code) is a missed opportunity to promote recommended practice for deploying solutions with SQLite.

DRH in one of his presentations categorically states SQLite is an in-process library and not SQLite is an in-process library that should be used with (out of step SQLite version-wise) System.Data.SQLite or Microsoft.Data.SQLite or whatever other wrapper (several exist) there may be. Worked examples1 or pseudo code for just 10 APIs would save a great deal of time and flatten the SQLite learning curve.

1User contributions would readily fill this gap but there is no platform for its submission, as far as I can see.

(2) By J. King (jking) on 2020-12-24 16:08:12 in reply to 1 [source]

I won't comment on anonymous' experience other than to say that is starkly different from mine.

Generally speaking I use SQLite quite a lot both in software I have written and software I have used, saving myself lots of pain in managing grants and backups for heavier database systems, not to mention time unit testing thanks to in-memory database which are trivial to build up and then tear down.

I'm not sure I could actually be using SQLite more, but as a PHP programmer I find myself unable to use more of SQLite (like fts5 or json1) because one can generally only rely on the core library being available. That's not really Hwaci's problem, but it is an answer to the question.

(3) By Gerry Snyder (GSnyder) on 2020-12-24 16:39:52 in reply to 1 [link] [source]

I can't imagine any software using SQLite "to its full potential."

I would guess that most programmers just solve their specific problems with it, and don't worry about the bajillion additional things the library is doing for others.

I use SQLite with Tcl, and have found that the official documentation pretty much all I have needed, with additional help from the old email list three or four times over the couple decades I have been using it.

I am not sure that additional documentation would have helped me very much.

Gerry Snyder

(4) By anonymous on 2020-12-24 17:58:43 in reply to 1 [link] [source]

I think that the existing official documentation is (mostly) sufficient, so I disagree with some of these points. (You may be correct about recommended practice, although I personally have not found it to be a problem.)

SQLite is very good. I use the C API myself, although if you are using other programming languages, then you will have to use that one.

I have used SQLite in many programs, including a NNTP server software, a NNTP client software, a ZZT external editor, TeXnicard (which uses not only the database but also the page cache), and others. In many cases, the user is allowed to enter SQL codes too, and to use in the configuration. (I think that exposing the SQL interface to the user is often useful.)

Even without writing a program that uses the SQLite library, the command shell is good for many things, including to deal with SQLite databases used by other programs, as well as just for storing and querying your own data which is not a part of another program.

(5) By Simon Slavin (slavin) on 2020-12-24 23:36:12 in reply to 1 [link] [source]

I'm not a programmer any more, but back when I was my reasons were weirdnesses and inconsistency.

sqlite_open() does weird things, including not reporting faults which prevent the opening of the file. You have to use sqlite_open_v2() for anything sensible. Okay, so you put a little note in your documentation that some SQLite calls have _v2 versions and it's probably best to use those where they exist.

Having done that, we're obviously going to use sqlite_close_v2(), not sqlite_close(). But sqlite_close_v2() may not close the file and does not report some things that would prevent closing the file. If you want to know if there's anything that prevents closing the file you have to do other things. It doesn't always release the handle's resources – because it doesn't always close the file. But it returns SQLITE_OK anyway. So my code has to do something else to figure out something is wrong.

And that's just opening and closing a connection. We haven't issued a SQL command yet.

NULL allowed in autoincrement indexes. No date/time type. Different journal and shared memory modes that have different behaviour and ways to fail. Refer to a table which doesn't exist and you can get data from a different table (TEMP.tablename). No way to enforce the use of transaction commands. Foreign keys off by default even if a table in the database the connection opens uses them. Numerous other things that look like bugs but are there because they once existed and there's code out there that uses them.

Sometimes the effort it took to document why my code looked so weird, it made it faster to use a text file, even if the code was less flexible and took a little longer to execute.

Don't get me wrong. SQLite is wonderful. It's terrific to have one database be one file on disk (except when it's two or three because of a journal or shared memory file). It's a relief not to worry about server/client problems. It's wonderful that the source code compiles to such a small object size. The lack of support for the crawling feature bloat of the SQL standard makes me feel I have a good overall understanding of SQL, unlike any other SQL engine I can name. The documentation (in response to continual criticism here) isn't too bad, despite the policy of having no sample code. The shell tool makes testing and scripting simple, fast, and convenient. I love SQLite. But for projects which demand documentation for people who don't know it, it can be annoying.

(6.1) By Gary (1codedebugger) on 2020-12-26 20:40:57 edited from 6.0 in reply to 1 [link] [source]

Deleted

(7) By Cecil (CecilWesterhof) on 2020-12-25 11:18:41 in reply to 1 [link] [source]

Because I cannot.
For my own code SQLite is the only database I use.
In the corporate world I have not enough leverage to change the database used.