undefined | Better HN

0 pointsmekster4y ago0 comments

The risk is as I had written previously that it takes some effort to move away from a db to another when the need arises when I see no benefit in choosing SQLite in the beginning.

I'm not a professional db engineer but one point is that there doesn't seem to be a way to create functions in SQLite which would mean creating triggers on various tables can cause excessive amount of duplicate code.

If I rely on PostgreSQL, I feel covered for my use case for web apps but once you hit some little gotchas in SQLite, you may regret about saving 10 minutes (install db and set up a password) for nothing.

0 comments

post-it4y ago

If a project of mine ever gets enough traffic to force me away from SQLite, I expect I'll be rolling in dough and willing to put in the effort.

TheRealPomax4y ago

That's not a risk, that's just an inefficiency further down the line (migrating data from sqlite to a "real" database can indeed be quite a chore, but far less so if you formalized your schema and constraints beforehand, so that a migration mostly involves exporting to SQL, rewriting where needed, and then importing into whatever dbms you end up using later on in the lifetime of your project).

When we're talking about risks, think security exploits: how is sqlite3 more likely to get your data leaked, or flat out copied in its entirety, compared to using a mysql/postgres/etc.

meksterOP4y ago

Inefficiency or not, if you start down the path of SQLite, you need to invest good amount of time refactoring into another DB if you feel like migrating away.

When I realized SQLite would store any type of data in any kind of column type, it was obvious SQLite is different from others. They only added strict types about a year ago but scared me enough not to use it.

And how is SQLite any less secure? You can flat out copy its entirety using pg_dumpall. I'm not talking about security.

TheRealPomax4y ago

While that's an unfortunate discovery, I do need to point out that that's a problem of your own making, not SQLite's fault. SQLite is very clear on what you can expect, with dynamic typing being one of the main differences between it and "regular SQL" databases.

Yes, plenty of folks just go for SQLite blindly, but the consequences of that aren't SQLite's doing: if you take the time to read through https://www.sqlite.org/features.html because you want to know what it can actually do, you'll almost certainly click through to https://www.sqlite.org/omitted.html because you'll want to know what it doesn't do, and then you'll see the "See also the Quirks, Caveats, and Gotchas of SQLite." link and you're going to follow it an read through https://www.sqlite.org/quirks.html because those sound pretty important to know about before you commit to using something that is going to be your application/service data store for the foreseeable future.

j / k navigate · click thread line to collapse

0 comments

post-it4y ago

If a project of mine ever gets enough traffic to force me away from SQLite, I expect I'll be rolling in dough and willing to put in the effort.

TheRealPomax4y ago

When we're talking about risks, think security exploits: how is sqlite3 more likely to get your data leaked, or flat out copied in its entirety, compared to using a mysql/postgres/etc.

meksterOP4y ago

Inefficiency or not, if you start down the path of SQLite, you need to invest good amount of time refactoring into another DB if you feel like migrating away.

And how is SQLite any less secure? You can flat out copy its entirety using pg_dumpall. I'm not talking about security.

TheRealPomax4y ago

j / k navigate · click thread line to collapse