There are many ways to backup an SQLite database, watch this video to see all the options, along with the preferred options.
SQLite Clustered Index & the WITHOUT ROWID Optimization
Clustered Indexes (WITHOUT ROWID optimization) are a special optimization you can make that could make your queries twice as fast and it could save on disk space as well.
Check out this video to learn more about this optimization.
Database statistics are a set of data that provides information about the distribution of values within a database. This information is used by the database management system to make informed decisions about query optimization and to improve query performance.
Want to learn more about statistics in SQLite? checkout this video.
Dates in SQLite
SQLite doesn't have date and time datatypes, which begs the question, how do you deal with your date and time values?
Want to learn more? Check out this video.
SQL Common Table Expressions (CTEs)
A Common Table Expression (CTE) in SQL is a named temporary result set, derived from a SELECT statement, that can be used within another SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH clause followed by a SELECT statement and a name for the CTE, and can be referred to by name in the main query. CTEs are useful for breaking down complex queries into simpler, more readable parts, and can also improve query performance by reducing the need to repeat subqueries.
Want to learn more, check out this video.
SQL Window Functions
SQL window functions allow you to perform calculations on a set of rows that are related to the current row, without the need for self-joins or subqueries. They are used to perform a calculation across a set of table rows that are somehow related to the current row, in a way similar to aggregate functions.
Want to learn more? Check out this video
SQLite for beginners: JSON
Did you know that you can store and manipulate JSON in SQLite?
Watch this video to learn more about how and when to use JSON in SQLite.
SQLite for beginners: Extensions
SQLite doesn't have as many features as the other popular databases, but don't let that scare you away from using SQLite, because even though there aren't as many built-in features, there are excellent ways of extending SQLite to add any missing features you might need.
Want to learn more about extending SQLite? Check out this video.
SQLite for beginners: Indexes, beyond the basics
If you want to level up your database skills, the area you should probably focus on first is Indexes.
So, are you familiar with the various types of indexes you can create?
Check out this video to learn more.
SQLite for beginners: Vacuuming
SQLite doesn't automatically free up disk space as it becomes available. In other words, if your database is 1 GB in size and you delete some or all of the data, in the database, the final size of the database will still be 1 GB.
So how do you free up unused space in SQLite?
You need to vacuum your database.
So, how do you vacuum your database?
Watch this video to learn more.