I was looking at SQLite for a product I’m working on. It looks awesome and has improved significantly since I last looked.
The reason I decided against it is that it doesn’t have proper stored procedures. I use them a lot in PGSQL. They result in far fewer lines of code.
They also have the benefit of significantly reducing round trip calls to the database, which is one of the key advantages of SQLite.
But having used stored procedures for years, I can no longer bear the thought of writing SQL code in a host language, so I’m going to stick with PG for the time being.
Would be great to see something similar in SQLite; there are other advantages such as the single file database, that would work well in a microservice environment.
So you want to write functions in SQL?
With SQLite you can define your own functions which can be called in your queries, but they do need to be written in your application language.
Yeah I know. But I find stored SQL to be a lot more concise than the application language, and less prone to bugs. I also find that it creates a much better separation of concerns between stuff that touches the database versus non-database stuff.
Just one example, error behaviour is well defined in PLPGSQL so I don’t have to constantly check for errors. That’s not true in my host language.
I am super impressed with SQLite, it’s just not a good fit for how I use databases yet.
The reason I decided against it is that it doesn’t have proper stored procedures. I use them a lot in PGSQL. They result in far fewer lines of code.
They also have the benefit of significantly reducing round trip calls to the database, which is one of the key advantages of SQLite.
But having used stored procedures for years, I can no longer bear the thought of writing SQL code in a host language, so I’m going to stick with PG for the time being.
Would be great to see something similar in SQLite; there are other advantages such as the single file database, that would work well in a microservice environment.