4 ms·

Replacing Complicated Hashmaps with SQLite

74 points by wilsonehusin 3y ago

deleted 3y ago
[deleted]
chrisaycock 3y ago
The post isn't specific to needing SQLite. Rather it's about the convenience of expressing an algorithm as a query and then using SQLite to execute the query. I agree; queries are why table semantics are so useful. Whether you choose an SQL implementation (e.g., DuckDB and SQLite) or a dataframe library (e.g., pandas and polars), expressing searches, joins, and aggregations becomes trivial for tabular data.
- aleksiy123 3y ago
  I've had this thought that almost 90% of rest APIs are essentially crud that map external API to internal storage layer. With a bit of auth and validation on top. Kinda thinking a lot of rest APIs could just be SQL logic. Postgrest is pretty much like that but I wonder if you could build a more general tool. SQL + protobufs or SQL + OpenAPI schema.
  - adhamsalama 3y ago
    Supabase?
    - aleksiy123 3y ago
      Yeah supabase is similar. But I was more thinking of allowing people to choose any db and be able to expose a rest API on top of that. Postgres, bigquery, supabase whatever. So you can quickly deploy a public facing rest API on views from that data.
  - paulddraper 3y ago
    Postgraphile
    - aleksiy123 3y ago
      Yeah postgraphile looks cool too. But I was more thinking any database. And you still get to define/control a public REST interface.
  - jd3 3y ago
    We are modernizing and migrating a codebase from the 90s that was used for fec filing at the federal + state level and a majority of their business logic was expressed and written as stored procedures in mySQL.
    - aleksiy123 3y ago
      Yeah, and in general the advice is to not do that. However I had more of a slightly diff idea. More like instead of having aweb server + orm. You just write SQL for an endpoint which maps the storage data to public response. Essentially what the ORM was doing. And if the server is just managing connection and calling then endpoint query. Then packing the data to JSON response. It can just be shared/generic.
  - aleksiy123 3y ago
    As a reply to myself I think https://www.tinybird.co/ https://www.tinybird.co/ is pretty close to this. Except I'm not sure if they are read only or not.
- wilsonehusin 3y ago
  OP here, yeah it’s definitely not specific to SQLite. However, I personally don’t find it intuitive to “create database” for short-lived CLI apps because it sounds weird to have such “heavy” dependency — but this experience made me realize SQLite isn’t “heavy” at all and should probably be considered more often!
  - packetlost 3y ago
    I imagine there are a lot of underappreciated "knockon" effects of using SQLite in this way: your data is already modeled as tables and making it persistent is dramatically easier than if you started with the traditional in-memory datastructures.
imiric 3y ago
SQLite is great. I've also recently settled on it as a key-value store, after considering a few purpose-built key-value solutions. Turns out that it's really easy to make SQLite work as a key-value store, but very difficult to make key-value stores relational, if you ever need to. Just be careful with `:memory:` databases. From the mattn/go-sqlite3 FAQ[1]: > Each connection to ":memory:" opens a brand new in-memory sql database, so if the stdlib's sql engine happens to open another connection and you've only specified ":memory:", that connection will see a brand new database. A workaround is to use "file::memory:?cache=shared" (or "file:foobar?mode=memory&cache=shared"). Every connection to this string will point to the same in-memory database. I noticed strange behaviors with just `:memory:` where tables would disappear at random, and this workaround helped. Make sure to use a unique filename as the `file:` value, especially if using this in tests. [1]: https://github.com/mattn/go-sqlite3#faq https://github.com/mattn/go-sqlite3#faq
- wilsonehusin 3y ago
  OP here — that's interesting, thank you for sharing! I use http://modernc.org/sqlite http://modernc.org/sqlite for easier static compilation and haven't observed unexpected behaviors, but will keep that in mind! I agree with the key-value store too — there are some instances where I wish for a columnar database, but settling to use SQLite with key-value store ended up pretty okay.
  - imiric 3y ago
    I use the modernc driver as well, which is where I ran into this. AFAIU this is not a driver issue, but a SQLite one. See https://www.sqlite.org/inmemorydb.html https://www.sqlite.org/inmemorydb.html

