15

Before I ask the question, let me first describe my thoughts about SQLite.

I happen to like tools that are small, fast and, more importantly, have only the really necessary functionality. That's why I like SQLite and I like MS-SQL a bit less.

For example: MS-SQL may have much more functionality, scalability, etc. etc., but it can also be a pain to install if you're unlucky. Of course, I'm not saying that a difficult installation is a reason to not choose a particular database.

Don't understand me wrong: MS-SQL is a fine quality product. I'm very experienced with MS-SQL; I understand the product very well as a professional. I just prefer it less in some circumstances where it's not really needed (= not many users, < 10-15).

How much of a database's functionality do you really use? In my experience it's often just the normal SQL (SELECT, INSERT and UPDATE).

I like SQLite. Is fascinating fast. It's extremely easy to "install". I think SQLite can do more than it claims it can do. Why only use it for one-process/single user applications? After all: not many applications are constantly accessing a database.

For example: consider a ERP application with, let's say, 15 users. Why can't SQLite used for that? Let's face it: in my professional experience most of the time users of this kind of application will access the database for about 5-10% of the total time they are using the application. In the other 90-95% they are just watching the information on the screen, entering data in a grid/form and when they save their input that's not more than 1 second of database time. F.e.: 1,5 minutes of input time vs. 1 second of saving time.

If the SQLite database file is locked during the "saving time" other users that need to access the database just wait, but they don't notice that because the waiting time will be very small (unnoticeable). In code you just have to deal with the possible "busy" time of the database, to avoid exceptions, but that isn't difficult to do.

Some guy, who must think the same like me, has even build a client-server solution for SQLite: SQLitening. This made me more convinced that I may be not fooling myself.

Of course there are database intensive applications where SQLite doesn't fit. But as I now think about it, many multi-users applications, if they don't exceed 15 users or so, should do just fine with SQLite.

Many of our customers don't spend much on hardware, so I often encounter a sole server with everything on it (Exchange, SQL(s), clients, etc.) and because of that are almost "out of breath". If I could deliver a product that hasn't high system requirements, then my customer would be happy. SQLite doesn't add any weight (at least not much), MS-SQL does. So I would not choose SQLite because it's free, cheap, or easy to install. I would choose it for practical/technical reasons.

FYI: In my profession we sell products (custom and standard, mostly ERP related) to customers where, on average, no more than 5-6 people will use the product. There are some exceptions, but no more than 10-15 users.

Question is: Am I right in thinking that I can use SQLite for some multi-user application like the example I describe? Are there any technical disadvantages which I should know of? What are your experiences (negative or positive) that will help me making the right choice?

Update: Please don't see this as a negative judgment of other databases. They are mostly all fine products. Just sharing my thoughts here and interested in your opinions about this.

  • 9
    Sorry, but adding "Am I right?" does not turn a rant into a question. – pdr Jul 28 '11 at 08:05
  • 1
    @pdr: Why do you consider this a rant? I'm not negatively judging MS-SQL or other databases; they are mostly all fine products. I'm just sharing my thoughts and are interested to hear other fellow programmer's opinions. Nothing more, nothing less. –  Jul 28 '11 at 08:16
  • 3
    There is no question there, only a search for validation. From the FAQ: "You should only ask practical, answerable questions based on actual problems that you face. Chatty, open-ended questions diminish the usefulness of our site and push other questions off the front page." http://programmers.stackexchange.com/faq – pdr Jul 28 '11 at 08:22
  • 1
    @pdr: I understand that, but I honestly meant to ask a question here. Maybe I wasn't clear, so I rephrased the question. –  Jul 28 '11 at 08:29
  • Much better question. I can't find a way to remove my vote to close, but you're much less likely to get more votes now. – pdr Jul 28 '11 at 08:44
  • @pdr: I appreciate you remarks; don't want to misuse a great site like this. BTW: I suppose you meant to say "much more likely" than "much less likely"? –  Jul 28 '11 at 08:47
  • :) I meant: much less likely to get more "votes to close". – pdr Jul 28 '11 at 08:50
  • 4
    Database choice, code optimization, client, server or web based application, these are all areas of consideration and the pros and cons need to be discussed on a platform like this. To me a rant is more when someone categorically refuses to find any redeeming quality in a particular technology. – JeffO Jul 28 '11 at 12:01

6 Answers6

12

