2b:null 2c:["$","title",null,{"children":"Datasette Client for Observable | Alex Garcia | Observable"}] 31:T44f,Love, love, love. I just started playing with this, and installed Datasette in my Docker locally (because I would like to deploy the image to heroku later). The instructions in the Datasette docs worked great: https://docs.datasette.io/en/stable/installation.html#using-docker but in order to use localhost in my notebook, I had to of course insert --cors in the command line and also, because localhost didn't have an SSL cert, I couldn't have the https observable notebook fetch from the http localhost... so I had to use Chrome and allow the notebook sandbox to allow insecure content. I had to first find the domain for my user content by simply typing 'location.origin' in a cell to get the Observable worker's domain for my login, and then setting 'Insecure content: Allow' for that domain in Chrome. That allowed me to then finally do db = DatasetteClient("http://localhost:8001/fixtures") and the rest was great. Not sure if you want to perhaps explain some of these roadblocks to people who want to run their Datasette locally or in a local Docker. Thanks again for a great notebook!!!!32:[] 2f:{"id":"70be6383a46eb092","slug":"datasette-client","title":"Datasette Client for Observable","thumbnail":"88629459a89a6b4b33d07cd345cf93c0c0694ab5f9eba1339b163e05035431df","access_level":"public","create_time":"$D2020-11-18T23:50:50.223Z","update_time":"$D2021-12-02T22:21:52.668Z","view_time":null,"first_public_time":"$D2020-11-24T04:23:40.909Z","fork":false,"stars":24,"starred":false,"owner":{"type":"team","id":"512cbe7209990c30","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":"starter_2024"},"version":996,"publish_version":996,"role":null,"authored":false,"authors":[{"type":"individual","id":"113fc3dc69e1f893","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":null}],"members":[{"type":"individual","id":"113fc3dc69e1f893","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":null,"role":"editor"}],"comments":[{"id":"17090bc3b951c341","reply_to_id":null,"content":"Minor reduction in sql here:\n\nWITH RECURSIVE dates(date) AS (\n SELECT DISTINCT start FROM terms WHERE start >= \"1975-01-01\"\n)\n, members_gen as (\n SELECT \n members.*, \n members.birthday < '1928-01-01' as other,\n members.birthday between '1928-01-01' and '1945-12-31' as silent,\n members.birthday between '1946-01-01' and '1964-12-31' as boomer,\n members.birthday between '1965-01-01' and '1980-12-31' as x,\n members.birthday between '1981-01-01' and '1996-12-31' as millenial\n FROM members\n)\n\nSELECT \n dates.date\n , sum(other) as num_other\n , sum(silent) as num_silent\n , sum(boomer) as num_boomer\n , sum(x) as num_x\n , sum(millenial) as num_millenial\n from terms\n inner join dates\n on terms.start <= dates.date \n and terms.end > dates.date \n left join members_gen on members_gen.bioguide = terms.member\n where terms.type = 'rep'\n group by dates.date","create_time":"$D2020-11-24T21:52:44.943Z","update_time":null,"resolved":true,"user":{"type":"individual","id":"ef26dea4eb9d647b","login":"casajarm","name":"Greg Jarmiolowski","avatar_url":"https://avatars-next.observableusercontent.com/820d8b7aa75432b3a4c3dbfd12b4cff9d64d9f073f7ce0682137ed77f9e8ffba","tier":null}},{"id":"50ea871871742234","reply_to_id":"17090bc3b951c341","content":"Yo thanks for the suggestion, just added this + republished the notebook!","create_time":"$D2020-11-24T22:10:54.614Z","update_time":null,"resolved":true,"user":{"type":"individual","id":"113fc3dc69e1f893","login":"asg017","name":"Alex Garcia","avatar_url":"https://avatars-next.observableusercontent.com/fa4015f63c3a56af76b90247b0b0f8a98019b671f135157d746d3924eda27c07","tier":null}},{"id":"05ec91fd31162889","reply_to_id":null,"content":"$31","create_time":"$D2020-11-25T02:27:29.889Z","update_time":null,"resolved":true,"user":{"type":"individual","id":"c1daf2951ad616fb","login":"cobus","name":"Cobus Theunissen","avatar_url":"https://avatars-next.observableusercontent.com/4a17a065d7a0f5f9a64a852f7b26a4f875c2ff69eb17264c3bd9f81e69285c05","tier":null}}],"custom_thumbnail":true,"trashed":false,"subscribed":null,"forks":6,"collection":{"id":"2f85d9e4fc377f53","slug":"datasette-client","title":"datasette-client","owner":{"id":"512cbe7209990c30","login":"asg017","name":"Alex Garcia"}},"type":"notebook","body":{"title":"Datasette Client for Observable","cells":[{"id":977,"value":"# Datasette Client for Observable\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"id":989,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"id":9,"value":"db = DatasetteClient(\"https://ds.agarcia.dev/congress/congress-members\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"id":390,"value":"info = db.info()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"id":406,"value":"millennialMembers","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"id":252,"value":"viewof party = html`\n \n `","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":476,"value":"beforeDate = '1900-01-01'","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"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","author":"$undefined","summary":"$undefined"},{"id":501,"value":"viewof selectedMembers = html`