Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

closed as not constructive by pdr, Jon Hopkins, Wyatt Barnett, Walter, ChrisF Jul 28 '11 at 21:01

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

5  
Sorry, but adding "Am I right?" does not turn a rant into a question. –  pdr Jul 28 '11 at 8:05
    
@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. –  Steve Hogun Jul 28 '11 at 8:16
2  
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." programmers.stackexchange.com/faq –  pdr Jul 28 '11 at 8:22
    
@pdr: I understand that, but I honestly meant to ask a question here. Maybe I wasn't clear, so I rephrased the question. –  Steve Hogun Jul 28 '11 at 8:29
1  
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 Answers 6

up vote 3 down vote accepted

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.

share|improve this answer

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").

share|improve this answer
    
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. –  Steve Hogun Jul 28 '11 at 10:15

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.

share|improve this answer
    
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. –  Steve Hogun Jul 28 '11 at 8: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 8: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. –  Steve Hogun Jul 28 '11 at 9: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
    
@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. –  Steve Hogun Jul 28 '11 at 12:04

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

share|improve this answer
1  
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 ... ;). –  Steve Hogun Jul 28 '11 at 8: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 8:25
1  
@sarat: Afaik SQLite does use memory caches intensively. –  Steve Hogun Jul 28 '11 at 9:32
    
@Marcus Ohh really.. Thanks for the information! –  sarat Jul 28 '11 at 14:51

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 :-)

share|improve this answer
    
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. –  Steve Hogun 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. –  Steve Hogun Jul 28 '11 at 12:31

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.