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.
Isn't SQLite a bit underestimated?
database;database development
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.