4 ms·

Expensify is powered entirely using sqlite (inside bedrockdb.com) and it is freaking amazing.

sciurus 8y ago
This was discussed a couple years ago at https://news.ycombinator.com/item?id=12739771 https://news.ycombinator.com/item?id=12739771
ris 8y ago
Good thing you don't need 64bit integers. Seriously, as impressive as SQLite is for what it is, people shouldn't be given the impression that it doesn't come with some massive and often surprising caveats when viewed as a general purpose database.
- piyh 8y ago
  Think you have a few misplaced double negatives.
- HumanDrivenDev 8y ago
  SQLite has 64 bit integers. In fact row ids are 64 bit by default since version 3.
- deleted 8y ago
  [deleted]
- dagenix 8y ago
  It has some quirks, but, I'd be interested to know what you thing are its "massive and surprising" issues. Also, it has 64-bit integers - https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
- ris 8y ago
  Looks like I misremembered this (re 64bit integers) and it was one of the other slightly odd limitations SQLite has around rowids and foreign keys that caused me to abandon the last system I was designing with it... As for what most concerned me most when using SQLite was the ease of which it would happily allow me to make broken queries and not raise a fuss, e.g. referencing non-GROUP BYed terms in an aggregation.
  - ak39 8y ago
    Hi ris, Can you provide example(s) of the broken query with non-GROUP BY columns in an aggregation? (Or a reference link if this is well known - I've searched briefly but can't find anything.)
    - lmz 8y ago
      Not the parent poster but it's queries like these: Select user.id, user.name, sum(tx.amount) from user inner join txn on (user.id = txn.uid) group by user.id User.name is not in the group by list so it is picked arbitrarily from the rows in the group. It's harmless here but not in all cases.
      - Groxx 8y ago
        TBH I like the ability to do this. Sometimes you truly don't care which value you get, you just want one. Sometimes you have application-level constraints which mean the one it picks is guaranteed to be correct, but you can avoid the cost of enforcing that (at insertion time and at lookup time). It's a handy tool when it's appropriate. But hell yes, tools should warn you about this loudly, as it's not generally correct.
  - yawaramin 8y ago
    Did you file a bug report?

This was discussed a couple years ago at https://news.ycombinator.com/item?id=12739771 https://news.ycombinator.com/item?id=12739771

ris 8y ago

Good thing you don't need 64bit integers. Seriously, as impressive as SQLite is for what it is, people shouldn't be given the impression that it doesn't come with some massive and often surprising caveats when viewed as a general purpose database.

piyh 8y ago
Think you have a few misplaced double negatives.
HumanDrivenDev 8y ago
SQLite has 64 bit integers. In fact row ids are 64 bit by default since version 3.
deleted 8y ago
[deleted]
dagenix 8y ago
It has some quirks, but, I'd be interested to know what you thing are its "massive and surprising" issues. Also, it has 64-bit integers - https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
ris 8y ago
Looks like I misremembered this (re 64bit integers) and it was one of the other slightly odd limitations SQLite has around rowids and foreign keys that caused me to abandon the last system I was designing with it... As for what most concerned me most when using SQLite was the ease of which it would happily allow me to make broken queries and not raise a fuss, e.g. referencing non-GROUP BYed terms in an aggregation.
- ak39 8y ago
  Hi ris, Can you provide example(s) of the broken query with non-GROUP BY columns in an aggregation? (Or a reference link if this is well known - I've searched briefly but can't find anything.)
  - lmz 8y ago
    Not the parent poster but it's queries like these: Select user.id, user.name, sum(tx.amount) from user inner join txn on (user.id = txn.uid) group by user.id User.name is not in the group by list so it is picked arbitrarily from the rows in the group. It's harmless here but not in all cases.
    - Groxx 8y ago
      TBH I like the ability to do this. Sometimes you truly don't care which value you get, you just want one. Sometimes you have application-level constraints which mean the one it picks is guaranteed to be correct, but you can avoid the cost of enforcing that (at insertion time and at lookup time). It's a handy tool when it's appropriate. But hell yes, tools should warn you about this loudly, as it's not generally correct.
- yawaramin 8y ago
  Did you file a bug report?