SQLite: The art of keeping it simple | Hacker News Reader

SQLite: The art of keeping it simple

jarchitect.com

114 pointsyarapavan64 comments

Since no one mentioned yet, the (lack of) license:

"The source code files for other SQL database engines typically begin with a comment describing your license rights to view and copy that file. The SQLite source code contains no license since it is not governed by copyright. Instead of a license, the SQLite source code offers 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."

I wonder if they've ever run into the problem Douglas Crockford had with IBM requesting he clarify the "The Software shall be used for Good, not Evil" clause he added to the MIT license for JSLint....

https://www.youtube.com/watch?v=-C-JoyNuQJs&feature=player_d...

You can buy a SQLite license if you really want one:

https://www.sqlite.org/copyright.html

at which point you are free to ignore the blessing.

It's public domain, so no license necessary. The blessing is awesome though.

As discussed below, public domain isn't a concept in many legislations (copyright vs. authors' right e.g. – you can relinquish your exclusive right to copy, but not your authorship), so minimal licenses like CC0 are needed to really put your works under public-domain-or-equivalent worldwide.

http://sqlite.org/src4/doc/trunk/www/design.wiki offers a good overview of SQLite deisgn, for those who are interested.

> "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not going away. SQLite3 and SQLite4 will be supported in parallel. The SQLite3 legacy will not be abandoned. SQLite3 will continue to be maintained and improved. But designers of new systems will now have the option to select SQLite4 instead of SQLite3 if desired."

In addition to commitment, that's what I call clarity by repetition / reformulation :) . Good to see a writer handling worries about such backward-compatibility-breaking changes in the absolutelytotallymostest explicit way.

I listened to a podcast interview with Richard Hipp (the main SQLite developer) recently: http://5by5.tv/changelog/201

It was really good. One of the things he mentioned is how they're planning to support SQLite until 2050 because of an agreement with Airbus (apparently that's the life of one of their recent plane models, and SQLite is used for some aspect of the software). Crazy!

I remember seeing mostly same paragraph 10 years ago, only with 3->2 and 4->3 :)

She knows her audience ;)

sqlite4 is completely different from sqlite3. The design is interesting in its own right but it's for a completely different product.

I thought SQLite4 was dead in the water? There have been no contributions since late 2015. Is there still a plan to maintain it?

I like to think of it as a thought experiment with some working code attached.

The link is broken at the moment.

I really want to read it to learn the answer to the following:

why isn't sqlite able to handle full scale loads? Why do I have to swap it out? Can't someone change the implementation details so it can work at scale and leave the interface the same?

Where is sqlheavy?

SQLite has a basic optimizer. That's basically it IMO. You can already do some things like use BDB backend if you want better transaction performance.

Here's the cached link, but there are no such answers within this text. This just measures some arbitrary metrics on sqlite codebase.

http://webcache.googleusercontent.com/search?q=cache:21EcxYD...

Some of advice within should be taken with a grain of salt. Exiting function as early as possible might be OK for you, but it goes against some coding standards that dictate only single return statement per function.

sqlheavy is available, it's called postgresql. But you could go halfway and use something like libmysqld.

> why isn't sqlite able to handle full scale loads?

The goal of sqlite is to be easily embeddable: that means not too much code, not too much resources used. It serves that purpose well: it's widely used in a lot of programs where you never even thought that they could have it.

https://www.sqlite.org/mostdeployed.html

So the goal was never to suit your "full scale loads" the way you probably define them. But I don't know what you actually understand under the "full scale loads" and once you state what you would exactly expect to achieve it can be discussed more. Until then, simply stated, there is a reason why the big server software is big and resource hungry, unlike sqlite.

You mean heavy concurrent writes?

I have no special insight in SQLite, but I think because it would need to be multiprocess, which it cannot as an embedded library.

Have you read https://www.sqlite.org/whentouse.html ?

> Situations Where A Client/Server RDBMS May Work Bette

> • High-volume Websites

> • High Concurrency

