Datasette tutorials: "Exploring a database with Datasette" and "Learn SQL with Datasette" - feedback wanted!

[simonw]

I just published the first official end-user-targeted Datasette tutorial on the project website:

• Exploring a database with Datasette

I'm really keen on getting feedback on this tutorial:

• Is it clear and easy to follow?
• Does it cover the right material?

I'd also love suggestions on other tutorial topics I could cover on the site. I'm already planning one called "Learn SQL with Datasette" - suggestions for others would be most welcome.

[simonw]

The second tutorial is now live as well:

Learn SQL with Datasette

[dbready]

I took a read through the first tutorial and thought it looked pretty good. High-level with some pointed examples to interest the reader. If inviting commentary from the peanut gallery, a few thoughts:

• (100% personal style opinion) avoid contractions. It weakens the writing when attempting to speak from a position of authority. Then again, contractions give it a more personal, “We’re all learning together and having fun here” camaraderie.
• More of the diagrams could benefit from explicit labeling. In the faceting example, you gave arrow call-outs to direct the reader where to click. In the “Tables can relate to each other section” maybe a pointer to the legislator_id column referenced in the text? Or in the opening image, labeled identifiers for each bit of the interface (1 – This is the Database name, 2 – SQL editor, 3 – Table Names, 4 – Table columns, etc)
• Pre-built “solution links” to the exercises is fantastic.
• When defining foreign keys, maybe this could serve as an area where you highlight that Datasette retrieves the name column for easier browsing? In the executive_terms views where it lists “George Washington 1”, “Thomas Jefferson 3”, etc.
• Data visualization is a great feature, which I think would benefit from an additional example, even if it is trivial (eg # of presidents over time). Something that highlights the ability to generate live charts. I do not have a lot of mapping data, but I always need to generate bar/line graphs.

Future topic suggestions:

• How to build a Datasette – Starting from data, how do I create a database I can share with the world? Begin with dumping data into a single table and how/where to upload it to a hosting provider. Expand to demonstrating how to normalize the tables. Incorporate sqlite-utils to bring in data from csv/json sources.
• Port: Select Star SQL is a fantastic intro SQL course I have recommended to others. Could port some amount of the course into Datasette to provide a more “real” interface to the data. At minimum, providing the database as a Datasette would be useful for poking around. Could optionally reproduce the exercises with solution links.
• How to build a site like the Datasette homepage? The generation and templating system you have is pretty nifty, and I have been meaning to explore the option as a self-contained report + data artifact.
• Step-by-Step Analysis Reproduction - using a FiveThirtyEight or other newsworthy report, attempt to reproduce the outcome

[kindly]

I think both tutorials are very valuable. But it is so hard for me to judge with over 15 years of SQL experiance.

Nonetheless the users of one that I manage https://datasette.codeforiati.org/iati (aid transparancy data) are asking for help doing SQL, so pointing to those will be very handy, so thank you!

For me I just "got" datasette and was like something I would have liked to build.

I think there are a few types of users that need particular targetting when approaching SQL tutorials, as it likey where people will be coming from after having a basic grasp of tabular data:

1: Spreadheet users that know what they are doing.

This group of people who know pivot tables, how to do vlookups and are comforatble with formula who need their first steps SQL. There needs to be some approach to use the tools they have learnt in spreadsheets and translate it on to SQL.

2: ORM users

There are progammers that think (or find) ORMs are easier than writing the SQL directly. Anything more complicated than basic CRUD, especillay on the select side, for me, just seem to add unessesery complication (unless maybe you are targetting multiple dbs).
I think there is value in looking at what is appealing about ORM query languages and helping to migrate back to SQL.

3: Pandas/Dataframes/R users

There are certain quality of life things that are good in the dataframe world, like quick summaries of column contents (which is partially solved by facets in datasette). Nonetheless a join between dataframes scares me a lot more than one in SQL and they are limited to in memory operation. So tutorials targetting these users would be good too.

[plpxsk]

Quick reactions from a skim of first tutorial:

• looks good overall
• great to emphasize facets (& filtering) - these are SUPER powerful, and a key use case when you just want to figure out what's in your data
• regarding how tables relate to each other, this could be the place to emphasize that "merging tables" into an "analysis-ready" table is a tricky and time consuming part, and that's where additional time to learn "data/science" comes in (or need for a specialist). For datasette, it's best to have "all" your variables in a "single table". But this is where things may get complicated (and need those "specialists").

Cheers & keep it up 💯