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

A Minimalist Guide to SQLite

tech.marksblogg.com

Post reply on HN
Print view View original

111–120 of 127 posts

Re: A Minimalist Guide to SQLite

#111

Post by hardwaresofton »

xellisx wrote: post #30
hardwaresofton wrote: post #23

Earlier quoted context omitted.

So I'm actually doing this right now, I write about it on my blog. None of the apps I've written have hitten any kind of crazy traffic peak, so I started wondering why the hell I was using postgres or other database types after reading https://www.sqlite.org/whentouse.html . Seeing how simple SQLite has been for me to use has inspired me to write a bunch of dead-simple good-enough approximations for other tools that…

How does SQLite FTS compare to things like Sphinx and Elastic Search?

Probably not well, in the long run? FTS is just a plugin to SQLite where as Sphinx and ES are purpose-built software. It all comes down to what your actual need is though.

I think for most smal projects SQLite + FTS offers a pretty simple easy to setup solution that doesn't require much compute power, either. The simplicity is also a bonus.

Re: A Minimalist Guide to SQLite

#112

Post by hasenj »

jakevn wrote: post #108
hasenj wrote: post #47

Earlier quoted context omitted.

Strictly speaking, SQLite is dynamically typed in a sense, because the database does not enforce the values in the rows match what the columns claim about the type. But from a developer experience point of view, the main difference between dynamic and static typing is the following: Dynamic typing requires a lot of extra checks to ensure everything is what you expect it to be, and a lot of annotations to document wha…

In your experience, what are the strange ways that PostgreSQL breaks?

Initial setup is confusing. Users and rules are confusing. Taking backups and restoring them is confusing.

Re: A Minimalist Guide to SQLite

#113

Post by bschwindHN »

hasenj wrote: post #98
bschwindHN wrote: post #68

Earlier quoted context omitted.

Sure you can do that, but if your app needs to use other services (Redis, for example) you can easily throw that in the compose file as well, and then it's easy to maintain versions and have every developer be on the same page. This is especially helpful if some developers use OS X or Windows. I don't understand the "all of that" phrasing, as if what I just described was a large amount of work.

Why would I want to use redis? My whole point is that I want to simplify my setup, so that I don't need tons of third party servers.

Do you reimplement caching and data storage on every new project? Redis is just one example. Surely in any major project you've made use of a third party server? Maybe not, but tons of projects do and I'm simply describing an easy way to manage those services in a development environment. What's so hard to understand about this?

Re: A Minimalist Guide to SQLite

#114

Post by CJefferson »

labster wrote: post #21
CJefferson wrote: post #2

I feel this article misses out on "why". I tend to store stuff as csv or json, then slurp it into python to operate on it. It's not clear what benefit putting your csv into sqlite gets you, from this article.

I think this comment misses out on "why". I tend to store stuff in xlsx, then use Excel to operate on it. It's not clear what benefit Python or JSON gets you, from this post.

I never claimed to show advantages over excel. Excel is fine, enjoy it!

Re: A Minimalist Guide to SQLite

#115

Post by [deleted] »

gghjhbb wrote: post #106
orthecreedence wrote: post #105

Earlier quoted context omitted.

Exactly. When my 10 visitors a day start writing too many comments, I usually jump to containerizing everything, deploying kubernetes, and installing a queuing system w/ workers to handle the load. A zookeeper cluster with Kakfa could definitely work too, and has the added benefit that you can replay history if your SQLite comment database ever got corrupted. I just don't understand why people can't see the logic beh…

And here I thought you were being sarcastic. Good Lord. On the other hand, you seem to know all the buzzwords, so I bet you’re pretty good at getting your ass hired.

[deleted]

Re: A Minimalist Guide to SQLite

#116

Post by zxcmx »

orthecreedence wrote: post #105
SteveNuts wrote: post #62

Earlier quoted context omitted.

Just add Kafka in between

Exactly. When my 10 visitors a day start writing too many comments, I usually jump to containerizing everything, deploying kubernetes, and installing a queuing system w/ workers to handle the load. A zookeeper cluster with Kakfa could definitely work too, and has the added benefit that you can replay history if your SQLite comment database ever got corrupted. I just don't understand why people can't see the logic beh…

This comment and parent are pretty master level HN trolling.

Re: A Minimalist Guide to SQLite

#117

Post by Can_Not »

hasenj wrote: post #61
dotancohen wrote: post #57

Earlier quoted context omitted.