Anecdotal Observation:

I like to use SQLite when running tests etc... because it's easy to make the data make sense, and easy to ship the resulting file, but recently I had to run a high number of concurrent writes and it slowed down my tests and made my laptop unusable. I re-wired my scripts to use PostgreSQL and it moved the bottleneck to what I was actually trying to test, and even though my disk led was blinking like crazy, my laptop was still usable. I checked what was going on and saw that postgres had spawn a whole bunch of processes...

On other workload, especially with low-concurrency, I have found SQLite actually performing really well, and comparably to postgres. You do have to tweek a few things depending on your workload, but there are a lot of very informative blogs and SO pages on this subject out there.

Yes.

But the whole point of sqlite is that i don't have to type in more code. Can't it just do all that for me?

I should add that I know it is a minimal lightweight database, and that there are other ones that are much more feature rich and more complicated.

No, the whole point of sqlite is to be able to use a relational data model instead of a text file.

It sounds like you are needing a Relational Database server. PosgreSQL, DB2 and Oracle aren't that difficult to install on a laptop. I highly recommend using the same product in dev and prod.

I think you're looking at the wrong layer. SQLite is a specific database implementation, there's no reason to expect it to provide a wrapper around some other implementation too.

One potential problem is that not all databases are created equal. SQLite may support features that other databases don't (or vice versa), or they may both support the same feature but with different syntax, and so code that works with one backend may not work with another.

In any case, it sounds like you're after some sort of wrapper that can provide access to different database backends without your code needing to know or care what the current backend is. Something like ODBC would seem to fit the bill, and there is a SQLite driver for it.

You keep asking this and keep getting the same answer. Why do you expect the answer to change if you ask more times?

Because the answer is not the answer to the question I asked, so if you think it is, I didn't ask it the right way.

If I ask:

-- why can't sqlite just scale for me.

And you say:

-- Well, its meant to be lightweight. There are other more powerful databases, or you can add more code to sqlite to make it scale.

Its not that I didn't know all those things. Those were the premise to the question I asked.

The things that make a database scale are fundamentally at odds with the things that make a database zero-configuration. You can get one or the other. The thing you want, a zero-configuration self-tuning automagical smart RDBMS, is not considered feasible.

There. An answer to the question you asked. Incidentally, if you think that's unsatisfactory answer, I remind you that you're on a website linked to a startup accelerator.

> why can't sqlite just scale for me.

Because it's not a client/server implementation, it's a single embedded library, and because part of the requirements were to use a single data file. Therefore, each process (all completely independent) accessing that file:

-needs to lock the entire file (rather than tables/rows)

-needs to flush and close the file after each write

-cannot prioritize I/O across processes, one file open the file, read or write, close the file (as opposed to do all the writes at once for example)

-cannot cache very effectively, every time a process writes, all the other processes have to re-load the file

etc...

> need to be multiprocess, which it cannot as an embedded library

It actually can, it's just not ideal and leads to a lot of lock contention.

Isn't this just an issue if there are too much writes?

And doesn't a WAL help with that?

Run the db in-memory when doing tests with sqlite.

In postgresql, open a transaction, and run each test in a subtransaction, then abort the 'top' transaction. This should be ~fastest.

One of the reason I use a DB is to be able to recover most of the data in case of a crash, met disconnection etc... So memory isn't good for me here.

I meant only for testing.

There is no reason why you cannot do concurrent transactional writes in embedded database library and there are many embedded databases that can do that.

MySQL is probably only opensource client-server RDBMS whose server is based on relatively tightly coupled threads. PostgreSQL uses separate processes with well defined IPC interface between them, for long time the postmaster process mostly served to initialize and teardown this IPC interface (apart from spawning postgres processes for incomming connections), which was mostly needed because SysV IPC is brain-dead, hacking PostgreSQL into embedded library is probably not advisable, but perfectly doable. Firebird's architecture is similar, but in addition explicitly supports use as embedded library and stores whole database in single file, that can be used by both server daemon and embedded library at the same time (and the file can even reside on windows share without compromising transactional semantics).

