5 ms·

Someone on Reddit suggested stored procedures, which seems like a good alternative. Alas, SQLite doesn't have them, so query building it is.

musingsole 5y ago
Stored procedures are a nightmare that shepherd your application into an illegible, unmanageable monstrosity. Stored procedures are the slipperiest slope I've seen as a developer.
- empthought 5y ago
  Sounds like you’re pretty bad at writing stored procedures.
  - freedomben 5y ago
    Sounds like you've never worked on a team.
- BoxOfRain 5y ago
  That's a bit of an absolutist stance I think, a good programmer can use stored procedures just fine and in some cases it can even improve performance. Yes it makes it easier for bad programmers to write bad code, but bad programmers will write bad code no matter what tools they have in their toolbox.
- taffer 5y ago
  In my experience, such problems only occur when people believe that common software engineering practices do not apply when writing stored procedures. Just have your stored procedures version controlled, tested, and deployed like any other code.
  - kbenson 5y ago
    The real problem is that you're almost always shifting work from a language that is well known and understood by you and/or your team to one that is less, or even poorly, understood and known, and you end up incurring the cost of novice programmers, which can be a real problem for both security and performance. If you have good knowledge and experience in the language your preferred version of SQL implements, that's good. If you just have people that understand how to optimize schemas and queries, you might find that you encounter some of the same problems as if you shelled out to somewhat large and complex bash scripts. The value of doing so over using your core app language is debatable.
    - taffer 5y ago
      If your front-end is written in React and your business logic is written in SQL, is it really fair to call what's left in the middle tier a "core app language"? If you're writing a SaaS application today, you're more likely to want to rewrite your Java middle tier in Go than replacing your DBMS.
      - kbenson 5y ago
        Not everyone is making a web app, or even something amenable to using React native, and even if they are, there's no guarantee that their middle tier isn't also in JavaScript. That said, I wasn't making a case about replacing your DBMS. I specifically avoided that because yes, most people stay with what they know and used, and even if they switch, they switch for a different project, not within the same project. There are some cases where multiple DBMS back-end support is useful, but I think that's a fairly small subset (software aimed towards enterprises which wants to ease into your existing system and note add new requirements, and open source software meant to use one of the many DBMS back-ends you might have). My actual point is more along the lines of: - Most DBMS hosted languages I've seen are pretty shitty in comparison to what you're already using. - The tooling for it is likely much worse or possibly non-existent. - You are probably less familiar with it and likely to fall into the pitfalls of the language. All languages have them, shitty languages have more. See first point. - If you accept those points and the degree to which you accept them should definitely play a role in deciding to use stored procedure you've written in the language your DBMS provides. - I think trade offs are actually similar to what you would see writing chunks of your program in bash and calling out to that bash script. People can write well designed and safe bash programs. It's not easy, and there are a lot of pitfalls, and you can do it in the main language you're writing probably. Thus the reasons against calling out to bash for chunks of core are likely similar to the reasons against calling a stored procedure.
- moksly 5y ago
  This isn’t my experience at all, but I suppose it depends on how you build your applications. I used to be of your opinion, before we moved more and more of our code based from .Net to Python an I was a linq junkie, but these days I think you’re crippling your pipeline if you don’t utilise SQL when and how it’s reasonable to do so. There are a lot of data retrieval where a stores procedure will will save you incredibly amounts of resources because it gives you the exact dataset you need exactly when you need it.
laszlokorte 5y ago
Except if you build a complete query builder as a stored procedure I do not see the problem solved. Simply stated the problem is: Viewing code as data (in a lisp sense) and transforming arbitrary data into a query that can be executed.
justsomeuser 5y ago
With stored procedures you run your code inside of the SQL server process. With SQLite, your entire application IS the process, and your SQLite data moves from disk to your app processes RAM. I think the SQLite model is much better as you get to use your modern language and tooling (which is better than the language used for stored procedures which has not changed in 20 years, and is generally a bear to observe, test, develop).

musingsole 5y ago

Stored procedures are a nightmare that shepherd your application into an illegible, unmanageable monstrosity. Stored procedures are the slipperiest slope I've seen as a developer.

empthought 5y ago
Sounds like you’re pretty bad at writing stored procedures.
- freedomben 5y ago
  Sounds like you've never worked on a team.
BoxOfRain 5y ago
That's a bit of an absolutist stance I think, a good programmer can use stored procedures just fine and in some cases it can even improve performance. Yes it makes it easier for bad programmers to write bad code, but bad programmers will write bad code no matter what tools they have in their toolbox.
taffer 5y ago
In my experience, such problems only occur when people believe that common software engineering practices do not apply when writing stored procedures. Just have your stored procedures version controlled, tested, and deployed like any other code.
- kbenson 5y ago
  The real problem is that you're almost always shifting work from a language that is well known and understood by you and/or your team to one that is less, or even poorly, understood and known, and you end up incurring the cost of novice programmers, which can be a real problem for both security and performance. If you have good knowledge and experience in the language your preferred version of SQL implements, that's good. If you just have people that understand how to optimize schemas and queries, you might find that you encounter some of the same problems as if you shelled out to somewhat large and complex bash scripts. The value of doing so over using your core app language is debatable.
  - taffer 5y ago
    If your front-end is written in React and your business logic is written in SQL, is it really fair to call what's left in the middle tier a "core app language"? If you're writing a SaaS application today, you're more likely to want to rewrite your Java middle tier in Go than replacing your DBMS.
    - kbenson 5y ago
      Not everyone is making a web app, or even something amenable to using React native, and even if they are, there's no guarantee that their middle tier isn't also in JavaScript. That said, I wasn't making a case about replacing your DBMS. I specifically avoided that because yes, most people stay with what they know and used, and even if they switch, they switch for a different project, not within the same project. There are some cases where multiple DBMS back-end support is useful, but I think that's a fairly small subset (software aimed towards enterprises which wants to ease into your existing system and note add new requirements, and open source software meant to use one of the many DBMS back-ends you might have). My actual point is more along the lines of: - Most DBMS hosted languages I've seen are pretty shitty in comparison to what you're already using. - The tooling for it is likely much worse or possibly non-existent. - You are probably less familiar with it and likely to fall into the pitfalls of the language. All languages have them, shitty languages have more. See first point. - If you accept those points and the degree to which you accept them should definitely play a role in deciding to use stored procedure you've written in the language your DBMS provides. - I think trade offs are actually similar to what you would see writing chunks of your program in bash and calling out to that bash script. People can write well designed and safe bash programs. It's not easy, and there are a lot of pitfalls, and you can do it in the main language you're writing probably. Thus the reasons against calling out to bash for chunks of core are likely similar to the reasons against calling a stored procedure.
moksly 5y ago
This isn’t my experience at all, but I suppose it depends on how you build your applications. I used to be of your opinion, before we moved more and more of our code based from .Net to Python an I was a linq junkie, but these days I think you’re crippling your pipeline if you don’t utilise SQL when and how it’s reasonable to do so. There are a lot of data retrieval where a stores procedure will will save you incredibly amounts of resources because it gives you the exact dataset you need exactly when you need it.

laszlokorte 5y ago

Except if you build a complete query builder as a stored procedure I do not see the problem solved. Simply stated the problem is: Viewing code as data (in a lisp sense) and transforming arbitrary data into a query that can be executed.

justsomeuser 5y ago

With stored procedures you run your code inside of the SQL server process. With SQLite, your entire application IS the process, and your SQLite data moves from disk to your app processes RAM. I think the SQLite model is much better as you get to use your modern language and tooling (which is better than the language used for stored procedures which has not changed in 20 years, and is generally a bear to observe, test, develop).