74 points by wilsonehusin 3y ago

deleted 3y ago

[deleted]

The post isn't specific to needing SQLite. Rather it's about the convenience of expressing an algorithm as a query and then using SQLite to execute the query. I agree; queries are why table semantics are so useful. Whether you choose an SQL implementation (e.g., DuckDB and SQLite) or a dataframe library (e.g., pandas and polars), expressing searches, joins, and aggregations becomes trivial for tabular data.

aleksiy123 3y ago
I've had this thought that almost 90% of rest APIs are essentially crud that map external API to internal storage layer. With a bit of auth and validation on top. Kinda thinking a lot of rest APIs could just be SQL logic. Postgrest is pretty much like that but I wonder if you could build a more general tool. SQL + protobufs or SQL + OpenAPI schema.
- adhamsalama 3y ago
  Supabase?
  - aleksiy123 3y ago
    Yeah supabase is similar. But I was more thinking of allowing people to choose any db and be able to expose a rest API on top of that. Postgres, bigquery, supabase whatever. So you can quickly deploy a public facing rest API on views from that data.
- paulddraper 3y ago
  Postgraphile
  - aleksiy123 3y ago
    Yeah postgraphile looks cool too. But I was more thinking any database. And you still get to define/control a public REST interface.
- jd3 3y ago
  We are modernizing and migrating a codebase from the 90s that was used for fec filing at the federal + state level and a majority of their business logic was expressed and written as stored procedures in mySQL.
  - aleksiy123 3y ago
    Yeah, and in general the advice is to not do that. However I had more of a slightly diff idea. More like instead of having aweb server + orm. You just write SQL for an endpoint which maps the storage data to public response. Essentially what the ORM was doing. And if the server is just managing connection and calling then endpoint query. Then packing the data to JSON response. It can just be shared/generic.
- aleksiy123 3y ago
  As a reply to myself I think https://www.tinybird.co/ https://www.tinybird.co/ is pretty close to this. Except I'm not sure if they are read only or not.
wilsonehusin 3y ago
OP here, yeah it’s definitely not specific to SQLite. However, I personally don’t find it intuitive to “create database” for short-lived CLI apps because it sounds weird to have such “heavy” dependency — but this experience made me realize SQLite isn’t “heavy” at all and should probably be considered more often!
- packetlost 3y ago
  I imagine there are a lot of underappreciated "knockon" effects of using SQLite in this way: your data is already modeled as tables and making it persistent is dramatically easier than if you started with the traditional in-memory datastructures.

imiric 3y ago

SQLite is great. I've also recently settled on it as a key-value store, after considering a few purpose-built key-value solutions. Turns out that it's really easy to make SQLite work as a key-value store, but very difficult to make key-value stores relational, if you ever need to. Just be careful with `:memory:` databases. From the mattn/go-sqlite3 FAQ[1]: > Each connection to ":memory:" opens a brand new in-memory sql database, so if the stdlib's sql engine happens to open another connection and you've only specified ":memory:", that connection will see a brand new database. A workaround is to use "file::memory:?cache=shared" (or "file:foobar?mode=memory&cache=shared"). Every connection to this string will point to the same in-memory database. I noticed strange behaviors with just `:memory:` where tables would disappear at random, and this workaround helped. Make sure to use a unique filename as the `file:` value, especially if using this in tests. [1]: https://github.com/mattn/go-sqlite3#faq https://github.com/mattn/go-sqlite3#faq

wilsonehusin 3y ago
OP here — that's interesting, thank you for sharing! I use http://modernc.org/sqlite http://modernc.org/sqlite for easier static compilation and haven't observed unexpected behaviors, but will keep that in mind! I agree with the key-value store too — there are some instances where I wish for a columnar database, but settling to use SQLite with key-value store ended up pretty okay.
- imiric 3y ago
  I use the modernc driver as well, which is where I ran into this. AFAIU this is not a driver issue, but a SQLite one. See https://www.sqlite.org/inmemorydb.html https://www.sqlite.org/inmemorydb.html