Main problem with doing fine grained locking in embedded library (apart from complexity of fine grained locking itself) is with various non-standard situations like crash recovery. Another problem is that when processes that access the database data run under different users, there is real possibility of data corruption caused by wrong permissions or some similar misconfiguration (perfect example of this were first versions of subversion with Berkeley DB backend).

SQLheavy is called "Oracle". You want a feature, it's in there. You don't want a feature? Well, tough, it's in there anyway.

From https://www.sqlite.org/about.html :

"SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine"

Zero-configuration is actually quite an onerous design requirement, as it immediately eliminates most features which shouldn't be on by default. It also eliminates tuning.

Serverless imposes most of the rest of the performance penalty. If you're accessing a sqlite database from a single process, it will most likely be limited by your storage speed. But if you're writing to it from multiple processes, lock contention will quickly become an issue. The obvious technique to improve lock contention is to separate the tables so they can be locked separately, but then you lose the single-file convenience.

That's why that page also says "Think of SQLite not as a replacement for Oracle but as a replacement for fopen()".

> Zero-configuration is actually quite an onerous design requirement, as it immediately eliminates most features which shouldn't be on by default. It also eliminates tuning.

It also makes for a stupidly easy to use product. SQLite easily outperforms Postgres in default settings and any "optimized" snippets blog posts cough up for the workload generated by a project I'm working on.

I'm seriously tempted to drop support for non-sqlite DB backends, since it's unlikely that the majority our potential customers (SMB environment) could do a better job than that at configuring database servers.

> That's why that page also says "Think of SQLite not as a replacement for Oracle but as a replacement for fopen()".

True.

Point is, most software has only the need for fopen(), but instead uses something like Oracle.

I guess I don't mean more features. I just mean I want to write the same code and put in heroku or whatever and have it be ready for production load (10,000s or 100,000s of people hitting it).

The code I wrote has already told the machine what I want it to do. I don't want to be involved in figuring out how to make the DB work on a larger load.

Being serverless isn't a feature for me. Zeo-configuration is. Can't it handle the server stuff without asking me to type more lines of code? Maybe on heroku?

If you're into ORMs, then you can do that, use SQLite in dev and switch to PostgreSQL when in prod on heroku.

You're basically bashing the Smartcar because it can't pull your 5th wheel heavy trailer.

I agree with the analogy, but I disagree with "bashing."

I don't think its a failure.

> I just mean I want to write the same code and put in heroku or whatever

> I don't want to be involved in figuring out how to make the DB work on a larger load.

Sqlite definitely wasn't made to fulfill that need of yours. Why do you think it's supposed to give you that?

I agree it wasn't designed to fulfill that need. I have not claimed (anywhere) that it is supposed to.

Of course, if RDBMS vendors really cared about compliance with ANSI SQL, swapping out SQLite for Oracle or MS or Postgres would be easy. But they're mostly halfhearted about it.

Including SQLite, to be fair. Its dynamic typing can make for rather nasty surprises if you try to import its data into anything else.

Hrm.[1]

> (3) SQLite lets me insert a string into a database column of type integer!

> This is a feature, not a bug. SQLite uses dynamic typing. It does not enforce data type constraints. Data of any type can (usually) be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns. The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column. Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer. An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)

> But SQLite does use the declared type of a column as a hint that you prefer values in that format. So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is called type affinity.

I did not know that.

I am... incredibly horrified.

1) https://www.sqlite.org/faq.html#q3

It's why I don't use SQLite without some sort of ORM layer to do the typechecking and to sanely migrate schema changes.

> Zero-configuration ... also eliminates tuning.

Not quite, SQLite can be tuned using pragma's, https://www.sqlite.org/pragma.html which covers most of your tuning needs.

SQLite 4 supports multiple engines. So hopefully sometime in the future it will be possible to start a project with the awesome default configuration-less, serverless SQLite, and then if & when you run into scaling problems, swap in a beefier engine.

