Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

beets.io

Post reply on HN
Print view View original

51–60 of 125 posts

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#51

Post by jacquesm »

Boulth wrote: post #42
jimktrains2 wrote: post #34

Earlier quoted context omitted.

Do you have a write-heavy workload? How do you handle contention? Do you have multiple servers? How do you handle real-time filesystem sync? If you only have one server, how do you handle its inevitable failure? Do you loose any data collected between last backup and failure? (I'm not saying SQLite isn't good, because it's f-ing amazing. I'm just not sold on it as a multi-process, multi-user database.) (If you have a…

> Do you have a write-heavy workload? How do you handle contention? I'd also be interested in that. Last time I wanted to use Sqlite opening twice the same file for writing either would not succeed or could time out.

That's because SQLite is meant for single user applications. If you try to open the file the second time it will wait to acquire the lock.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#52

Post by tabulatouch »

batmansmk wrote: post #30

We use SQLite in an app with about 1,000 active users. It took us: - 0h to manage backups ("cp"), - 0h to manage seeds and tests fixtures ("cp"), - 0h to configure and secure (void), - 0h to write the deployment scripts (void), - 0h monitoring/watchdog jobs (void), - 1h to rsync for failover ("rsync") My last projects always spent at least a good 100h to do all of this the right way. Then, if it is not good enough, w…

Nice. Do you have any advice for true SQLite replication? Mobile scenarios for instance.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#53

Post by noir_lord »

benoror wrote: post #45
wenc wrote: post #35

This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.

Don't underestimate the ability to see right away CSV contents using Excel/Numbers/Google Spreadsheets. Non-coders technical users

I'm a coder and I still find the ability to eyeball a file useful, also csv can be munged about with the shell easily (cut, grep, sed etc) which can be surprisingly useful.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#54

Post by tomtimtall »

wenc wrote: post #35

This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.

Your costumer just sent you a slightly malformed cab, there’s a couple of column names that need to change and some of the rows need to be excluded. 10min tops. Vs your consumer just sent you a slightly mallformed sqlite dB... which tables need to be merged with which tables? What values are actually indexes into other tables? What assumptions are there on the id’s of this table compared to this table? 4days and 20 emails later you still haven’t fed the data into your model. csv has problems that are trivial to solve live. SQLite is just way overkill to be handed over from party to party lightly.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#55

Post by mamcx »

kodablah wrote: post #11

Abstract the persistence layer, document the contract, build a conformance suite, and write your one impl that uses sqlite. I know sometimes there seems to be an impedance mismatch between callers and databases, but often it's not that bad. And no, don't use an ORM or existing DB abstraction to do this. Sure it takes work, but pluggable storage is nice on some of these types of things.

This was for long time the "correct" way to think about this.

Let me show you why is insane.

Imagine you work on Java. Do you build a DSL/Transpiler to Java so you MAYBE could change later to C#?

A RDBMS is even more important that the glue code. Why hide it, why think is something "easy" to trow away?

Why think is nut to code most or all the code on it?

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#56

Post by hexmiles »

tabulatouch wrote: post #52
batmansmk wrote: post #30

We use SQLite in an app with about 1,000 active users. It took us: - 0h to manage backups ("cp"), - 0h to manage seeds and tests fixtures ("cp"), - 0h to configure and secure (void), - 0h to write the deployment scripts (void), - 0h monitoring/watchdog jobs (void), - 1h to rsync for failover ("rsync") My last projects always spent at least a good 100h to do all of this the right way. Then, if it is not good enough, w…

Nice. Do you have any advice for true SQLite replication? Mobile scenarios for instance.

i'm not if is what you need, but the sqlite backup api is really useful, and i find it very simple to use

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#57

Post by fnord123 »

simonw wrote: post #32
fnord123 wrote: post #28

Earlier quoted context omitted.

Not only desktop and mobile, but it can be used as the storage format for larger scale distribued systems. For example it's the on disk format for FoundationDB. >Beeing coded in a single C file makes it portable as hell. It's not coded in a single C file...

It kind of is... the SQLite "amalgamation" process takes all of SQLite's source code (across 100+ files) and concatenates it together into a single sqlite3.c file: https://www.sqlite.org/amalgamation.html

That's horrifying and interesting. Thanks for linking it!

I wonder if the alleged 5-10% performance gains are compared with usage of LTO.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#58

Post by jimktrains2 »

jahvo wrote: post #8

Kudos to this guy. As the maintainer of an open source project, the hardest job is to tell people "your idea is stupid and I won't do it"

Especially since this (single-user music management application) seems to fit SQLite's use-case perfectly:

* No external services required * Backups, when the application isn't run, is just a copy * Good SQL support

I'm not even sure it's possible to get to the point where SQLite is going to fall over in this context (music collection metadata managment).

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#59

Post by kodablah »

mamcx wrote: post #55
kodablah wrote: post #11

Abstract the persistence layer, document the contract, build a conformance suite, and write your one impl that uses sqlite. I know sometimes there seems to be an impedance mismatch between callers and databases, but often it's not that bad. And no, don't use an ORM or existing DB abstraction to do this. Sure it takes work, but pluggable storage is nice on some of these types of things.

This was for long time the "correct" way to think about this. Let me show you why is insane. Imagine you work on Java. Do you build a DSL/Transpiler to Java so you MAYBE could change later to C#? A RDBMS is even more important that the glue code. Why hide it, why think is something "easy" to trow away? Why think is nut to code most or all the code on it?

> Imagine you work on Java. Do you build a DSL/Transpiler to Java so you MAYBE could change later to C#?

Why would I do such a thing? I rarely ever promote abstraction for abstraction's sake and will in almost all cases disagree with premature abstraction. There are levels and for simple CRUD things, a thin contract over your DB is a reasonable tradeoff.

> A RDBMS is even more important that the glue code. Why hide it, why think is something "easy" to trow away?

I don't think you even need glue code. You just need a well-specified contract. You don't want to throw away anything of course.

> Why think is nut to code most or all the code on it?

I don't think that. Often it can be prudent to use plsql/triggers for things. Doesn't mean the frontend of your web app needs to build the SQL in the JS though, right? Just a high level "DoThing" contract with a description of what it should do is fine.

Re: We’re happy with SQLite and not urgently interested in a fancier DBMS (2016)

#60

Post by wenc »

Someone1234 wrote: post #47
wenc wrote: post #35

This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications? CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality. SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.

CSV's biggest competitor is XML. But both XML and SQLite have the same issue: They give you just enough rope to hang yourself with. While SQLite is a fantastic micro-database engine and a file format, it isn't a very good universal B2B format because there are too many features you'd have to support to interoperate. I'd argue CSV's biggest strength is that it is easy to parse correctly, because the format is so simpl…

> I'd argue CSV's biggest strength is that it is easy to parse correctly

Not sure I resonate with that. I work with large CSVs of varying provenance every day (I work in big data) and there's always some CSV edge case that stymies my analysis pipeline.

Timestamp parsing is extremely hard if it's not ISO-8601, as well as handling of unicode encoding, missing data, type inference, European usage of , as a decimal point, hidden characters, etc. One of the costs of almost complete freedom in input is the "interesting" possibilities people come up with to stymie your code.

The Pandas read_csv method has tons of switches to deal with all kinds of CSV parsing definitions precisely because there is no standard. Fortunately this covers 80% of the use-cases. https://pandas.pydata.org/pandas-docs/stable/generated/panda...

Excel's CSV parsing isn't the greatest, but does a surprisingly decent job considering how ill-defined CSV is.

XML isn't really on anyone's radar in the big data world. It's an interchange format yes but is hugely inefficient for dataframe-type data.

Post reply on HN