4 ms·

I wonder why sqlite? Isn't quite limiting for a client-server app?

mwcampbell 8y ago
I suppose nimforum's developer is comfortable limiting it to just one web server, because with an efficient, AOT-compiled language like Nim, a single server can bear a heavy load. Still, performance isn't everything. What about high availability through redundancy? I see that forum.nim-lang.org is behind CloudFlare. Is that the answer? Finally, what about disaster recovery? In the absence of replication, I hope forum.nim-lang.org at least has backups.
- dom96 8y ago
  You're very right to point out high availability and disaster recovery. I must be honest that I haven't considered the impact that the use of SQLite has on redundancy, but in the multiple years that I have been running the forum I never needed it, a single instance of the forum works perfectly fine. I would say that for most communities, setting up the forum on a VPS and letting it run will be perfectly adequate and that creating redundant servers would be overkill. As for backups, of course we do have them. Although I've also recently learned that we could be handling them better. SQLite has a backup API that we should make use of[1]. The main reason I have set up CloudFlare is for DDoS protection. I learned the hard way that Digital Ocean will null route a VPS' network if they detect a DDoS. 1 - https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html
  - mwcampbell 8y ago
    Fair enough. How do you do backups?
    - dom96 8y ago
      Mainly using DigitalOcean's droplet backups.
      - mwcampbell 8y ago
        Have you researched the potential pitfalls of relying only on crash-consistent VM backups? For example, I read that if you're using the ext4 filesystem, the data=journal option is safer than the default (data=ordered, I think).
        dom96 8y ago
        Nope. I should probably read up on it a bit more :)
      - omnimus 8y ago
        This thread reminds me of Basecamps Getting Real book. Just getting it done. I am now intrigued to try Nim if you are core dev.
dom96 8y ago
For our use case, SQLite has been working fine for many years. I like to think that for most communities SQLite will be perfectly adequate. The main reason for choosing SQLite is that it is much simpler to work with, you don't need to spend time setting up a mysql or postgres server.
- beagle3 8y ago
  If it isn't already, it's worth switching the sqlite database to WAL mode (perhaps even by default, or as an admin option) using "pragma journal_mode=WAL"[0]; In standard "journal_mode=DELETE" mode, readers don't block readers, but readers do block writers, and writers do block both readers and writers. In WAL mode, readers do not block writers and writers do not block readers - the only contention is that writers block writers -- so you get much better performance and much less waiting in most use cases. The downsides of WAL mode are: (a) simple file copies are much more fragile - unless you have a "point-in-time" backup, you essentially must use the backup API to copy the database, or alternatively use [1] which does it for you; Best practice is to do that even if you do have a "point-in-time" backup. (b) All processes that use the WAL database simultaneously must be on the same host. This is NOT a requirement for standard mode -- you only need an underlying filesystem without a broken locking implementation[2]..... (I would trust no NFS implementation based on history; No idea if SMB or AFS is trustworthy -- but if your underlying filesystem is good, you actually can do load-balancing / high availability through the file system with a standard-mode sqlite backend) [0] https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html [1] https://stackoverflow.com/questions/25675314/how-to-backup-sqlite-database https://stackoverflow.com/questions/25675314/how-to-backup-s... [2] https://www.sqlite.org/howtocorrupt.html#_file_locking_problems https://www.sqlite.org/howtocorrupt.html#_file_locking_probl...
  - dom96 8y ago
    Sounds good. I added an issue for this: https://github.com/nim-lang/nimforum/issues/138 https://github.com/nim-lang/nimforum/issues/138. Thanks!
gravypod 8y ago
What I'm interested in is why write all of your code for a single database? Are there no query level abstractions for Nim? Even a basic ORM or even a query-builder would allow portability across systems. Writing for a single db is a bad idea.
- dom96 8y ago
  Indeed, Nim badly needs a good ORM library. Maybe you'd be interested in creating one? :)
  - gravypod 8y ago
    ORMs are extremely simple to implement. I've written them a few times in Java for simple things. Primarily you need something equivalent to reflection so you can read from and inject into any properties that an object might contain. Does Nim have this kind of feature set?
    - dom96 8y ago
      I've never written one, I always thought it's one of those things that sound simple but actually require a lot of careful thought and design. Nim doesn't do reflection, but it has some of the best metaprogramming features including static introspection tools which are far superior to reflection IMO.
      - gravypod 8y ago
        It just takes some opinions. If you haven't developed any yet just take someone else's opinions. Eloquent is a good start for where to look. The QueryBuilder frame is a nice platform that makes available many nice features.
- syseng1002 8y ago
  > Writing for a single db is a bad idea. That's quite a bold statement. Abstractions are never free. Abstract too much and you end up with something slow, that uses the common subset of many databases, and requires very extensive testing.