https://github.com/rqlite/rqlite

That depends on what your "full scale loads" are.

SQLite can easily scale out to read workloads that can consume an entire (large) machine, especially when using its mmap'd IO modes.

However, it doesn't have good concurrency for writers and concurrency for readers also drops when any writer is large enough to spill the cache.

Writer concurrency can be managed down by building a custom write pipeline in front of it but that is not trivial. ...and, when you do that, you then have to manage your own consistency and Read/Modify/Write hazards so you're no better off than any of the first generation NoSQL stores. If you think about your data model up front then that might be OK, otherwise it's a very difficult road.

An important other difference between SQLite and other database engines (RDBMS or otherwise) is that SQLite is in-process rather than client/server.

This has a lot of implications for the latency costs associated with data access. In particular, SQLite allows you to mostly ignore query round-trip times: because there aren't any. If you change your engine to something that's not in-process you suddenly start having to think about batching and prefetching for things that might initially look like a simple point query. This can have a serious performance impact and result in profound refactoring requirements for your app. Things that were previously running at several 10s of thousands of point queries per second and complete in nanoseconds now require 10s of thousands of sockets and take 10s of milliseconds.

For my part, I've built production web apps with lots of users on SQLite and the database engine was the smallest part of our performance work. Reducing garbage generation in the render pipeline and moving query generation out of the request path were where we got most of our performance gains.

Regarding concurrency, the folks at Oracle welded sqlites front-end onto berkeleydb. Since bdb does page-level locking you can potentially see much greater concurrency with the bdb implementation.

> why isn't sqlite able to handle full scale loads?

The same reason an Access database doesn't scale. Because it's not designed to.

It doesn't have a complex query optimizer. It isn't designed to use tremendous amounts of memory to optimize execution speeds with a well-managed cache. It isn't designed to be clustered or sharded or partitioned or replicated. It doesn't have GIS or geometry functions. It doesn't support point-in-time recovery. It doesn't support many ANSI SQL functions and features. It's capable of being thread safe, but limited to a single process.

The real limitation that people tend to run into, however, is that writing to the database locks the whole database. You can switch to WAL mode which allows reading while writing, but you never get more than one writer and by default writing blocks reading. In other RDBMSs, you have table locks, page locks, or row locks. You can have multiple writers going on in multiple connections managed by multiple processes with multiple threads, and reading is (in general) not blocked.

Furthermore, databases for high load applications often support multiple, concurrent connections from multiple discrete applications. You might have 3 different web servers all connecting to your SQL servers in the background. You might have a main application and a reporting application. You may need to ETL data from the database to another database for another purpose. You can't do any of that with SQLite.

Hell, I support an app that has six different application servers that connect to it (two main app servers, one secondary app server, two "task" servers that run long execution processes, and a reporting server). And that's just the app itself. There's about a dozen ETL tasks that run against the DB for other applications.

Sure, there are some third party add-ons that can do some of these things (SQLitereplica, SQLitening) but I've never seen anybody implement them instead of moving to a heavier RDBMS, which is likely to be better supported.

> Can't someone change the implementation details so it can work at scale and leave the interface the same?

They could, but then it wouldn't be a lightweight RDBMS anymore. Making a system scale takes more complexity, and therefore more code.

> Where is sqlheavy?

Oracle, MySQL, PostgreSQL, MS SQL Server, DB2, Firebird, Cassandra, MongoDB, etc. With varying degrees of heaviness.

> you never get more than one writer

Is this a file system issue (because only one file is used) or a SQLite problem? Like, file systems only allow one writer to a file, even if they would write at different locations.

Succinctly, sqlite3 uses database locks for write transactions. This means only one writer can exist at a time, limiting concurrency.

Secondly, sqlite is an embedded database and doesn't have a separate server process.

I recently hacked at sqlite to support some features I wanted (being able to retrieve same named columns from different tables in jdbc result sets and implementing some mysql functions)

