3 ms·

> Right now I'm basically concatenating strings Well, you're using query parameters, but I think I understand what you mean. What I would do is write a stored

> Right now I'm basically concatenating strings Well, you're using query parameters, but I think I understand what you mean. What I would do is write a stored procedure that takes whatever parameters, then I'd write PG SQL to generate bindings to that procedure for whatever language you're using (Golang here). I might write the bindings by hand if it's not a lot of work, but definitely would write a little compiler if it's a tone of work. That way you can get type-safety.

bbkane 4y ago
Thank you. I'm using SQLite3 to keep deployment simple, which unfortunately doesn't support stored procedures. Do you have any other suggestions to improve this code?
- cryptonector 4y ago
  SQLite3 very much supports something like stored procedures though! Only, instead of calling them that it calls them `INSTEAD OF INSERT` triggers. So, you'd do something like: CREATE VIEW IF NOT EXISTS things AS ...; CREATE TRIGGER IF NOT EXISTS create_thing INSTEAD OF INSERT ON things FOR EACH ROW BEGIN ... END; and to invoke such a "stored procedure" you'd INSERT INTO things (...) VALUES (...), ...; or `INSERT .. SELECT ..;`. Now, with SQLite3 you don't get reflection via SQL, like you do in PG via the `pg_catalog` schema, but you do get reflection via the API. Also, because it's SQLite3, you don't get strong typing, and you get very few types, so even with reflection via the API, generating type-safe stub functions to invoke the "stored procedure" is not exactly easy or possible. FYI, SQLite3 is Turing-complete. You can loop via recursive triggers. And you can do conditionals via `WHERE` clauses on the statements in your trigger bodies (and also via `OR IGNORE` and such). EDIT: I made a post of this: https://news.ycombinator.com/item?id=31913062 https://news.ycombinator.com/item?id=31913062

bbkane 4y ago

Thank you. I'm using SQLite3 to keep deployment simple, which unfortunately doesn't support stored procedures. Do you have any other suggestions to improve this code?

cryptonector 4y ago
SQLite3 very much supports something like stored procedures though! Only, instead of calling them that it calls them `INSTEAD OF INSERT` triggers. So, you'd do something like: CREATE VIEW IF NOT EXISTS things AS ...; CREATE TRIGGER IF NOT EXISTS create_thing INSTEAD OF INSERT ON things FOR EACH ROW BEGIN ... END; and to invoke such a "stored procedure" you'd INSERT INTO things (...) VALUES (...), ...; or `INSERT .. SELECT ..;`. Now, with SQLite3 you don't get reflection via SQL, like you do in PG via the `pg_catalog` schema, but you do get reflection via the API. Also, because it's SQLite3, you don't get strong typing, and you get very few types, so even with reflection via the API, generating type-safe stub functions to invoke the "stored procedure" is not exactly easy or possible. FYI, SQLite3 is Turing-complete. You can loop via recursive triggers. And you can do conditionals via `WHERE` clauses on the statements in your trigger bodies (and also via `OR IGNORE` and such). EDIT: I made a post of this: https://news.ycombinator.com/item?id=31913062 https://news.ycombinator.com/item?id=31913062