4 ms·

I feel like most of the hyperbole would be resolved if people would differentiate between SQLite the storage engine (which is indeed excellent, especially if yo

I feel like most of the hyperbole would be resolved if people would differentiate between SQLite the storage engine (which is indeed excellent, especially if you're working in a language like C or Rust that allows plugging into the virtual tables API) and SQLite the RDBMS, which is very much not and is actually guilty of most of the things people accuse MySQL of. There are plenty of reasons not to use SQLite that have nothing to do with its performance for your workload.

atat7024 6y ago
> There are plenty of reasons not to use SQLite that have nothing to do with its performance for your workload. Go on
- Jweb_Guru 6y ago
  If you like foreign key constraints to actually be enforced automatically (without turning them on in a pragma and/or expecting that everyone who connects to your database does the same or uses a version compiled with custom flags), transactions not to allow an accidental commit after a syntax error, richer indices than btree ones, enforced typechecking without a CHECK constraint, DML commands other than SELECT telling you what rows were returned, etc., the functionality is simply missing in SQLite. Some of it is for backwards compatibility, some not, but at the end of the day it's just not as pleasant to use as something like Postgres as a SQL database. These have all caused issues I actually ran into while using the database, some of which led to integrity violations that took a long time to fix. Sure, they're documented behaviors, but that does not mean they're good behaviors, and they substantially restrict the situations in which I'm willing to deploy it far more than any scalability issues do. If, on the other hand, you're just using it as a transactional storage engine, none of this stuff really matters and I understand the praise--hence why I think the two modes of use should be differentiated. The other thread responding to me is basically all about extending the very barebones provided interface with custom, application-level semantics. I agree that SQLite is excellent for this, but you have to remember that this is not the only thing people want out of a database.
bob1029 6y ago
SQLite's RDBMS is fucking incredible because it doesnt make a bunch of assumptions. I don't think developers are paying attention to the most important feature. SQLite is embedded in your application. You can write user-defined functions in your application logic that are then bound to SQL functions. These functions are then treated just like first-class functions built-in to the dialect: https://www.sqlite.org/appfunc.html https://www.sqlite.org/appfunc.html The way Microsoft exposes this in their provider is really wonderful. Using aggregate UDFs feels like cheating. We have also done some next-level stuff where we can call a UDF that executes arbitrary SQL command (i.e. thus invoking subsequent UDFs recursively). We found that UDFs don't necessarily need to be deterministic and can mutate the outside world... https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/user-defined-functions https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite... If you think along this axis, you would find SQLite as more a blank canvas that you can construct a very domain-specific view of the world upon and then leverage it for implementing complex business logic. You can get from "lots-of-code" to "no-code" applications pretty damn fast if you play this game right.
- yread 6y ago
  I usually try not to use the stuff that's strictly RDBMS-specific so that if I need to move it's not a huge hassle, but DAMN: connection.CreateFunction( "regexp", (string pattern, string input) => Regex.IsMatch(input, pattern)); var command = connection.CreateCommand(); command.CommandText = @" SELECT count() FROM user WHERE bio REGEXP '\w\. {2,}\w' "; var count = command.ExecuteScalar();
  - bob1029 6y ago
    Yes. Also consider the implications of: var myUdfInstance = new MyUdfInstance(connection); connection.CreateFunction( "Execute", (string command) => myUdfInstance.ExecuteSql(command)); I can confirm this actually works. It stacks up nicely in the debugger too.

atat7024 6y ago

> There are plenty of reasons not to use SQLite that have nothing to do with its performance for your workload. Go on

Jweb_Guru 6y ago
If you like foreign key constraints to actually be enforced automatically (without turning them on in a pragma and/or expecting that everyone who connects to your database does the same or uses a version compiled with custom flags), transactions not to allow an accidental commit after a syntax error, richer indices than btree ones, enforced typechecking without a CHECK constraint, DML commands other than SELECT telling you what rows were returned, etc., the functionality is simply missing in SQLite. Some of it is for backwards compatibility, some not, but at the end of the day it's just not as pleasant to use as something like Postgres as a SQL database. These have all caused issues I actually ran into while using the database, some of which led to integrity violations that took a long time to fix. Sure, they're documented behaviors, but that does not mean they're good behaviors, and they substantially restrict the situations in which I'm willing to deploy it far more than any scalability issues do. If, on the other hand, you're just using it as a transactional storage engine, none of this stuff really matters and I understand the praise--hence why I think the two modes of use should be differentiated. The other thread responding to me is basically all about extending the very barebones provided interface with custom, application-level semantics. I agree that SQLite is excellent for this, but you have to remember that this is not the only thing people want out of a database.

bob1029 6y ago

SQLite's RDBMS is fucking incredible because it doesnt make a bunch of assumptions. I don't think developers are paying attention to the most important feature. SQLite is embedded in your application. You can write user-defined functions in your application logic that are then bound to SQL functions. These functions are then treated just like first-class functions built-in to the dialect: https://www.sqlite.org/appfunc.html https://www.sqlite.org/appfunc.html The way Microsoft exposes this in their provider is really wonderful. Using aggregate UDFs feels like cheating. We have also done some next-level stuff where we can call a UDF that executes arbitrary SQL command (i.e. thus invoking subsequent UDFs recursively). We found that UDFs don't necessarily need to be deterministic and can mutate the outside world... https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/user-defined-functions https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite... If you think along this axis, you would find SQLite as more a blank canvas that you can construct a very domain-specific view of the world upon and then leverage it for implementing complex business logic. You can get from "lots-of-code" to "no-code" applications pretty damn fast if you play this game right.

yread 6y ago
I usually try not to use the stuff that's strictly RDBMS-specific so that if I need to move it's not a huge hassle, but DAMN: connection.CreateFunction( "regexp", (string pattern, string input) => Regex.IsMatch(input, pattern)); var command = connection.CreateCommand(); command.CommandText = @" SELECT count() FROM user WHERE bio REGEXP '\w\. {2,}\w' "; var count = command.ExecuteScalar();
- bob1029 6y ago
  Yes. Also consider the implications of: var myUdfInstance = new MyUdfInstance(connection); connection.CreateFunction( "Execute", (string command) => myUdfInstance.ExecuteSql(command)); I can confirm this actually works. It stacks up nicely in the debugger too.