Page Comparison

...

• How to get your database to allow emojis and other unusual characters:
  • ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

SQLite

When to use SQLite

• https://www.sqlite.org/whentouse.html

  • Relevant excerpt:

    Websites
    SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

    The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.

    See also: Hacker New discussion from 2022-12-13.

    Also:

    A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.

• https://news.ycombinator.com/item?id=31152490

  • It is exceptionally great if you don't need parallel writes or have many terabytes of data - ie: for most services out there.
    When embedding natively, like in a Rust app, the performance is better than any other RDBMs because no network/serialization overhead and being able to use pointers in-process if needed.

    The DevOps story also is a dream: typically it is just a single file (optionally + some more for journaling) and setup is automated away (most language libs bundle it already), plus it is widely known since smartphone SDKs and all webbrowsers include/expose it.

    A subtile advantage: the supported SQL subset is so small, that "if it works in sqlite, it will also work with $RDBMS" in most cases, but not the other way around. I always use it when getting started when in need of relational data, and only had to swap it out for postgres once, but not due to technical/scaling reasons (IT policy change & stuff).

    Having said that, it is mind-boggling what kind of load you can handle with a small VPS that runs a Rust microservice that embeds it's own SQLite natively... that would be an expensive cluster of your typical rails/django servers and still have worse performance.

Misc tips

• Installation
  • Go to SQLite3 download page
    1. Go down to the “Precompiled Binaries For Windows” section.
    2. Download “sqlite-shell” and “sqlite-dll” archive files.
       • sqlite-dll-win64-x64-3160200.zip
    3. Download the sqlite tools .zip
       • sqlite-tools-win32-x86-3160200.zip
    4. Unpack everything in C:\sqlite folder.
    5. Add that folder to your PATH.
• Creating the empty .db file:
  1. Hit the Windows key, type 'cmd', then right-click the 'Command Prompt' result and select 'Run as Administrator'.
  2. Navigate to the folder where you want the db to be.
  3. Type 'sqlite3 name_of_the_db.db' and hit 'Enter'.
• Simple example of a schema file:

  • 
    

    Code Block
    CREATE TABLE salespeople ( id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, commission_rate REAL NOT NULL );

    
    

...