undefined | Better HN

0 pointslenwood2d ago0 comments

Just yesterday it occurred to me that it had been a while since I last saw an SQLite post at the top of HN.

I really like the simplicity and speed of SQLite, I've used in both personal and professional projects. For day-to-day work I still end up in Excel, not because I like it more (I don't), but because its ubiquity makes it the lowest friction way to share & explore datasets with less technical stakeholders and execs.

0 comments

gcardinal2d ago

I'm under no illusion I'll suddenly shatter your world views with this, but in case it's as useful to you as it was to me, you might want to check out Metabase[1].

You can self-host and if all you care about is showing data in a digestible format to stakeholders, it's really simple. You can of course go overboard and regret all of your life's decisions with it, but I try and abstain myself.

[1]: https://www.metabase.com/

LoganDark2d ago

I've always been irked by how SQLite relies on text parsing to work. Why is it that I have to write queries in text rather than expressing them in programmatic logic? I have never used a relational database because of this, because I hate them, but they can be more performant than pure structured data, but I hate SQL and the entire idea of SQL and I don't want to write it or learn it or use a system that relies on it. It feels like the wrong approach, on the level of PHP. Is there anything I can do to help this? I don't want to keep passing up SQLite just because of SQL, but I just can't seem to agree with it. I don't want to build strings or have string parsing anywhere in the stack, it just feels wrong.

StilesCrisis2d ago

A "prepared statement" is a precompiled SQL command, ready for bindings and execution: https://sqlite.org/c3ref/stmt.html

You can't precompile your SQL at build time, unfortunately, but you _can_ precompile all your SQL at the very start of your program and then never touch the parser again. This might be a good middle ground for you. It is infra that you can centralize, write some unit tests against, and then not worry about forever.

It's not common because the SQLite parser is lightning fast and it's so convenient to just write out a new query as you need one, versus having one bucket of all queries. But it's an option!

DowsingSpoon2d ago

If you want to avoid string manipulation then you can construct queries with a query builder API like C#'s LINQ. Other languages have similar libraries, e.g., Rust has Diesel.

If your objection is to the SQL language itself then you might find Datalog interesting. Datalog is a logic-based language where you query by writing predicates rather than writing SQL statements. Check out Logica <https://logica.dev>. It's a language in the Datalog family that compiles to SQL.

In both cases, SQL is used only as a low-level IR for interfacing with the database engine.

17186274401d ago

> Why is it that I have to write queries in text rather than expressing them in programmatic logic?

Do you only use graphical programming languages or how is your "programmatic logic" typically expressed?

weird-eye-issue2d ago

I bet you really love LLMs

LoganDark2d ago

I'm torn on LLMs. I've started to use them to accelerate personal development, but they still require a lot of babying and manual assistance. Still, they help a lot.

j / k navigate · click thread line to collapse

0 comments

gcardinal2d ago

I'm under no illusion I'll suddenly shatter your world views with this, but in case it's as useful to you as it was to me, you might want to check out Metabase[1].

[1]: https://www.metabase.com/

LoganDark2d ago

StilesCrisis2d ago

A "prepared statement" is a precompiled SQL command, ready for bindings and execution: https://sqlite.org/c3ref/stmt.html

It's not common because the SQLite parser is lightning fast and it's so convenient to just write out a new query as you need one, versus having one bucket of all queries. But it's an option!

DowsingSpoon2d ago

If you want to avoid string manipulation then you can construct queries with a query builder API like C#'s LINQ. Other languages have similar libraries, e.g., Rust has Diesel.

In both cases, SQL is used only as a low-level IR for interfacing with the database engine.

17186274401d ago

> Why is it that I have to write queries in text rather than expressing them in programmatic logic?

Do you only use graphical programming languages or how is your "programmatic logic" typically expressed?

weird-eye-issue2d ago

I bet you really love LLMs

LoganDark2d ago

I'm torn on LLMs. I've started to use them to accelerate personal development, but they still require a lot of babying and manual assistance. Still, they help a lot.

j / k navigate · click thread line to collapse