I think it is great to use when you need an "internal" database. That is, a database your application/code will interact with but that will not be directly related to the main reason your application exists. Instead of using huge in-memory mappings, or cache managers, you may as well use such a database for example. I have a very concrete example of this, as I recently used it in JUnit/DBunit test cases where I needed to connect to a database, perform some work, read data and erase everything at the end. Since you only need to create an empty file to create the database, that was fairly easy to do.

Another usage I see: when you have only one user. Yes, that is possible, think "Firefox" or "Opera" for example :-)

Also, on the SQLite website they are very honest about that and give reasons when to NOT USE IT (see paragraph "Situations Where Another RDBMS May Work Better")

ps: related to the comments on Sql Server Express, yes it needs to be "installed". And I had some trouble updating it personally (I had to manually remove some registry keys related to the previous version in order to be able to install the 2008 R2). However, if you want to compare SQLite to a database developed by Microsoft, look at Sql Server Compact Edition, which you don't need to install (see "private-file based deployment").

Jalayn
  • 9,789
  • 4
  • 39
  • 58
  • I looked at CE, but somehow it seems that SQLite is better/faster/easier. Don't know for sure, I didn't used/tested CE enough to be sure. –  Jul 28 '11 at 10:15
8

There are plenty of instances where SQLite is plenty. The user, department, or company rarely outgrow it (We don't hear about it as much because no one calls a programmer if the application is working just fine.) You could make the same arguement for an MS Access file (Windows) or SQL Server Compact Edition (some installation required). They're all good enough for a local application because you don't have to be as concerned about the security of the file.

In a multi-user scenerio on a local network, the file is going to a shared folder that is going to need access by all users - call security. Simple maintenance or any table structure changes like backups or add a column are going to prevent other users of accessing. Last night the backup didn't work because someone forgot to exit the application. What happens when you want to do a backup in the middle of the day? Everyone rationalizes that they don't need any backups during the day because of the technical limitations of their database. At some point, installing SQL Server Express/other equivalent on your server will take some initial setup and security configuation, is more involved up front, but little maintenance is going to be required.

There's always the concern for scalability/over-engineering. Even if the number of users or amount of data is still managable, someone always has the idea of utilizing the live data on some intranet or other website/browser interface. File databases present problems. It only takes one person who wants to access the data file over a VPN (the app is installed on their laptop) to give you a 'scaling' problem. You can build the app to allow for disconnected users who can sync-up when they return. Just doesn't seem worth it for the occasional out of office user.

JeffO
  • 36,816
  • 2
  • 57
  • 124
  • You could make the same argument for SQL Server Compact Edition, but *not* for an MS Access database. Access databases are treated like they are a real database, but work more like shared files. They are a fragile solution; SQL Server Compact is actually a better, faster, more reliable alternative that still works with Access frontends. I suspect that SQLite is substantially more durable than an Access database, even though it is technically a shared file solution. – Robert Harvey Feb 22 '18 at 21:34
  • @RobertHarvey - We have business demands where MS Access has been a good enough (i.e. better than Excel) solution for 10 years. When a major deal is made, we have to have something up and running. Power users with Access skills are much easier to find and leverage. The functionality has to be there by a certain date, but we're fortunate that scalability, performance, data growth and security have never been a factor. Upgrading Access, now that's a different story. – JeffO Feb 23 '18 at 14:02
  • Don't get me wrong; I think Access is great. But SQL Server Compact would be my minimum backend requirement for any new Access applications where users share data. – Robert Harvey Feb 23 '18 at 15:43
  • @RobertHarvey - I'll have to look into that. Thanks – JeffO Feb 23 '18 at 18:07
5

For example: consider a ERP application with, let's say, 15 users. Why can't SQLite used for that?

Very few applications have that few users forever. And for anything seeing more users, and more complex data manipulation, using SQLite is completely out of the question for performance reasons due to the simple "one write transaction at a time" locking model.

Let's say you have 100 users, each working for 60 seconds filling out a form and then submitting it. So you need to process about 1.6 transaction per second. The data model is complex and saving a form involves reading from and writing to many large tables, perhaps even communicating with a different system, so each "submit form" results in a transaction that takes 2 seconds. But SQLite can't process transactions concurrently, so this means it can only process 0.5 transactions per scond. Oops.

"Can be a pain to install" is not a good reason to decide against a critical piece of infrastructure. Besides, there are other DB engines to choose from, at least two of which (MySQL and Postgres) are free and don't have the concurrency limitations of SQLite. They may even be easier to install than MS-SQL.

Michael Borgwardt
  • 51,037
  • 13
  • 124
  • 176
  • I understand and agree. But in my profession I really don't have customers which use our products (standard and custom, mostly ERP related) with more than 10 people. On average it's even 5-6 users. I'll rephrase my question. –  Jul 28 '11 at 08:38
  • 4
    @Marcus V: The question is: if a customer has grown a lot and finds that the app you built becomes impossibly slow to use, and you tell them the reason is that the DBMS can't handle that many users, and they ask "why didn't you use a better DBMS", how do you think the answer "those are hard to install" would be recieved? I can tell you what my reaction would be: I'd think "this is an amateur. I need to find someone else to write my software". – Michael Borgwardt Jul 28 '11 at 08:48
  • You're right. I don't think you meant it that way, but I can assure you that I'm not an amateur. I definitely will user "real" DBMS systems if the situation would ask for it. Our products are licensed for a number of users. I would only develop using SQLite if I know that the number of users are relatively small (< 10-15). If the customer would exceed a limit, which in our customer base will not happen soon, we can always relatively quickly/simple convert them to an other database. That's not rocket science ;). Based on your remarks, I rephrased the question to make it more clear. –  Jul 28 '11 at 09:08
  • The user who cries foul about outgrowing the application was probably informed about the user limitations but chose to go the cheaper route. It's all fine on the intial setup, but how happy will they be when you have to charge them another fee to install on the new server, when they could have just copied a file? – JeffO Jul 28 '11 at 11:56
  • 1
    @Jeff O: I guess you misunderstood my intentions. I'm *not* choosing SQLite because it's free, cheap and/or easy to install. I would choose it only because it's small, fast and resource friendly. So it's mainly for practical/technical reasons. Many of our customers don't spend much on hardware, so I often encounter a sole server with everything on it (Exchange, SQL, etc) and because of that are almost "out of breath". If I could deliver a product that hasn't high system requirements, then my customer would be happy. SQLite doesn't add any weight, MSSQL does. –  Jul 28 '11 at 12:04
  • @Marcus V - this was in response to the answer, not your question. – JeffO Jul 28 '11 at 12:15
