31:[] 2e:{"title":"Datasette Client for Observable","cells":[{"id":977,"value":"# Datasette Client for Observable\n","pinned":false,"hidden":false,"mode":"md"},{"id":982,"value":"**Update Dec 2nd 2021**: Rejoice! Observable now supports SQL cells out of the box - please use the newer version of [DatasetteClient](https://observablehq.com/@ambassadors/datasette-client) to use Datasette in Observable! The API isn't 100% compatible to this, but upgrades should be straightforward 🤘🏼","pinned":false,"hidden":false,"mode":"md"},{"id":989,"value":"---","pinned":false,"hidden":false,"mode":"md"},{"id":0,"value":"\n[Datasette](https://docs.datasette.io/en/stable/) is an awesome tool that publishes and serves a SQLite database to the internet. Every Datasette instance has a web interface where you can point+click to explore data or write SQL queries, but it also offers a [JSON API](https://docs.datasette.io/en/stable/json_api.html) that lets you query for data, with SQL, from any webpage.\n\nYou can access this API from an Observable notebook (as long as CORS is enabled on the Datasette instance with \\`--cors\\`), but I got tired of constantly writing \\`fetch('url').then(r=>r.json())\\` in my notebooks. So, here's a much simplier utility for querying data from Datasette instances, with some syntax and design choices that makes it ✨delightful✨ to use in Observable!","pinned":false,"hidden":false,"mode":"md"},{"id":60,"value":"md`## Usage\n\n~~~js\nimport {DatasetteClient} from '@asg017/datasette-client-core'\n\ndb = DatasetteClient('https://...') // Link to your Datasette install\n\ndb\\`select \"some SQL\" as test;\\` // -> [{test: \"some SQL\"}]\n~~~`","pinned":false,"hidden":false,"mode":"ojs"},{"id":69,"value":"md`Use \\`DatasetteClient()\\` to create a tagged template for SQL queries on the specified Datasette instance. Data is returned in the \\`_shape=array\\` format (an array with an element for every row returned), which is the most appropriate format for Observable imo.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":208,"value":"md`Let's give it a try, on a Datasette instance with data on every single Representative and Senator U.S. Congress in U.S. history, from the [unitedstates/congress-legislators](https://github.com/unitedstates/congress-legislators) project. I [wrote a small script that](https://gist.github.com/asg017/f23d3b67f4b481cb2fd32e2b323ff591) loads the \\`legislators-current\\` and \\`legislators-historical\\` files into a SQLite database, and I hosted that on a [Datasette instance running here](https://ds.agarcia.dev/congress/congress-members). Here's how we would load it with \\`DatasetteClient\\`:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":9,"value":"db = DatasetteClient(\"https://ds.agarcia.dev/congress/congress-members\")","pinned":true,"hidden":false,"mode":"ojs"},{"id":235,"value":"md`Now \\`db\\` can be used as a tagged template literal (similar to the \\`html\\` or \\`md\\` builtins in Observable) to make queries on the specified database, like so:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":240,"value":"db`\n SELECT \n bioguide, \n first_name, \n last_name \n FROM members \n WHERE first_name LIKE \"ALEX%\" \n LIMIT 25;`","pinned":true,"hidden":false,"mode":"ojs"},{"id":388,"value":"md`You can use \\`.info()\\` to get a JSON representation of the tables, columns, and foreign keys available on the database.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":390,"value":"info = db.info()","pinned":true,"hidden":false,"mode":"ojs"},{"id":395,"value":"md`If you prefix the tagged template with \\`.table\\`, then it'll render the results to an HTML table, which is a little easier to read. It also attaches the raw data to the \\`.value\\` of the tables, meaning it works well with \\`viewof\\` in Observable, if you want to both display it nicely + use the values programattically.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":244,"value":"viewof millennialMembers = db.table`\n SELECT bioguide,\n first_name,\n last_name,\n CAST(strftime('%Y', DATE(birthday)) AS INTEGER) AS birth_year\n FROM members\n WHERE \n birth_year >= 1981 \n AND birth_year <= 1996\n ORDER BY birth_year DESC`","pinned":true,"hidden":false,"mode":"ojs"},{"id":406,"value":"millennialMembers","pinned":true,"hidden":false,"mode":"ojs"},{"id":246,"value":"md`You can pass in parameters inside the tagged template with \\`\\${value}\\` as well. Here we query for either Democratic, Republican, or Independent Senators who are currently serving.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":252,"value":"viewof party = html`\n \n `","pinned":true,"hidden":false,"mode":"ojs"},{"id":476,"value":"beforeDate = '1900-01-01'","pinned":true,"hidden":false,"mode":"ojs"},{"id":460,"value":"// Try removing the \"dry\" to see the results of the query!\ndb.dry`SELECT * FROM members WHERE gender = ${gender} AND birthday < ${beforeDate}`","pinned":true,"hidden":false,"mode":"ojs"},{"id":485,"value":"md`If you pass in array into an \\`\\${}\\` expression, then it will automatically be encoded as a SQL array (with parenthesis).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":501,"value":"viewof selectedMembers = html`