15
When I should and should not use Sqlite?
I have a project that I intend to do I have no idea how many tables will be, but I believe that more than 15 with about a thousand lines in some tables.
It would be feasible to do on Sqlite?
3 answers
19
Before you start using a tool you should study it deeply. And after studying it you will have information to decide on your own whether it is viable for your case or not. Don’t trust random people on the internet to tell you what’s good for you or not.
If you study Sqlite you will surely arrive on that page. It tells you where Sqlite can be used. In short:
- Mobile and onboard devices
- File format
- "Small" websites
- Data analysis
- Cache for corporate data
- Database only on the server side
- Data archiving
- Replacement for operations with common files
- Temporary or internal banks
- Replacement for other Sgdbs in test and demonstration tasks
- Training in a database.
It says where nay must use:
- Client-server applications (has how to use if you know how to do)
- High volume websites (there speaks in 1% of sites, my experience shows that it is even rarer)
- Absurdly large data set (but it is rare who has this whole volume)
- High competition (his biggest problem, but few have this need)
That is, Sqlite is suitable for almost all cases. And the cases where it is not suitable, the person will know, because there the problem is so complex that someone inexperienced will not be involved. And if that happens, the wrong choice of database will be a small problem next to the others she commits.
Of course he may not please you, but that’s another problem. I use Sqlite whenever I can. It is not as complete in features as other banks. It is great for storage and basic relationship. In general, the resources of other databases may look great at first, but you need to learn everything and use it correctly. Often having only the basics makes work simpler without losing important functionality. Most existing features in more "powerful" products are just facilitators that can help or disrupt. They’re not there without charging a price.
Your case is extremely simple and should have no problems. But it is necessary to analyze how the data will be accessed. Will there be great competition? I doubt it, so I guess you don’t have to worry about it. Will the access be by an application on the same machine? This is important. Sqlite does not do well in accesses being made by another machine.
It cannot be reliably accessed from other machines. In practice it is rare to give problem, but it can happen. The solution is to have an application on the machine that accesses it and allows other machines to communicate with this application (see link above). That’s why websites work well. The HTTP server and the website code just follow this model. Why don’t people follow the same model for other types of application? Probably because they follow cake recipes and don’t think outside the box. Or maybe because they don’t know how.
This is the real decision whether to use it or not. The data volume is secondary.
Then study the product to understand the limitations it has on extra facilities, volume of competition (real), and remote direct access. Knowing this you will know where you can use it or not. The official website is very informative.
An example of using Sqlite on a large scale. Some people don’t use it, and they think it doesn’t scale, that it can’t be used in almost every scenario in place of more complete solutions. It just isn’t good with a lot of writing competition and for auxiliary database tools. Interestingly, these people often believe in Nosql that they are usually worse at reading than Sqlite. People need to think outside the box, investigate before making claims that reach their credibility.
-
For a ease with the use of this bank has the
com.orm.SugarRecord;helps a lot in creating tables.. and for reading, changing and deleting data. Site Do sugar -
1
It may be useful, but it is limited to a specific use of it.
-
1
@Marconciliosouza took a peek, and it seems much more complex than using his native interface (it’s hardly going to be easier to use than the native Sqlite interface that already has everything ready). To understand better, what would be the advantages of this indication of yours, compared to the native?
-
Thank you all!
-
1
@Marconciliosouza but I already say that I even liked the abstraction of things without using SQL. For certain things it may be interesting (but not as a general replacement for native functions).
-
@Bacco, in some functionality, is useful, but not as a general replacement for native functions, for example in creating tables, I simply create my class the sugar itself turns to generate the table in the database... Good more to talk about one or the other’s functionality would be something for a more elaborate question and answer.
-
@Marconciliosouza of course, I just asked to see if there was any point in particular that you recommend. But here you are separated for me to read better later. I will try to understand where this recommendation can fit well.
-
@Maniero excess analysis cannot become a barrier to execution?
-
1
@Can’t over-execution be detrimental to a good result? Each one chooses what he thinks best, I never go the worst way because delivery faster. I deliver quality. This goes through good performance, lower cost, better usability, easier operationalization, and overall simplicity. Of course this has an initial cost to learn how to do. In general doing anyway is the excuse of those who do not want or can not learn right, even because the right always goes to be easier, after the outdated learning curve.
12
Sqlite vc uses if you need to have a data scenario in disconnected mode.
Example: You have your corporate system (15 tables with 1000 records each), but need to go to the field (disconnected environment) use an application with data from your system.
Then, you create a Sqlite base on the device that goes to the field (mobile, laptop, etc.) and brings to that database only data that will meet the need for this trip to the field. And once this device returns to the enterprise network, vc creates a synchronism logic to bring the Sqlite data back to the main database.
-
Thanks for the clarification!
-
1
This is a simplification of the problem.
-
6
+1, I do not see why of so many downvotes, your answer is not totally wrong, some companies use the sqlite for this, but the sqlite has power to do much more, besides supporting up to 140TB it is chosen by several for its accessibility since it has native support on various Android Developers, iOS, Windows Phone, Blackberry
-
The answer leads a person who does not know about the subject believe that Sqlite is this, which is a limitation of what it can do. This is just a scenario. Nor is the scenario proposed in the question. The acceptance was made by a user who does not understand the system and accepted the first posted reply without understanding what this meant.
-
@bigown, Sqlite is a powerful self-contained bank. Banks like this are for specific purposes, when there is no client-server scenario (as you said yourself). So it’s ideal for working in disconnected environments (in the field). The question was: "What to use or not?" I replied: "Use whenever you need to use a BD with the characteristics of Sqlite.". What was not to your liking?
-
But this is incorrect, or at least limiting. It is not a matter of pleasing me or not, it is a matter of saying that the Sqlite is made for this scenario, when in fact it is made for several. I use Sqlite in client-server environment very well. And several other scenarios.
-
2
But @bigown, Sqlite has limitations. You yourself cited "It cannot be reliably accessed from other machines. It can give problems.". In itself website of them they do not recommend client-server scenarios.
-
Every product has, but it is not nearly as limited as its response indicates. I put that quote in my answer, and I showed that it’s not real. Just know how to do it. That’s the problem with the answer. It limits by ignorance of how to use the tool correctly in the various possible scenarios. The fact that one does not know how to do it does not mean that it cannot be done. The site does not recommend direct use, but indirectly it is possible to use without any problem. And it’s actually used and they recommend it in a client-server scenario, which is the website.
-
1
@bigown, quoting from the official website: "Sqlite strives to provide local data Storage for individual Applications and Devices. /Sqlite is dedicated to providing local data storage for individual applications and devices." - Disconnected environment, as I replied. That’s what it’s made for, and that’s what it’s meant to be used for.
-
1
@bigown, use Sqlite for website, only if vc host the BD on the same website host (on the website it says the official website is made like this, shared host). Other than that, will be created a middleware (ex:API) to satisfy. Architecturally, this is bad. Application server must be different from repository server. And in this environment, Sqlite does not meet.
-
But that’s the problem, you’re taking a sentence of what’s there in isolation, you’re not reading the whole site, you’re not seeing what people actually do. And you’re ignoring what I’m saying here. You’re taking things out of context to give the wrong answer. Programming is finding a solution, not slowing down fragments of text to conform and say it has no solution. The problem with the answer is just this attitude of clinging to a detail as if it defined the whole.
-
Now you’ve started pouring the "that’s right, that’s wrong" crap, then someone asks why and the answer is "because I read somewhere that’s so". Things need to be used according to facts not with achismos, according to recipes without context. 99.99% of sites have banks next to the HTTP server. The same goes for other types of applications. Not that I think I should, but if you need to create a middleware, create it for the right reasons. There’s nothing wrong with this. I never said that the Sqlite meets all scenarios. Meets almost all. You said that it meets only one!
-
Apart from the inconsistency of saying that middleware is bad and that application server and repository should be separated. First all this is silly little rule that doesn’t look at the context, doesn’t look for a solution, and then the middleware is just to make this separation.
-
3
@bigown, "99.99% of sites have the banks next to the HTTP server." only if it’s 100% that you know about. That’s bad practice. Application and database have to be separated YES. They have different needs and behave different. At no time did I say or meant that middleware is bad, I said that, it is bad to decide to use middleware to hit an inappropriate scenario. And middleware is for integration, not separation.
-
I will not waste time with you who likes to vomit "good practices". I am pragmatic, I do not follow cake recipes. I produce solutions according to needs. Stick to your senseless good practices, I stick to real solutions. Theories don’t solve problems. So much so that people don’t follow what you’re saying, except in the rare cases where they fit or when the person just likes to follow recipes without looking at the context. It is not necessary a cannon to kill bird. But if you like this, you can use in its "solutions", will not disturb me anything.
-
5
A shame to see someone so supposedly influential here in the OS preach that good practices, architecture and Devops responsibility are bullshit.
1
Sqlite can be used whenever you have to store data locally in a structured and corruption resistant way. The Sqlite code is considered one of the best in dealing with local files so as to minimize the chance of data loss, who studies the theme is advised to study its code, so it is even preferable to use Sqlite to try to write file manipulation code, even when it comes to simple data as an application configuration.
Browser other questions tagged database sqlite
You are not signed in. Login or sign up in order to post.
In what kind of application you will use the
SQLite?– Marco Souza
Need to give more details on the application.
– rray
A talk that sums up a lot: https://www.youtube.com/watch?v=Jib2AmRb_rk
– Sildeman Dourado Araújo