Skip to the content

/

Drizzle ORM

Drizzle ORM 0.45 on D1

Drizzle ORM: 0% done

Module 1 · What a database is

A database keeps facts so a program can find them again

Loading the slide…

A line of words about as long as a slide’s first line, and its second.

Another line, shorter.

1 / 172 · What a database is

Course progress

0%

A database keeps facts so a program can find them again
, 1 to practiseWhy not just save a file?
Data lives in tables of rows and columns
, 1 to practiseWrite each fact once
, 1 to practiseA key names a row; a foreign key points at one
, 1 to practiseRelations: one-to-many and many-to-many
, 1 to practiseThe library you will build
Programs talk to a database in SQL
Recap: what a database is

SELECT asks for columns FROM a table
Get a library to practise on
, 1 to practiseEvery column with *, and new names with AS
, 1 to practiseWHERE keeps only the rows you want
, 1 to practiseCombine conditions with AND, OR and NOT
, 1 to practiseLIKE, IN and BETWEEN
, 1 to practiseNULL means unknown, and needs IS NULL
, 1 to practiseORDER BY sorts; LIMIT and OFFSET take a slice
Recap: asking questions

CREATE TABLE describes a table before it holds anything
, 1 to practiseConstraints are rules the database enforces
, 1 to practiseINSERT adds rows
, 1 to practiseUPDATE changes rows that match WHERE
, 1 to practiseDELETE removes rows that match WHERE
, 1 to practiseRETURNING gives back the rows you changed
, 1 to practiseALTER TABLE and DROP TABLE change the shape
Recap: making and changing data

A JOIN puts split data back together
, 1 to practiseJOIN keeps only rows that found a match
, 1 to practiseLEFT JOIN keeps every row on the left
Join through a join table
, 1 to practiseAggregates turn many rows into one number
, 1 to practiseGROUP BY makes one result row per group
, 1 to practiseWHERE filters rows; HAVING filters groups
, 1 to practiseA query inside a query
Recap: across tables and in groups

Some databases are servers; SQLite is a file
, 1 to practiseSQLite stores five kinds of value
, 1 to practiseNo booleans, no dates: numbers and text stand in
D1 is SQLite, run for you by Cloudflare
, 1 to practiseprepare, bind, then run
, 1 to practiseValues travel apart from the SQL
D1 facts that shape your code
Recap: SQLite and D1

SQL in strings is easy to get wrong
ORMs and query builders
, 1 to practiseIf you know SQL, you can read Drizzle
Install two packages
Your first table, in TypeScript
Connect: wrap the database in drizzle()
, 1 to practiseA query runs when you await it
, 1 to practiseSee the SQL with toSQL() and the logger
, 1 to practiseReading your first errors
Recap: meet Drizzle

The schema file is the single source of truth
Column builders map to SQLite types
, 1 to practiseA mode changes TypeScript, not the column
, 1 to practisedefault, $defaultFn and $onUpdate
, 1 to practiseenum and $type are promises made in TypeScript
The library’s members and loans
, 1 to practiseTypes come from the table
Name each column, or let casing do it
Recap: describing tables

, 1 to practise.references() makes a foreign key real
, 1 to practiseThe third argument: rules about several columns
, 1 to practisecheck() makes the database refuse bad values
, 1 to practiseAn index is a sorted list that finds rows fast
, 1 to practiseindex() and uniqueIndex()
, 1 to practiseAn index on two columns is sorted by the first, then the second
A partial index covers only some rows
Recap: constraints and indexes

select().from() reads whole rows
Pass an object to pick columns
, 1 to practiseConditions are functions: eq, gt, lt…
, 1 to practiseand(), or() and not() nest like brackets
, 1 to practiseFiltering on NULL
, 1 to practiseorderBy, limit and offset
, 1 to practiseFetching one row
Distinct rows and quick counts
Recap: reading rows

, 1 to practiseinsert().values() adds one row or many
.returning() gives back what was written
, 1 to practiseupdate().set().where()
, 1 to practisedelete().where(), and forgetting where
, 1 to practiseUpserts: insert, or do something else on conflict
, 1 to practiseHow many rows did a write change?
Validate input before it is written
Recap: writing rows

, 1 to practiseA join's rows are nested by table
, 1 to practisePick columns across tables
, 1 to practiseOn a left join, where a condition goes matters
alias() joins a table to itself
, 1 to practiseSubqueries: inArray, exists, and .as()
Join a subquery with .as()
, 1 to practiseTurning joined rows into nested objects
Recap: joins and subqueries

Nested results without folding rows by hand
relations() describes connections, for Drizzle only
, 1 to practiseA many needs a one on the other side
, 1 to practiseGive drizzle() the whole schema
findMany and findFirst
, 1 to practisePredict the shape
It is still one SQL statement
, 1 to practiseTwo relations to the same table need names
Joins, relational queries or raw SQL
Recap: relations and nested queries

, 1 to practisecount, sum, avg, min and max
, 1 to practisegroupBy and having
The sql template writes SQL by hand, safely
, 1 to practisesql.raw() turns off the protection
, 1 to practiseFill gaps with coalesce and CASE
db.$count, also inside a select
CTEs and whole raw queries
Recap: aggregates and the sql template

Some changes must happen together or not at all
, 1 to practiseBEGIN, COMMIT and ROLLBACK
db.transaction() on drivers that support it
, 1 to practisedb.batch() sends statements together, as one transaction
, 1 to practiseA batch can't look at its own results
Put the decision in the statement
, 1 to practiseRead, decide, write: close the gap
Recap: transactions and batches

Every copy of the database must change the same way
, 1 to practisedrizzle.config.ts tells drizzle-kit where things are
, 1 to practiseFrom schema.ts to production
, 1 to practiseSQL for the database, snapshots for drizzle-kit
Applied migrations are history: fix forward
Renames and data changes need you
, 1 to practiseSome changes rebuild the whole table
push, check and studio
Recap: migrations

Test against the database, not a stand-in
, 1 to practiseOn D1: tests in the Workers runtime
Elsewhere: an in-memory SQLite per test file
, 1 to practisePass the database in
, 1 to practiseEach test makes its own data
, 1 to practiseTest the paths that should fail
Test a risky migration on old data
Recap: testing against a real database

What makes a query slow
, 1 to practiseEXPLAIN QUERY PLAN asks SQLite how it will find the rows
Reading a plan
, 1 to practiseEquality columns first, one range or sort last
An index can give the order for free
, 1 to practiseA covering index answers without the table
, 1 to practiseWrapping a column in a function hides it from the index
Indexes cost writes
Recap: query plans and index design

, 1 to practiseN+1: one query for the list, one more per item
Fix it with a join, inArray or a relational query
Independent reads can run together
, 1 to practisePage with a keyset, not a large offset
, 1 to practiseChunk big writes under the bound-value limit
, 1 to practisePrepared statements skip rebuilding the SQL
Build queries in pieces with $dynamic()
Recap: queries at scale

, 1 to practiseOld code meets the new schema
, 1 to practiseExpand, migrate, contract
, 1 to practiseAdding a column safely
Backfills: small steps and idempotent
Ids your code makes
, 1 to practiseIntegrity in layers
, 1 to practiseFind the bad data you already have
Recap: schema evolution and data integrity

The brief: reservations
, 1 to practiseFinish the table
, 1 to practiseWho is next?
, 1 to practiseTwo copies come back at once
, 1 to practiseThe staff page
, 1 to practiseNotifying the queue
, 1 to practiseShipping a change to a live table
, 1 to practiseTuning the hot query
Recap: the whole path