4

I think SQLLite is excellent for application development, it's biggest strength is that it doesn't need to be installed on the client.

However, don't underestimate SQL Server Express either, it's an free excellent database with most of the features of ordinary sql server with only a limitation in database size (which is hard to exceed) together with the ability to use the excellent tools of normal sql server.

It's biggest downside afaik is that you need to install it, I'm a little bit unsure of that part though, might be a way around it now

Homde
  • 11,104
  • 3
  • 40
  • 68
  • 2
    You're right. MS-SQL Express is a fine quality product. It's just that I find it a bit bloated and using too much resources. In nowadays PC's/servers that isn't a problem. I'm just and old-school person that still thinks that more powerful hardware doesn't mean that I should neglect/waste resources if I can avoid it. Less it better ... ;). –  Jul 28 '11 at 08:22
  • 2
    the database with DB engines can optmize the access by caching it memory rather than reading/writing from disks. But I am not sure about the performance for in-process DB like SQL Lite – sarat Jul 28 '11 at 08:25
  • 1
    @sarat: Afaik SQLite does use memory caches intensively. –  Jul 28 '11 at 09:32
2

I don't consider SQLite as a serious database if you are dealing with a few GB of data every hour. It gets painfully slow and brings down the performance of the entire Application. Sorry, but I don't agree with your fascination for SQLite :-)

Geek
  • 3,951
  • 1
  • 24
  • 29
  • 1
    I respect you opinion. I'm just a practical man. When I choose a tool I choose it because I think it's the most realistic/practical decision. I'm not fascinated with SQLite, but do admire the way they managed to put so much in such a small, efficient and fast package. Like I mentioned in my question: if MS-SQL is a better tool for a particular job, then I would simply choose that. But, like I explained, in many occasions I truly believe that SQLite just suited fine. –  Jul 28 '11 at 12:14
  • That amout of data ussage is a big if. – JeffO Jul 28 '11 at 12:23
  • @Jeff O: Right. I would only choose SQLite if the number of users is relatively low (< 10-15) and also the total amount of data isn't high. In our experience the total database size is often 300-400MB and almost never > 1GB. –  Jul 28 '11 at 12:31
1

The problem with databases is that they tend to accumulate more and more data. Choosing a lightweight DB introduces a risk that your tool will not scale properly.

quant_dev
  • 5,117
  • 2
  • 22
  • 26