> queue those up in a list (in the code) That won't work in the language that most server-side website code is written in. PHP processes are created and destroyed per HTTP request, and even the FastCGI implementation won't let one share data across requests. PHP does have a sessions feature, but that data is written to either the filesystem (by default) or a database on every request!

Yes but if you use SQLite because you value minimalism you would also choose a language that compiles the server to a statically linked binary, e.g. Go, in which case this is a trivial "optimization" to do (if you would even call it that).

I've ran sites in the ~500 users per day category where MySQL and postgre use nearly nothing in resources while user interaction causes complex transactions and competing row-level locks. Sounds pretty minimal to me. Odd though that someone who values golang would want to use DB with a poor reputation for concurrency.

Re: A Minimalist Guide to SQLite

#118

Post by vram22 »

I wonder why the Firebird database is not mentioned more often in such threads. It is a somewhat lightweight RDBMS that still has many advanced features that SQLite may not (by design, of course). It may be positioned somewhere between SQLite and heavyweight databases like MySQL, PostgreSQL, Oracle, DB2, etc. I've used Firebird, though only lightly, with Python. It also has different (lighter/heavier) versions for different needs.

https://en.wikipedia.org/wiki/Firebird_(database_server)

https://firebirdsql.org/

A few excerpts from this page:

Get to know Firebird in 2 minutes:

https://www.firebirdnews.org/docs/fb2min.html

[

Firebird is derived from Borland InterBase 6.0 source code. It is open source and has no dual license. Whether you need it for commercial or open source applications, it is totally FREE!

Firebird technology has been in use for 20 years, which makes it a very mature and stable product.

Don’t be fooled by the installer size! Firebird is a fully featured and powerful RDBMS. It can handle databases from just a few KB to many Gigabytes with good performance and almost free of maintenance!

Below is a list of some of the Firebird’s major features:

Full support of Stored Procedures and Triggers

Full ACID compliant transactions

Referential Integrity

Multi Generational Architecture

Very small footprint

Fully featured internal language for Stored Procedures and Triggers (PSQL) Support for External Functions (UDFs)

Little or no need for specialized DBAs

Almost no configuration needed - just install and start using!

Big community and lots of places where you can get free and good support

Optional single file embedded version - great to create CDROM catalogs, single user or evaluation versions of applications

Dozens of third party tools, including GUI administrative tools, replication tools, etc.

Careful writes - fast recovery, no need for transaction logs!

Many ways to access your database: native/API, dbExpress drivers, ODBC, OLEDB, .Net provider, JDBC native type 4 driver, Python module, PHP, Perl, etc.

Native support for all major operating systems, including Windows, Linux, Solaris, MacOS, HP-UX and FreeBSD.

Incremental Backups

64bits builds available

Full cursor implementation in PSQL

Monitoring tables

Connection and Transaction Triggers

Temporary Tables

TraceAPI - know what happens in your server

]

Disclaimer: I'm not associated with them in any way. Just have used the product a bit, and think it is somewhat good, based on that.

Re: A Minimalist Guide to SQLite

#119

Post by vram22 »

vram22 wrote: post #118

I wonder why the Firebird database is not mentioned more often in such threads. It is a somewhat lightweight RDBMS that still has many advanced features that SQLite may not (by design, of course). It may be positioned somewhere between SQLite and heavyweight databases like MySQL, PostgreSQL, Oracle, DB2, etc. I've used Firebird, though only lightly, with Python. It also has different (lighter/heavier) versions for di…

Also, from:

https://www.firebirdsql.org/en/features/

[

Firebird is free for commercial and educational usage: no license fees, installation or activation restrictions. No double licensing - Firebird license is based on Mozilla Public License.

The mix of high performance, small footprint, supreme scalability, silent and simple installation and 100% royalty-free deployment make Firebird a highly attractive choice for all types of software developers and vendors.

It is used by approximately 1 million of software developers worldwide.

]

Re: A Minimalist Guide to SQLite

#120

Post by justinclift »

danso wrote: post #82
Something1234 wrote: post #36

Earlier quoted context omitted.

Have you tried sqlitebrowser? It's fantastic, I use it a lot. http://sqlitebrowser.org

Yep, that's what I use. Because it is open-source and cross-platform -- not to mention, pretty high quality -- it has significantly increased the attractiveness of teaching SQLite. Previously, me and other SQLite teachers I knew would point students to the SQLite Manager plugin for Firefox, which is also free and open source, but a bit clunkier: https://addons.mozilla.org/en-US/firefox/addon/sqlite-manage... The DB B…

> ... also has in my experience, a very helpful and responsive dev team.

Thanks. We definitely try. :D

Post reply on HN