I use SQLite to power up lots of stuff I’m working on. It’s lightweight, fast, simple and well-documented for small projects — like a Postgres but very local. Saves me from having to deal with containers “just to store data”, let alone for moving stuff to other machine where I would also need the permissions to configure and run containers in the first place; whereas all you need to pass SQLite databases along is scp / rsync.
There are a few legitimate downsides to using SQLite. First off, the type system. It’s bad. Luckily, as of a month ago, you can use strict typing instead, which somewhat improves the situation.
That’s a terribly weak argument. No details, no rationale, not objective evidence, no insightz nothing. “It’s bad.” That’s the full extent of the analysis.
I love SQLite in the command line. Being able to import data sets into a db that I can quickly write queries for has saved me a lot of data processing time.
Yes, exactly! I just import the data into SQLite as an in memory db, execute whatever query I want and .output the answer somewhere. It’s so easy and clean.
I’ve built and deployed specifically small applications using sqlite and yeah I agree with everything, but especially the migration pains. Any change becomes difficult and bringing another developer onto a project just slows it to a crawl when db changes are needed. If that can be resolved I could be convinced, but until them postgres4lyf
Other databases have the same issue, try having multiple database containers (without massive speed losses). If application is bound by the performance of the front end, this is a problem, but those really are not what SQLite was intended for.
In the case of database bound applications, SQLite is just as good as any other database, which despite having a client server model can typically only handle a single operation at once.
Are you Larry Ellison? Because then your remarks would suddenly make sense.
It’s really simple: if you can’t avoid spreading your database over many machines, sqlite is not the tool to use. Pick another one. Even if you can run it on a single server, but need authorized network access from multiple backends, it’s not for you (although there are some services built around sqlite that might help). Pick something else. But don’t hate a perfectly fine, well defined tool for existing.
If you actually read the page, it’s intended as a tongue-in-cheek box-checker.
This document was originally called a “Code of Conduct” and was created for the purpose of filling in a box on “supplier registration” forms submitted to the SQLite developers by some clients.
This document continues to be used for its original purpose - providing a reference to fill in the “code of conduct” box on supplier registration forms.
They are serious about the religious stuff. And someone who kicks the concept of a code of conduct with their feet like this is surely not a person that is nice to be around.
In the face of today’s attention, which has included a wave of aggressive responses accusing Hipp of un-Christian behavior – he tells us he updated the preface to highlight the fact that by adopting St Benedict’s rules he was not seeking to exclude anyone.
“Nobody is excluded from the SQLite community due to biological category or religious creed,” he told us. “The preface to the CoC should make this clear. The only way to get kicked out of the SQLite community is by shouting, flaming, and disrespectful behavior. In 18 years, only one person has ever been banned from the mailing list.”
He also said that he considered only retaining the bullet points that would be relevant to the project, but ultimately decided that would be disrespectful to the original text and its author. Seems fine to me.
He also said that he considered only retaining the bullet points that would be relevant to the project, but ultimately decided that would be[ing] disrespectful to the original text and its author. Seems [other religions] is fine to me.
@colonial@Sibbo I’m actually glad I did read the page itself - it’s clearly satire, making fun of how “sacred” others seem to hold their codes of conduct/ethics. I’m glad I read through that - I see no problems with it or in using SQLite.
Thanks, everyone knows they have a weird coc. It obviously only applies to the maintainers/members of the project though and is more of a statement than something that is actually enforced. As a convinced atheist, I also find it pretty weird but see absolutely no reason at all to avoid sqlite because of that. What matters is: Code quality/correctness (which is absolutely superb when it comes to sqlite) and license, of course. Why would I care about the authors beliefs? They don’t even directly benefit from me using their product.
It doesn’t even apply to the members of the project. It’s St. Benedict’s rule, and only chapter 4 of it, relating to the life in a monastery, but “No one is required to follow The Rule, to know The Rule, or even to think that The Rule is a good idea.” It has a whole bunch of good advice for living in a community, interspersed with religion. But if you work together on a project, “be not lazy” is not a bad suggestion. Nor is “Be a help in times of trouble”. Many of those concepts have been proposed in other religions or social contracts all over the world. And it cannot be taken too seriously: “Speak no useless words or words that move to laughter”
As for programmers: Respect your seniors. Love your juniors.
I love SQLite! My current project actually uses it to serve read-only web content - it’s plenty fast, and it’s really nice having everything baked into the executable. No need to juggle a separate database server.
Their docs are also superb - maybe I’m weird, but I like reading about stuff like atomic commit.
I don’t know, SQLite it’s something that makes sense in theory, but I think its easier for ops people to just use a proper database. If you need to move the database to a separate machine, limit permissions, etc. its just easier to do.
SQLite is great for local apps that need a structured way to store their data, but I’m not really comfortable using it for more than that.
The database in a state where it’s violating some assumptions I’m making and I need to manually intervene without taking down my application for example. I need to have an audit trail on the changes being made to the database and who made them. I need to create replicas to implement failover. I need to replicate my application on multiple machines and all the replicas need to have the same view of the data. I need to mitigate the possibility of data leaks if I have multiple tenants sharing a database.
I’m not saying that you’re wrong for using it. I’m just saying that it doesn’t work for everything.
I typically run postgres locally too (in docker), while there’s still technically network overhead there’s not much compared to a real network, plus you can easily move it to another machine without reworking your app to switch from SQLite to postgres.
Personally I never do permissions at the database layer anyway - it’s always done at the application layer.
I also never move the database to a separate server - that adds too much latency. If your database is local, and “hot” tables are cached in RAM, you can do several hundred thousand queries in a split second and having performance like that can drastically reduce complexity in your business logic (and therefore, drastically reduce bugs).
Regardless, I don’t see it as something that is the silver bullet that people make it out to be. Being able to introspect the production database, query it, and generally have a set of tools to properly manage your data as opposed to having everything in a file fully managed by your application is something useful for me that you lose with SQLite.
I’m not sure I understand your point? You can connect to and run queries/etc on the production database in SQLite.
I’m not really advocating for using SQLite by the way - I’ve only ever used it on smartphone apps myself where a full database wouldn’t even have enough RAM to run at all. I’m just pointing out that permissions isn’t a feature I’ve ever found useful.
For example, say I have an invoice table that is written to whenever a customer buys a product. Customers need to have write access to the table in the database. But I don’t want them to be able to write anything they want - there needs to be severe restrictions on what can be written, and those have to be done outside of the database.
Since you have to do some of your permissions outside the database, it’s more reliable to just do all of them there. Splitting things up with half your security in one place and half in another is asking for bugs.
The main reason I would avoid SQLite is the backup system, which essentially takes your whole database offline (for write access anyway) while the backup is running. That’s just not good enough once the database reaches a size where backups take more than a moment. But if you’re not storing much data, or not doing many writes, that’s a non-issue.
SQLite definitely has advantages. It’s often extremely fast for example. The lack of complex features removes performance bottlenecks all of the place and you can do millions of basic select queries per second in SQLite. Obviously not every query is that fast, but a lot of them are especially if you design your indexes/etc properly.
Definitely not a silver bullet, but I do think anyone who writes code that reads or writes data should be at least aware of the basic capabilities of SQLite. It’s free. It’s reliable. It runs literally on any platform (you can even run it client side in a webpage these days). So the only reason to avoid SQLite is if it’s the wrong tool for the job. And you can’t make that judgement call unless you have experience with it.
SQLite should be in every developer’s toolchain, even if you don’t have a use for it right now.
It’s useful for audit trails and the like, generally OS audit logs only tell you who accessed the machine not what they did on the production database. Things like that. Databases like postgres come with admin tooling in general that SQLite isn’t really meant for. As you said, backups as well are a problem.
You are not logged in. However you can subscribe from another Fediverse account, for example Lemmy or Mastodon. To do this, paste the following into the search field of your instance: !programming@programming.dev
Welcome to the main community in programming.dev! Feel free to post anything relating to programming here!
Cross posting is strongly encouraged in the instance. If you feel your post or another person’s post makes sense in another community cross post into it.
Hope you enjoy the instance!
Rules
Rules
Follow the programming.dev instance rules
Keep content related to programming in some way
If you’re posting long videos try to add in some form of tldr for those who don’t want to watch videos
I use SQLite to power up lots of stuff I’m working on. It’s lightweight, fast, simple and well-documented for small projects — like a Postgres but very local. Saves me from having to deal with containers “just to store data”, let alone for moving stuff to other machine where I would also need the permissions to configure and run containers in the first place; whereas all you need to pass SQLite databases along is
scp/rsync.From the article:
That’s a terribly weak argument. No details, no rationale, not objective evidence, no insightz nothing. “It’s bad.” That’s the full extent of the analysis.
Quite weak.
SQLite’s type system could accurately be described as “there is no type system”. And the developers consider it a feature not a bug.
A recently added “STRICT” feature changes that. But it’s very rudimentary.
I love SQLite in the command line. Being able to import data sets into a db that I can quickly write queries for has saved me a lot of data processing time.
The CSV import tool is so useful too. I’ll find myself looking at an excel sheet or something and thinking “if only I could query this like SQL”.
Yes, exactly! I just import the data into SQLite as an in memory db, execute whatever query I want and .output the answer somewhere. It’s so easy and clean.
I’ve built and deployed specifically small applications using sqlite and yeah I agree with everything, but especially the migration pains. Any change becomes difficult and bringing another developer onto a project just slows it to a crawl when db changes are needed. If that can be resolved I could be convinced, but until them postgres4lyf
In a world of containers and stateless applications, fuck SQLite.
… you know you can put SQLite in a container right? It doesn’t even need to be persisted to disk - it can just live in RAM.
If you don’t care about persistence, why are you even using a DB in the first place?
Just learn how to configure your containers.
Please teach me how to configure my containers so SQLite can scale horizontally.
Other databases have the same issue, try having multiple database containers (without massive speed losses). If application is bound by the performance of the front end, this is a problem, but those really are not what SQLite was intended for.
In the case of database bound applications, SQLite is just as good as any other database, which despite having a client server model can typically only handle a single operation at once.
You misunderstood. I’m not talking about scaling the DB horizontally, I’m talking about scaling the application using the DB horizontally.
Are you Larry Ellison? Because then your remarks would suddenly make sense.
It’s really simple: if you can’t avoid spreading your database over many machines, sqlite is not the tool to use. Pick another one. Even if you can run it on a single server, but need authorized network access from multiple backends, it’s not for you (although there are some services built around sqlite that might help). Pick something else. But don’t hate a perfectly fine, well defined tool for existing.
You can always use sqlite cloud
/s?
From their “code of ethics”
There are even more strange ones, but I hope you get the picture. Reason enough to use something else if possible.
I would think that’s satire of insanely long and irrelevant rules documents.
I dk I don’t think you can fake a persecution complex like that.
@Sibbo @chokidar they seem cool to me
Some background: https://www.theregister.com/2018/10/22/sqlite_code_of_conduct/
If you actually read the page, it’s intended as a tongue-in-cheek box-checker.
https://www.theregister.com/2018/10/22/sqlite_code_of_conduct/
They are serious about the religious stuff. And someone who kicks the concept of a code of conduct with their feet like this is surely not a person that is nice to be around.
I fail to see how that’s an issue.
Also, in the very page you linked, he clarified:
He also said that he considered only retaining the bullet points that would be relevant to the project, but ultimately decided that would be disrespectful to the original text and its author. Seems fine to me.
@colonial @Sibbo I’m actually glad I did read the page itself - it’s clearly satire, making fun of how “sacred” others seem to hold their codes of conduct/ethics. I’m glad I read through that - I see no problems with it or in using SQLite.
It’s not satire.
Thanks, everyone knows they have a weird coc. It obviously only applies to the maintainers/members of the project though and is more of a statement than something that is actually enforced. As a convinced atheist, I also find it pretty weird but see absolutely no reason at all to avoid sqlite because of that. What matters is: Code quality/correctness (which is absolutely superb when it comes to sqlite) and license, of course. Why would I care about the authors beliefs? They don’t even directly benefit from me using their product.
It doesn’t even apply to the members of the project. It’s St. Benedict’s rule, and only chapter 4 of it, relating to the life in a monastery, but “No one is required to follow The Rule, to know The Rule, or even to think that The Rule is a good idea.” It has a whole bunch of good advice for living in a community, interspersed with religion. But if you work together on a project, “be not lazy” is not a bad suggestion. Nor is “Be a help in times of trouble”. Many of those concepts have been proposed in other religions or social contracts all over the world. And it cannot be taken too seriously: “Speak no useless words or words that move to laughter”
As for programmers: Respect your seniors. Love your juniors.
@words_number @Sibbo that was one hell of an opening sentence to misread.
Holy crap. I’ve used this thing for years and never had a clue they’re a bunch of fruitcakes.
I love SQLite! My current project actually uses it to serve read-only web content - it’s plenty fast, and it’s really nice having everything baked into the executable. No need to juggle a separate database server.
Their docs are also superb - maybe I’m weird, but I like reading about stuff like atomic commit.
That was a good read, thanks!
I don’t know, SQLite it’s something that makes sense in theory, but I think its easier for ops people to just use a proper database. If you need to move the database to a separate machine, limit permissions, etc. its just easier to do.
SQLite is great for local apps that need a structured way to store their data, but I’m not really comfortable using it for more than that.
SQLite is a proper database.
For single-instance deployments, running SQLite means no overhead due to a network roundtrip, and things just work.
Proper = has actual admin tooling vs. just a file format.
What admin tooling do you need? You haven’t defined any problem requiring a solution.
The database in a state where it’s violating some assumptions I’m making and I need to manually intervene without taking down my application for example. I need to have an audit trail on the changes being made to the database and who made them. I need to create replicas to implement failover. I need to replicate my application on multiple machines and all the replicas need to have the same view of the data. I need to mitigate the possibility of data leaks if I have multiple tenants sharing a database.
I’m not saying that you’re wrong for using it. I’m just saying that it doesn’t work for everything.
I typically run postgres locally too (in docker), while there’s still technically network overhead there’s not much compared to a real network, plus you can easily move it to another machine without reworking your app to switch from SQLite to postgres.
Use the tool for the job. It is simple advice, but it will make your life so much easier.
I do use the tool for the job. I don’t understand your point.
I was just reiterating your point and agreeing with you, bud.
Sorry about that, it seems I unintentionally created a bit of controversy and am being a bit defensive.
Personally I never do permissions at the database layer anyway - it’s always done at the application layer.
I also never move the database to a separate server - that adds too much latency. If your database is local, and “hot” tables are cached in RAM, you can do several hundred thousand queries in a split second and having performance like that can drastically reduce complexity in your business logic (and therefore, drastically reduce bugs).
Regardless, I don’t see it as something that is the silver bullet that people make it out to be. Being able to introspect the production database, query it, and generally have a set of tools to properly manage your data as opposed to having everything in a file fully managed by your application is something useful for me that you lose with SQLite.
I’m not sure I understand your point? You can connect to and run queries/etc on the production database in SQLite.
I’m not really advocating for using SQLite by the way - I’ve only ever used it on smartphone apps myself where a full database wouldn’t even have enough RAM to run at all. I’m just pointing out that permissions isn’t a feature I’ve ever found useful.
For example, say I have an invoice table that is written to whenever a customer buys a product. Customers need to have write access to the table in the database. But I don’t want them to be able to write anything they want - there needs to be severe restrictions on what can be written, and those have to be done outside of the database.
Since you have to do some of your permissions outside the database, it’s more reliable to just do all of them there. Splitting things up with half your security in one place and half in another is asking for bugs.
The main reason I would avoid SQLite is the backup system, which essentially takes your whole database offline (for write access anyway) while the backup is running. That’s just not good enough once the database reaches a size where backups take more than a moment. But if you’re not storing much data, or not doing many writes, that’s a non-issue.
SQLite definitely has advantages. It’s often extremely fast for example. The lack of complex features removes performance bottlenecks all of the place and you can do millions of basic select queries per second in SQLite. Obviously not every query is that fast, but a lot of them are especially if you design your indexes/etc properly.
Definitely not a silver bullet, but I do think anyone who writes code that reads or writes data should be at least aware of the basic capabilities of SQLite. It’s free. It’s reliable. It runs literally on any platform (you can even run it client side in a webpage these days). So the only reason to avoid SQLite is if it’s the wrong tool for the job. And you can’t make that judgement call unless you have experience with it.
SQLite should be in every developer’s toolchain, even if you don’t have a use for it right now.
It’s useful for audit trails and the like, generally OS audit logs only tell you who accessed the machine not what they did on the production database. Things like that. Databases like postgres come with admin tooling in general that SQLite isn’t really meant for. As you said, backups as well are a problem.
While this is the norm, you can have one database in multiple files.
Also, provides benchmarks on a file-based database whithout providing any details on the underlying filesystem.
It’s an interesting format for passing around information that’s best structured as a database, thus useful for one of my current spare-time projects.