Brian Holt
Neon
Complete Intro to SQLite
5 hours CC
Course Description
SQLite is the world’s most widely-deployed database due to it’s lightning fast performance and zero configuration setup. Learn SQL queries, table management, joins, and performance optimization. Build a Node.js application with SQLite, implement full-text search, work with JSON data, and scale your database using tools like Litestream and LiteFS.
This course and others like it are available as part of our Frontend Masters video subscription.
Preview
CloseCourse Details
Published: October 1, 2024
Learn Straight from the Experts Who Shape the Modern Web
Your Path to Senior Developer and Beyond
- 200+ In-depth courses
- 18 Learning Paths
- Industry Leading Experts
- Live Interactive Workshops
Table of Contents
Introduction
Section Duration: 16 minutes
Brian Holt begins the course by sharing his background and walks through the course website which contains all the notes and code samples.
Brian shares a brief history of SQLite and discusses several use cases. Due to its size, flexibility, and performance, SQLite is suggested to be the most widely distributed software of all time. SQLite is great for everything from production applications to local development environments.
SQL Syntax
Section Duration: 1 hour, 48 minutes
Brian demonstrates how to install SQLite and introduces dot commands, which are built-in commands for working with the SQLite program. Some commands help inspect the database by describing the schema or listing tables. The full list of commands can be viewed with `.help`.
Brian demonstrates how to install SQLite and introduces dot commands which are built-in commands for working with the SQLite program. Some commands help inspect the database by describing the schema or listing tables. The complete list of commands can be viewed with `.help`.
Brian explains the keywords used to compare, limit, and sort results. The LIKE keyword allows for partial string comparison. When a returned dataset is limited, the OFFSET adjusts where the data set starts, and ORDER BY changes the sort order.
Brian demonstrates how to insert and delete data in SQLite. By default, these statements do not return any confirmation message or data. The RETURNING keyword allows you to display the affected rows in the terminal.
Brian reviews tables, which are the core repository of data in an SQLite database. Tables have columns representing the fields and each field can be one of four types: INTEGER, REAL, TEXT, or BLOB. Tables can be edited with the ALTER command or removed with the DROP command.
Brian introduces joins, which allow related data from multiple tables to be brought together into a dataset. Where clauses can be added to joins to limit the data returned from each table. Multiple tables can be joined to create more complex relationships.
Brian demonstrates other join operations, including left, right, and natural joins. A Venn Diagram of the various joins is shared to help illustrate the join operations. Nature joins are not recommended because of their potential for unintended results.
Brian introduces foreign keys and explains how to build relationships between tables. SQLite3 doesn't enforce foreign key rules by default, so using PRAGMA foreign_keys=on; will enable enforcement.
Brian demonstrates aggregation commands like COUNT, GROUP BY, and HAVING. Since a WHERE clause applies to the initial data set, not the aggregated data set, commands like COUNT and GROUP BY must be used in a HAVING statement to filter the results.
Brian answers questions about testing SQLite queries. Most testing would happen at the application level in the form of unit or integration tests.
Brian demonstrates subqueries which are sometimes an alternative to joins. Subqueries are nested in a WHERE clause of an outer query and are executed first.
Building a Project with Node.js & SQLite
Section Duration: 32 minutes
Brian walks through the project setup for a application built with SQLite and Node.js. Fastify is used to create a basic Node.js server. The sqlite3 NPM module provides APIs for working with a local SQLite database.
Students are instructed to write queries with sqlite3 to display invoice details and line items on the page. The solution can be found in the invoice_solution.js file.
Brian walks through the solution to the Querying SQLite exercise.
Brian shares libraries that can be used as an alternative to sqlite3. Some libraries extend the APIs for sqlite3. Others are ORMs which provide an abstracted set of APIs.
SQLite Features, Performance & Search
Section Duration: 39 minutes
Brian highlights SQLite's flexible typing. For example, an INTEGER column will allow values that are also REAL, TEXT, and BLOB. While this can have unintended consequences, it does allow most SQL queries written for other databases (like MySQL, Postgres, etc.) to coerce to a TEXT data type.
Brian discusses limits for row, column, query, table joining, and the overall database size. These limits are beyond typical use cases and are rarely reached.
Brian explains that views are abstract or virtual tables. They simplify common SQL queries that may include complicated joins and are also a more secure way to present a subset of the data to a permission-restricted application or user.
Brian uses EXPLAIN to analyze a query's execution. The EXPLAIN QUERY PLAN prefix provides a summarized version. When "--SCAN" appears in the results, it typically means the query looks at every row and may be inefficient or costly.
Brian uses indexes to optimize query performance. Indexes add a B-Tree (balanced tree), making lookups much faster. Instead of taking O(n) to look up items, it takes O(log n) since it can use a tree to find the item instead of scanning every item in the database.
Brian demonstrates the FTS5 extension that enables full-text search within a table. This eliminates the need to chain multiple LIKE clauses to search for text across multiple columns. The bm25 function, which returns the "best match" is also demonstrated in this lesson.
Working with JSON
Section Duration: 42 minutes
Brian installs sqlpkg which allows developers to install extensions for SQLite. These third-party extensions add additional features not available in SQLite by default.
Brian introduces the JSON extension for SQLite. It makes it possible to read and write arbitrary data, treating SQLite like a document-based database similar to MongoDB. NOTE: The JSON extension is now included in SQL and doesn't need to be installed separately.
Brian demonstrates some common JSON operations in SQLite. JSON allows data to be inserted, removed, and replaced. Specific values can be extracted from JSON with the -> and ->> operators.
Brian switches to JSONB, a more compact, binary representation of JSON. The output is harder to read since the format is more compact, but JSONB adds features like retrieving nested data and additional syntactic sugar, leading to a better developer experience.
Brian demonstrates a few advanced queries, including counting the most favorited band with the json_each function and updating JSON data with json_insert.
Scaling SQLite
Section Duration: 53 minutes
Brian introduces Litestream, a database backup solution for SQLite. Litestream runs as a separate background process and continuously copies write-ahead log pages from disk to one or more replicas. This asynchronous replication provides disaster recovery similar to what is available with database servers like Postgres or MySQL.
Brian discusses database replication and discusses tools that can replicate SQLite databases like Litefs. Litefs simulates a virtual filesystem and then replicates it across machines. It makes SQLite think it's reading and writing to a file, while underneath it is replicating it out to other replicas.
Brian spins up a Docker Compose project where Litefs is used to replicate an SQLite database. Once the project is running, a load balancer manages traffic between primary and replica databases.
Brian shares a couple of projects that forked SQLite and implemented additional features. Since SQLite doesn't allow contributions, forked versions like libSQL and sqld can be alternatives to developers who require a more modern or flexible feature set.
Brian discusses an emerging trend called "Local-First Development". This approach prioritizes local, in-browser storage to increase the performance of client-side applications. The browser-based database is streamed seamlessly to the server behind the scenes to ensure the data is backed up and available on the server.
Wrapping Up
Section Duration: 5 minutes
Brian wraps up the course by answering audience questions and sharing some final thoughts on the process of choosing a database platform for your application.
Learn Straight from the Experts Who Shape the Modern Web
- In-depth Courses
- Industry Leading Experts
- Learning Paths
- Live Interactive Workshops