I suppose nimforum's developer is comfortable limiting it to just one web server, because with an efficient, AOT-compiled language like Nim, a single server can bear a heavy load. Still, performance isn't everything. What about high availability through redundancy? I see that forum.nim-lang.org is behind CloudFlare. Is that the answer? Finally, what about disaster recovery? In the absence of replication, I hope forum.nim-lang.org at least has backups.

dom96 8y ago
You're very right to point out high availability and disaster recovery. I must be honest that I haven't considered the impact that the use of SQLite has on redundancy, but in the multiple years that I have been running the forum I never needed it, a single instance of the forum works perfectly fine. I would say that for most communities, setting up the forum on a VPS and letting it run will be perfectly adequate and that creating redundant servers would be overkill. As for backups, of course we do have them. Although I've also recently learned that we could be handling them better. SQLite has a backup API that we should make use of[1]. The main reason I have set up CloudFlare is for DDoS protection. I learned the hard way that Digital Ocean will null route a VPS' network if they detect a DDoS. 1 - https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html
- mwcampbell 8y ago
  Fair enough. How do you do backups?
  - dom96 8y ago
    Mainly using DigitalOcean's droplet backups.
    - mwcampbell 8y ago
      Have you researched the potential pitfalls of relying only on crash-consistent VM backups? For example, I read that if you're using the ext4 filesystem, the data=journal option is safer than the default (data=ordered, I think).
      - dom96 8y ago
        Nope. I should probably read up on it a bit more :)
    - omnimus 8y ago
      This thread reminds me of Basecamps Getting Real book. Just getting it done. I am now intrigued to try Nim if you are core dev.

dom96 8y ago

For our use case, SQLite has been working fine for many years. I like to think that for most communities SQLite will be perfectly adequate. The main reason for choosing SQLite is that it is much simpler to work with, you don't need to spend time setting up a mysql or postgres server.

beagle3 8y ago
If it isn't already, it's worth switching the sqlite database to WAL mode (perhaps even by default, or as an admin option) using "pragma journal_mode=WAL"[0]; In standard "journal_mode=DELETE" mode, readers don't block readers, but readers do block writers, and writers do block both readers and writers. In WAL mode, readers do not block writers and writers do not block readers - the only contention is that writers block writers -- so you get much better performance and much less waiting in most use cases. The downsides of WAL mode are: (a) simple file copies are much more fragile - unless you have a "point-in-time" backup, you essentially must use the backup API to copy the database, or alternatively use [1] which does it for you; Best practice is to do that even if you do have a "point-in-time" backup. (b) All processes that use the WAL database simultaneously must be on the same host. This is NOT a requirement for standard mode -- you only need an underlying filesystem without a broken locking implementation[2]..... (I would trust no NFS implementation based on history; No idea if SMB or AFS is trustworthy -- but if your underlying filesystem is good, you actually can do load-balancing / high availability through the file system with a standard-mode sqlite backend) [0] https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html [1] https://stackoverflow.com/questions/25675314/how-to-backup-sqlite-database https://stackoverflow.com/questions/25675314/how-to-backup-s... [2] https://www.sqlite.org/howtocorrupt.html#_file_locking_problems https://www.sqlite.org/howtocorrupt.html#_file_locking_probl...
- dom96 8y ago
  Sounds good. I added an issue for this: https://github.com/nim-lang/nimforum/issues/138 https://github.com/nim-lang/nimforum/issues/138. Thanks!

gravypod 8y ago

What I'm interested in is why write all of your code for a single database? Are there no query level abstractions for Nim? Even a basic ORM or even a query-builder would allow portability across systems. Writing for a single db is a bad idea.

dom96 8y ago
Indeed, Nim badly needs a good ORM library. Maybe you'd be interested in creating one? :)
- gravypod 8y ago
  ORMs are extremely simple to implement. I've written them a few times in Java for simple things. Primarily you need something equivalent to reflection so you can read from and inject into any properties that an object might contain. Does Nim have this kind of feature set?
  - dom96 8y ago
    I've never written one, I always thought it's one of those things that sound simple but actually require a lot of careful thought and design. Nim doesn't do reflection, but it has some of the best metaprogramming features including static introspection tools which are far superior to reflection IMO.
    - gravypod 8y ago
      It just takes some opinions. If you haven't developed any yet just take someone else's opinions. Eloquent is a good start for where to look. The QueryBuilder frame is a nice platform that makes available many nice features.
syseng1002 8y ago
> Writing for a single db is a bad idea. That's quite a bold statement. Abstractions are never free. Abstract too much and you end up with something slow, that uses the common subset of many databases, and requires very extensive testing.