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

61–70 of 127 posts

Re: A Minimalist Guide to SQLite

#61

Post by hasenj »

dotancohen wrote: post #57
hasenj wrote: post #33

Earlier quoted context omitted.

It's really weird to have to write on every request. If you're logging user visits for example, you can queue those up in a list (in the code) and flush it to the database every few seconds instead of flushing it on every visit.

> 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).

Re: A Minimalist Guide to SQLite

#62

Post by SteveNuts »

dotancohen wrote: post #57
hasenj wrote: post #33

Earlier quoted context omitted.

It's really weird to have to write on every request. If you're logging user visits for example, you can queue those up in a list (in the code) and flush it to the database every few seconds instead of flushing it on every visit.

> 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!

Just add Kafka in between

Re: A Minimalist Guide to SQLite

#63

Post by beagle3 »

mistercow wrote: post #18

> Data locality can be greatly improved by storing a SQLite 3 database in memory instead of on disk My understanding (although I can no longer find the page in the sqlite3 docs) was that because of caching, using :memory: is unlikely to make much difference in practice.

For reading, for a small database, after the cache is warm, that's likely true. In all other cases, there is no avoiding disk access - which, on spinning rust, is slow.

Re: A Minimalist Guide to SQLite

#64

Post by bschwindHN »

hasenj wrote: post #55
echlebek wrote: post #52

Earlier quoted context omitted.

You are meant to use a storage driver such as aufs or overlay with an attached volume to persist information between container lifetimes. There are a variety of opinions on the goodness of using docker for running a database.

Which translates to: I basically don't want to use it. At least not for development. Not as a means to abstract away the setup of something like Postgres; Docker has its own complications when it comes to setting things up, which I must learn _in addition_ to learning everything related to setting up Postgres.

Running Postgres in a container for development environments is one of the best features of Docker in my opinion. It's really not hard to set up at all.

Install docker and docker-compose, write a simple docker-compose.yml file, and then `docker-compose up -d` in the same directory as the docker-compose.yml file. You now have a full Postgres instance running on your machine at localhost:5432. Stop it with `docker compose down`

DB files are stored in the `pg` folder in the same directory as docker-compose.yml. You can specify this on the `volumes` property.

docker-compose.yml:

    version: '2'
    services:
      pg:
        image: postgres:9.4.10
        ports:
          - "5432:5432"
        volumes:
          - $PWD/pg:/var/lib/postgresql/data/pgdata
        environment:
          - PGDATA=/var/lib/postgresql/data/pgdata
          - POSTGRES_DB=YOUR_DB_NAME_HERE
          - POSTGRES_USER=YOUR_DB_USER_NAME_HERE
          - POSTGRES_PASSWORD=YOUR_DB_PASSWORD_HERE

Re: A Minimalist Guide to SQLite

#65

Post by krallin »

I love SQLite (and use it in a large number of places in our application stack!), but I feel that it suffers from a case of bad defaults. To name a few:

- Foreign key checking (and cascading deletion for that matter) are turned off by default. You need to enable them using `PRAGMA foreign_keys = ON;`.

- There are practically no downsides (and a number of upsides) to using the WAL journalling mode (at least for "use a local database and store it on the disk" use cases). The main one being that reads will conflict with writes if you don't enable it! (which is a problem in a multi-threaded environment) Unfortunately, that's another feature you must remember to enable: `PRAGMA journal_mode = WAL;` (for obvious reasons, this one "stays enabled" after you turn it on).

- Full auto-vacuum cannot be enabled after you start writing to the database unless you enabled incremental auto-vacuum. If you're unsure, it's a good idea to enable incremental auto-vacuum to keep that option open. But, here again, that's not the default: you need `PRAGMA auto_vacuum = INCREMENTAL`.

This tends to be explicitly problematic if you have to perform one or more ad-hoc queries using the SQLite command line on an app's database, and forget to apply the relevant PRAGMAs! I wish there was a way to add "default" PRAGMAs on a sqlite database file to avoid this.

(note: some of these defaults are configurable when compiling sqlite from scratch, but if you're dynamically linking with an OS-provided instance of the library, you can't really do that).

Re: A Minimalist Guide to SQLite

#66

Post by nh2 »

bschwindHN wrote: post #64
hasenj wrote: post #55

Earlier quoted context omitted.

Which translates to: I basically don't want to use it. At least not for development. Not as a means to abstract away the setup of something like Postgres; Docker has its own complications when it comes to setting things up, which I must learn _in addition_ to learning everything related to setting up Postgres.

Running Postgres in a container for development environments is one of the best features of Docker in my opinion. It's really not hard to set up at all. Install docker and docker-compose, write a simple docker-compose.yml file, and then `docker-compose up -d` in the same directory as the docker-compose.yml file. You now have a full Postgres instance running on your machine at localhost:5432. Stop it with `docker comp…

Instead of doing all of that, you could also do it without docker. Simply install postgres via your Linux distro and run e.g.:

    /usr/lib/postgresql/9.5/bin/postgres -D .mydatadir

Re: A Minimalist Guide to SQLite

#67

Post by masklinn »

SteveNuts wrote: post #62
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!

Just add Kafka in between

So you avoid just using a "real" db by adding half a dozen different pieces of software to paper over your "not db" replacement?

Re: A Minimalist Guide to SQLite

#68

Post by bschwindHN »

nh2 wrote: post #66
bschwindHN wrote: post #64

Earlier quoted context omitted.

Running Postgres in a container for development environments is one of the best features of Docker in my opinion. It's really not hard to set up at all. Install docker and docker-compose, write a simple docker-compose.yml file, and then `docker-compose up -d` in the same directory as the docker-compose.yml file. You now have a full Postgres instance running on your machine at localhost:5432. Stop it with `docker comp…

Instead of doing all of that, you could also do it without docker. Simply install postgres via your Linux distro and run e.g.: /usr/lib/postgresql/9.5/bin/postgres -D .mydatadir

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.

Re: A Minimalist Guide to SQLite

#69

Post by ilitirit »

Sqlite is great as data store for storing JSON. We receive realtime data from different sources in JSON format. I dump the raw data into MongoDB, but it's still not very easy to query. This is where Sqlite comes in handy. I import the data straight into Sqlite from MongoDB which then enables me to run SQL queries using the JSON1 extension. Currently I'm using it to store data that drive feed simulators. We can extract the data using queries like:

  SELECT json_extract(RawData, '$.sourceId') SourceId
  , json_extract(RawData, '$.messageId') MessageId
  , json_extract(RawData, '$.message') Message
  , json_extract(RawData, '$.message.parties[0].name') Party
  , json_extract(RawData, '$.timestamp') Timestamp
  FROM RawFeed
  JOIN Source
    ON SourceId = Source.Id
  WHERE Source.Name = 'Foo Company'
  ORDER BY Timestamp

Re: A Minimalist Guide to SQLite

#70

Post by golergka »

hasenj wrote: post #19
echlebek wrote: post #16

SQLite is one of the best pieces of software I have used in my career as a developer. It is performant, reliable, simple and consistent. There is a reason sqlite3 is deployed in so many places.

I think it's not used widely enough yet. For example, 99% of websites could benefit from using SQLite instead of MySql or (god forbid) PostgreSQL. I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite.

I'm trying to follow current news and knowledge on SQL databases, but 99% of my career was on the client-side, so I lack practical knowledge.

How easy it is, in practice, to switch from one SQL database to another?

Post reply on HN