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

SQLite is not a toy database

antonz.org

Post reply on HN
Print view View original

251–260 of 364 posts

Re: SQLite is not a toy database

#251

Post by formerly_proven »

nayuki wrote: post #156
formerly_proven wrote: post #83

Earlier quoted context omitted.

I'd expect this to be unnecessary in WAL mode (which you should use, when possible), since WAL allows concurrent readers while permitting up to one writer. In the old undo-log mode (which remains the default for compatibility) writing excluded readers.

I don't think you can do naive file copy backups even in WAL mode. WAL will checkpoint the log into the main database file from time to time. You need to use the SQLite backup API to let you make a backup while blocking any potential checkpointing.

Sorry, I was unclear. I meant the "copy a few pages then sleep for some time to release the database lock" part. With WAL mode you should just be able to copy as fast as you can without disturbing other readers or the writer. SQLite also has a similar SQL built in, VACUUM INTO. That's nice because the backup API just copies all pages (iirc).

Re: SQLite is not a toy database

#252

Post by steve_gh »

abhgh wrote: post #149
tgb wrote: post #7

Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-developed plotting utility, and it seems less convenient to do the actual analysis part through a sqlite connection in python, say.

Not "in lieu", but I have found SQLite to be a good companion to pandas. pandas is good when you want to lend some easily navigable structure to your data without leaving python, but becomes cumbersome (it feels cumbersome esp if you know SQL) when you want to do some serious processing. At such times, I just dump my data into a SQLite DB, and from then on my code has SQL queries to it, which I find are far more read…

I have exactly the opposite workflow. Import data into salute, perform as much heavy lifting as possible in salute, and export into a python data science workflow via led to if necessary.

Re: SQLite is not a toy database

#253

Post by 0xCMP »

crazygringo wrote: post #146
nalgeon wrote: post #51

Earlier quoted context omitted.

I think that the absence of 'high availability' is not an issue for small websites or web apps. Transactions are ACID, concurrent readers are fully supported. Backups and administrative tasks are super-easy.

So if you're saying everything does support a web database... then what's the reason people aren't using it for websites? Why do you say it works for "small" websites but presumably not large ones? If it's not transactions, concurrent reading, backups, or administrative tasks... then what's the issue you run into? Genuinely curious... I'm wondering if everything I've heard about "don't use SQLite for websites" is wro…

The primary reason for me is that Postgres has stronger default constraints. If you care about keeping your data logically consistent then Postgres has more of that out of the box.

SQLite just makes the tradeoff to be simpler since often it doesn't matter. But don't make the mistake that it doesn't matter. Since PG helps avoid data problems and you might need to scale out web servers that is why Django for instance recommends switching to Postgres (or whatever you're actually going to use) ASAP cause there are differences. You may end up relying on PG to reject things SQLite doesn't care about by default. SQLite might let you get away with inserting data which PG refuses to handle.

Not to mention the DB specific features can differ. Like PG's JSON field types or etc.

Re: SQLite is not a toy database

#254

Post by happyweasel »

SigmundA wrote: post #90
hackeraccount wrote: post #35

Is it the unix version of MS Access? It's sort of interesting to compare the two.

No it would be closer to the Jet db engine that Access uses, they are both file based in process relational db engines. Back in the day for a Windows app if you wanted a in process file based db you would use Jet since the engine was included with windows even though Access was a separate product.

Yeah, and I think sqlite is really comparable to this setup. Use Access to design the database (optional), and access (no pun intended) the jet database engine via ODBC,OLE-DB or DAO ..Jay! Code like it's 1999 :-) ... I remember the jet db engine offered at least transaction level READ_COMMITTED (in comparison to mysql's MyISAM) and "it was quite fast" until reaching about 1mio rows or so (of course, db design, index etc determines "fast", that was just a basic rule of thumb I remember)...

Re: SQLite is not a toy database

#255

Post by bionhoward »

busterarm wrote: post #36
hobo_mark wrote: post #15

Earlier quoted context omitted.

I never managed to wrap my head around jq syntax however

It takes a while. jq really needs a repl.

Check out ijq...used it this past week to deal with some ugly nested FHIR data. Not perfect, a bit laggy, sometimes crashes your terminal, but really cool in a lot of cases https://sr.ht/~gpanders/ijq/

Re: SQLite is not a toy database

#256

Post by flyflyFenix »

I was a fan of SQLite until I needed a full outer join of two tables. It was such a pain to create a workaround when I found out it isn't supported, and the result was still sub-optimal and needlessly complex. Is running a full outer joins such a unreasonable assumption within a DBMS?

Re: SQLite is not a toy database

#257

Post by justin_oaks »

flyflyFenix wrote: post #256

I was a fan of SQLite until I needed a full outer join of two tables. It was such a pain to create a workaround when I found out it isn't supported, and the result was still sub-optimal and needlessly complex. Is running a full outer joins such a unreasonable assumption within a DBMS?

I hear you. MySQL still doesn't have full outer joins either.

I would think this would be one of the easier features to add.

Of course, I'm sure the people working on MySQL or SQLite would say "Patches welcome!"

Re: SQLite is not a toy database

#258

Post by lacker »

gwd wrote: post #46
Thaxll wrote: post #20

Earlier quoted context omitted.

SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html

The article addresses this. Basically, you can have any number of concurrent readers , but only a single writer . Writing and reading can happen concurrently just fine. So the question is -- how many users does a website need before having only a single concurrent writer becomes a bottleneck? That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where [ED…

When I was working on database services there were actually many applications with more writes than reads. A common example is applications where you want to keep the user's state saved frequently, but you only need to reload it when the application is restarted. A lot of games work this way. So there can be a write every few seconds or every minute for every active user, but you only need to do a read a few times at the beginning of a new session.

Of course applications like a blog will have far more reads than writes. It just really varies depending on the type of application.

Re: SQLite is not a toy database

#259

Post by dnautics »

emodendroket wrote: post #158
TheRealPomax wrote: post #117

Earlier quoted context omitted.

Not sure I understand: sqlite is file based, so snapshots and concurrent backups are literally just file copies/backups. I'd much rather SQLite not waste its time on implementing features they're not good at, leaving that to the tools we already have available for rolling file backups, instead spending their time and effort on offering the best file-based database system they can. (Heck, even failover is just a file…

Every database is ultimately on the file system but there’s a reason that method of backup is rarely used.

Postgres lets you write directly to block. In practice, nobody does this though.

Re: SQLite is not a toy database

#260

Post by II2II »

zokier wrote: post #207
II2II wrote: post #127

Earlier quoted context omitted.

Another issue with Base is the required JRE. Installing the JRE may be trivial, but it means that an error message is the first experience most people will have with Base. As for Access, it's access is limited due to the much higher price point of Business/Professional versions of Office. In terms of office suites, that version is about two to three times more expensive than an equivalent office suite from the mid-19…

> Another issue with Base is the required JRE I thought that they dropped JRE requirement when the engine was switched to Firebird? Googling around apparently the transition wasn't quite successful :( https://ask.libreoffice.org/en/question/279711/firebird-dead...

The only "embedded database" option (HSQLDB) requires the JRE. I don't know if this holds true for the "connect to an existing database" options, but I would rather throw together a Python script using SQLite when it reaches that level of complication. (It uses my existing knowledge.)
Post reply on HN