and it was not fun. I guess maybe it says something that as someone with very limited c exposure I was able to get the column hack working in a night, but honestly following the code around was a chore. Lots of conditionals in a single function meant jumping back and forth a lot.

Also, as a primarily java programmer, I find what c programmers think is a descriptive variable name is a lot different than what I think is a descriptive variable name, but that's a personal issue.

Cached: https://webcache.googleusercontent.com/search?q=cache:http:/...

The post says the first code snippet's variable naming is easy to understand. Really? To me it's unreadable despite ticking all the best practice boxes. I mean there are no comments and all the variable names seem to be as short as possible, it's hard to follow exactly what and why it's doing things to me at least.

* No comments

* No line jumps

* Cryptic variable names

* Magic numbers

* Multiple return points in the middle of the function

Yeah, this code might be doing good things, but I wouldn't be thrilled to inherit it in a project. I mean it's not bad code per se, we all have written things like this; it's just not something to point at for exemplary code either.

The only numbers I see are 1 and 0. Surely, you don't mean we should redefine 1 and 0!? :P Those numbers are hardly magic.

The function comment header describes the purpose of the major variables used in the function.

While the variables names may seem cryptic, they appear of a consistant format (within the function) so I bet the larger codebase is equally consistent in their usage -- and adapting to the codebases convention would be made easier.

It's a little compact for my taste (eg: the mulitple early returns on error, I think I'd like a blank line after a return, to make it stand out a bit more -- but I'm not sure. I don't write C on a daily basis).

The only other thing that strikes me is the convoluted calculation of nBytes, from (sizeof(char * ) + sizeof(i16) + 1) multiplied by the n-argument for resize. Granted nBytes is just used for the custom malloc-call, but I get a little scared with that and the naked mix'n'match of sizeof's in there.

It seems like that (and maybe a few of the other things) could've been DEFINE-d in a header, and or factored out to easily in-line-able functions -- that might have provided an (even) clearer picture of what's going on.

SQLite has it's roots in Tcl/Tk which is written in the same style. Personally I find it hard to read and sometimes it does not even look like C to me, but it is very consistent.

Although there are places in Tcl/Tk ecosystem where using LISP bracing style (ie. last line of function being " return xFoo}}}};") in C seems acceptable.

I think a testament to the quality of SQLite is its flexibility. I was given the task of building a distributed, HA databases that ran without human intervention. I was able to adapt SQLite to run on a different storage backend in the area of days as opposed to the weeks or more it would take with another system.

In addition to this, I was even able to port SQLite to the Linux kernel: https://github.com/sargun/ksqlite -- and IMHO, it's really neat that in <1000 lines of code I could adapt this database to run in kernel space.

Why would I want sqlite to run in the kernel? I'm asking out of genuine curiosity.

For fun. More seriously I was interested for two things: 1) Policy framework - every policy framework has its own wacky way of specifying matches, and actions. SQL turns out to be pretty good at this. 2) For some other projects I needed an in-kernel virtual machine. I needed something written in C, that had a no libc requirement. My choices were nekovm, and SQLite at the end. I decided to take out two birds with one stone.

He's not as skilled as he thinks he is. And I can't even comment on the post, it returns a permissions error. Here was my comment:

1) Where's the unit test of this function? :)

2) The function could be purer. For example, sqlite3DbMallocZero could be passed in as an anonymous function. That way you could actually unit-test the out-of-memory condition that returns SQLITE_NOMEM_BKPT ... without actually being out-of-memory. ;)

Is this a joke?

> No extra comments in the body.

Yeay no comments!

> The function body is well indented.

It clearly isn't. There's no whitespace to separate the lines making it hard to read, and the if bodies are on the same line as the if statements which is also hard to read.

I love the indentation. Much easier to read than a bunch of nested ifs and whiles and good to see a lack of outdated comments inside the method body. So a line-by-line read of that code seemed quite enjoyable to me.

Reply on news.ycombinator.com