There's a self-paced. YouTube-based course by Mycelial on
the ins and outs of SQLite. It's short, succinct and free and a must watch for anybody wanting to get started with  SQLite.

In checking the course out I also got to learn about the very interesting endeavors of Mycelial. Mycelial lets you move your data from various sources to your chosen destinations on the cloud with a particular focus on edge data sources, real-time IOT.

To give an example, the US Navy uses Mycelial in order to synchronize AI/ML data between submersibles by sharing data seamlessly between the fleet of unmanned vessels, manned vessels, and Cloud environments. Leveraging Mycelial’s Kafka connector, Navy vessels using Kafka or Red Panda as the onboard system of record, synchronize with each other in real-time when connected but remain fully functional when disconnected under the sea.

Interoperability is Mycelial bread and butter, having connectors that seamlessly integrates with just about anything:

• Cloud-based systems (Snowflake, Kafka, Redshift,...)
• Open-source platforms (Postgres, Mysql,...)
• Local data sources (SQLite, Excel, local files,...)

Looking into SQLite's use on the edge in particular I learned that drones and the Humvees are equipped with local SQLite databases which Mycelial keep in sync across the network by automatically propagating this new state to every node in the network. That way it ensures that when any device queries its local database, it gets this newly inserted data as well.

At Iprogrammer we looked at an alternative approach of having SQLite on the edge in "Turn Your SQLite Database Into A Server" which goes through the 'sqld' daemon which allows access to SQLite over the network using the PostgreSQL wire protocol, hence allowing developers to use the SQLite database as a server.

Mycelial's credentials have surely predisposed me positively ahead of looking into their SQLite course. And that feeling proved to be right. "SQLite for beginners", consists of 12 videos embossed with their expertise:

• Indexes, beyond the basics
• Vacuuming
• Full Text Search
• Fixing Slow Queries
• Datatypes
• Journal Modes
• SQLite and the N+1 (no) problem
• JSON
• Dates
• Statistics
• Database Backups

From that list you can derive that this is not exactly a course addressed to beginners. It's more likely addressed to already knowledgeable users who want to learn how to use SQLite efficiently. This is something confirmed by
the introduction of the very first video "Indexes, beyond the basics":

If you want to level up your database skills, the area you should probably focus on first is Indexes. In other words, ideally, you should know what the various types of indexes are, how they work when you should use them, and so on.

The session explains why you should use indexes and goes over the the various types of indexes you can create.

Vacuuming
This sections shows you how to go about freeing up disk space, since SQLite doesn't automatically free it up when it becomes available.

Full Text Search
Use Sqlite's full-text search features by leveraging the FTS5 extension.

Fixing Slow Queries
How do you find and fix slow queries in SQLite?
SQLite offers profiling tools to help you identify the cause of slow queries , while there's even a tool that suggests indexes that you should add to improve a query's performance.

We've recently look at it too from a PostgreSQL perpective in "Let PostgreSQL Pick An Index For You" where we go through Supabase's Index Advisor, a Postgres extension for recommending indexes that improve query performance, easing the burden off the developer's shoulders.

Datatypes
This is where sqlite deviates from the rest of rdbms's .
Datatypes in SQLite are handled differently than pretty much every other popular relational database. SQLite uses a flexible, dynamic type system, whereas the other popular relational databases use a strict, static type system.

We've also looked at the issue in "Load SQLite databases into PostgresSQL", in talking about pgsqlite , a pure python module and command line tool that makes it simple to import a SQLite database into Postgres, saving a ton of time and hassle in the process. On paper such a migration might seem like a simple task, after all it's just structured data out and structured data in but wasn't so in reality. There were many obstacles in the way :

• Typing Differences
  First, SQLite and Postgres use different types.
  
  
• Identifier Quoting
  Mismatches in some cases between how PostgresSQL and Sqlite quote their objects.
  
  
• Forgiving SQL Parsing
  SQLite is more lax with syntax errors in some SQL statements, while Postgres is not.

Journal Modes
There are basically two main journal modes that you can use in SQLite: Rollback Journal mode, which is the default and WAL mode, which is short for Write-Ahead Log. Which journal mode should you use?

SQLite and the N+1 (no) problem
Why SQLite isn't really susceptible to the N+1 problem? SQLite is a library-based, embedded, relational database that runs in your application process and it doesn't suffer from any network IO-related overhead because it's not using the network.

JSON
How to store and manipulate JSON in SQLite

Dates
SQLite doesn't have date and time datatypes, which begs the question, how do you deal with your date and time values?

Statistics
It's tempting to think about the query planner as a magical black box, that we can mostly ignore, but in reality it relies on table statistics to be efficient. This section show how to fine tune them.

Finally, Database Backups
How do you go about backing up or copying a SQLite database?
There's actually quite a few options for copying a SQLite database, for example, you could just copy the file with the cp command, or you can use the clone command to export the entire database, or you can use the dump command to export the entire database to a SQL script which you could then use to recreate the database.
Which of these options should you use?

All the concepts are explained in recipe style "how do I" questions, succinctly and under 10 minutes each.
It is a course that goes beyond the usual and contrived learning materials, instead showcasing how powerful Sqlite is, how it works and what you should do to use it effectively. A must watch.

More Information

Sqlite For Beginners playlist

Related Articles

In Praise Of SQLite
Turn Your SQLite Database Into A Server

pgsqlite - Load SQLite databases into PostgresSQL

Let PostgreSQL Pick An Index For You

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info