2a:null 2b:["$","title",null,{"children":"Hello, SQLite | Observable | Observable"}] 30:T847,After attaching a SQLite file, you can insert another SQL cell from the databases pane in the right margin by clicking next to the desired database. Or you can create a new SQL cell by clicking the inserter and then **Database query** (or type “SQL” and then ${buttons("Enter")}).31:[{"id":"b3711cfd9bdf50cbe4e74751164d28e907ce366cd4bf56a39a980a48fdc5f998c42a019716a8033e2b54defdd97e4a55ebe4f6464b4f0678ea0311532605a115","href":"https://static.observableusercontent.com/files/b3711cfd9bdf50cbe4e74751164d28e907ce366cd4bf56a39a980a48fdc5f998c42a019716a8033e2b54defdd97e4a55ebe4f6464b4f0678ea0311532605a115","name":"chinook.db","type":"application/x-sqlite3","lastModified":1621984933321.619,"size":884736},{"id":"fff70c81d8467f4ee7eaba72f13ff8ceab705494e564cd029d065f7a31e2ca36a49beb3ded7cf55d6084afc978511083c1350333e35d9fe157257c1ce47b8df1","href":"https://static.observableusercontent.com/files/fff70c81d8467f4ee7eaba72f13ff8ceab705494e564cd029d065f7a31e2ca36a49beb3ded7cf55d6084afc978511083c1350333e35d9fe157257c1ce47b8df1","name":"Inserting a SQLite file@1.webp","type":"image/webp","lastModified":1667435013788.656,"size":1021724}] 2e:{"id":"5e4043e53ea95976","slug":"sqlite","title":"Hello, SQLite","thumbnail":"cf43b95ff9c409526ab0c0a71ffc9fc61caded63d5e7b0821cf9d73f36c7b591","access_level":"unlisted","create_time":"$D2021-05-25T23:21:52.980Z","update_time":"$D2024-09-13T04:21:58.057Z","view_time":null,"first_public_time":"$D2021-05-26T18:53:11.904Z","has_unpublished_edits":null,"fork":false,"stars":96,"starred":false,"owner":{"type":"team","id":"f35c755083683fe5","login":"observablehq","name":"Observable","avatar_url":"https://avatars-next.observableusercontent.com/424c6c1f7794371c9915a160519bdfb34cf34e50069fcd6def38a1a04d4e9702","tier":"enterprise_2024"},"version":643,"publish_version":null,"role":null,"authored":false,"authors":[],"members":[{"type":"group","id":"c4990be612f2da51","login":"8788045797a2fd14","name":"Product Education","avatar_url":null,"tier":"public","role":"editor"},{"type":"individual","id":"074c414ad1d825f5","login":"mbostock","name":"Mike Bostock","avatar_url":"https://avatars-next.observableusercontent.com/7dbba99ced40bd5ab8013c99b1a11da61231bce321ba1148a9d16ad0f121550a","tier":null,"role":"editor"},{"type":"team","id":"f35c755083683fe5","login":"observablehq","name":"Observable","avatar_url":"https://avatars-next.observableusercontent.com/424c6c1f7794371c9915a160519bdfb34cf34e50069fcd6def38a1a04d4e9702","tier":"enterprise_2024","role":"viewer"}],"comments":[],"custom_thumbnail":true,"trashed":false,"lapsed":null,"subscribed":null,"forks":42,"collection":{"id":"69718aa29ac11e6c","slug":"getting-data-in-and-out","title":"Getting Data In and Out","owner":{"id":"f35c755083683fe5","login":"observablehq","name":"Observable"}},"type":"notebook","body":{"titling":"auto","title":"Hello, SQLite","cells":[{"id":0,"value":"# Hello, SQLite\n\nObservable supports [SQLite](https://sqlite.org/), “a small, fast, self-contained, high-reliability, full-featured, SQL database engine” and “the most used database engine in the world.” Observable’s SQLite client uses [sql.js](https://sql.js.org), an open-source Emscripten port of SQLite.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":396,"value":"To get started, [attach](/@observablehq/file-attachments) a SQLite file to your notebook. Click the inserter in the left margin and then click **File attachment** and select your SQLite file. A new [SQL cell](/@observablehq/sql-cell) will then appear.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":509,"value":"

Inserting a SQL cell by attaching a SQLite file.

","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":157,"value":"$30","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":389,"value":"To demonstrate, here we’ll explore the [Chinook sample database](https://github.com/lerocha/chinook-database) representing a fictitious music library. The SQL query below selects everything in the _tracks_ table.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":341,"value":"SELECT * FROM tracks","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"tracks","database":"chinook.db"},{"id":423,"value":"To reference the results of a query from another cell, give the cell a name. The cell above has the name _tracks_. Its value is an array of objects representing the rows returned by the query.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":425,"value":"tracks // the result of the query above","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":428,"value":"tracks[0] // the first returned row","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":540,"value":"tracks[0].Name // the name of the first track","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":545,"value":"You can interpolate a dynamic variable into a SQL query using dollar-curly brace embedded expressions, `$\\{…}`. For example, say you have a JavaScript cell with the name _composer_:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":273,"value":"composer = \"Billy Cobham\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":561,"value":"You can reference this in a SQL query as `$\\{composer}`, like so:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":352,"value":"SELECT * FROM tracks WHERE Composer = ${composer}","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:db"},{"id":568,"value":"Any JavaScript expression can go within the curly braces. For example, to do a case-insensitive substring match, add percent signs to the interpolated value and use the LIKE operator.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":579,"value":"term = \"billy\" // edit me, then press Shift-Enter","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":581,"value":"SELECT * FROM tracks WHERE Composer LIKE ${`%${term}%`}","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"var:db"},{"id":501,"value":"You can even use a [text input](/@observablehq/input-text) to query interactively. This is nice because you don’t have to press ${buttons(\"Shift-Enter\")} to see the results — the results are live. However, it also means that the notebook won’t remember the last query you entered (though you can specify the initial query using the text input’s *value* option).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":502,"value":"viewof query = Inputs.text({label: \"Search\", placeholder: \"Search tracks\", value: \"billy\"})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":504,"value":"SELECT * FROM tracks WHERE Name LIKE ${`%${query}%`} OR Composer LIKE ${`%${query}%`}","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"results","database":"var:db"},{"id":505,"value":"To visualize your query results, try [Observable Plot](/@observablehq/plot). For example, here’s a histogram of track duration in minutes.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":506,"value":"Plot.plot({\n x: {\n label: \"Duration (min) →\"\n },\n y: {\n grid: true\n },\n marks: [\n Plot.rectY(tracks, Plot.binX({y: \"count\"}, {x: d => d.Milliseconds / (60 * 1000)}))\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":376,"value":"---\n\n## SQLiteDatabaseClient API\n\nFor a lower-level API, you can query SQLite from JavaScript directly rather than using SQL cells. For this, first create a SQLite [database client](/@observablehq/databases) by calling *file*.sqlite().","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":13,"value":"db = FileAttachment(\"chinook.db\").sqlite()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":320,"value":"Alternatively, call SQLiteDatabaseClient.open(*source*), where *source* is a URL, fetch Response, Blob, File, ArrayBuffer, or Array (Uint8Array), or a promise to the same. This is convenient when a SQLite file hosted elsewhere, say on a private server or GitHub.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":317,"value":"altdb = SQLiteDatabaseClient.open(FileAttachment(\"chinook.db\").url())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":96,"value":"To query the database, call *db*.query(*sql*), where *sql* is a SQL string.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":15,"value":"customers = db.query(`SELECT * FROM customers`)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":163,"value":"customers[0] // the first row of the customers query above","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":233,"value":"The *db*.query() function accepts a second *parameters* argument which allows the query to reference dynamic values without allowing arbitrary SQL injection. These parameters can be referenced in the *query* as `$1`, `$2`, *etc.*","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":229,"value":"db.query(`SELECT * FROM tracks WHERE Composer LIKE $1`, [`%${term}%`])","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":610,"value":"The _db_.queryRow() function is similar to _db_.query() except that it returns only the first row, or null if the query returns no results.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":286,"value":"db.queryRow(`SELECT sqlite_version()`)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":607,"value":"You can also use the _db_.sql tagged template literal.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":476,"value":"db.sql`SELECT * FROM tracks WHERE Composer LIKE ${`%${term}%`}`","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":400,"value":"---\n\n## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":404,"value":"import {buttons} from \"@observablehq/keys\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W31","stdlib":"1"}} 2f:"$2e:body"