# Structured Data Output Library in [[golang]] ## Overview The idea is to build a library that allows CLI applications to: - output structured data - easily query the data to be output - output schema and documentation - output rich structured data (multiple tables at once, for example) - aggregate and filter data ### Taking the concept further Once this "simple" outputting of data has been implemented, take the concept one step further to turn it into a real data handling powerhouse: - import and export structured data, in a safe and understandable way - export "computable" data, for example SQLite databases with views, or [[Excel]] spreadsheets with formulas - expose simple and unified APIs to query said data - make interfacing to said APIs easy by also scaffolding library code - make authentication and deploying these APIs a breeze - a simple, graphical [[Information Dashboards]] (think redash) on top of the previous functionality - snapshot exported data and transparently save it to the cloud - easily handle previously run queries or query libraries (by using cloud history, local filesets, caches) - easily share exported data - for example, it should be possible for a developer or data scientist to design a dashboard and quickly share it to the sales department, in a secure way ### Splitting into multiple libraries To avoid muddying the water with all these extensions, it is probably a good idea to split the library into multiple smaller libraries: - library to output csv, json, sql, schema, markdown - library to query and aggregate on top of that, with DSL - library to export the previous two as APIs and webapps - library to share data as cloud data / service This is directly related to [[Manifesto for CLI utilities]] and [[20220510 - 14 Great Tips To Make Amazing CLI Tools]]. ### Use case: a retail company Let's take as a base example a retail company that has an inventory, products with their metadata (name, description, categories, price, stock), orders, customers and shipments. These are all stored in a SQL database, and are enriched by a whole set of query functions. For example, get all the orders with order line items associated with a certain user. We want to make it easy to export (and later on import) this data from the CLI. Here is a list of things we want to query: - all product prices in category "Trees", as CSV - all the orders in march - the average order total per month for 2021 - all customers that placed an order for tractors in march - the stock history for each product, at a daily interval - all categories and their products Advanced functionality would be: - export an [[Excel]] spreadsheet so that sales can run their own [[Inventory Forecasting]] - import [[Excel]] spreadsheet with batch price updates, and apply them safely - export a dataset with a simple UI for stakeholders to review inventory data, without having to deal with SQL or a query DSL - easily snapshot and save datasets to the cloud for future reference, for example as part of a cronjob ## Tabular data output A lot of data output by CLI apps is tabular. Most apps have a simple, ad-hoc `printf` based solution to output data. Tabular means that we want are dealing with a list of **objects**. Each **object** has **fields**, and each **field** has a **type** that is printable. At the very least, it should be able to be output in the following formats: - tabular, human readable data - [[JSON]] (as list of **objects**) - [[CSV]] (one line per **object**) - [[SQLite]] (one table, with one row per **object**) - [[Go templates]] (each **object** is passed to the template as a hashmap) The user should be able to specify a list of fields to be output. ## Focus on documentation - make it easy to provide rich CLI documentation - description - autocomplete generation - examples (!) - generated web UI (see below) - interactive TUI editor (see below) - write great documentation for the library itself! ## Query DSL Many [[Command Line|CLI]] programs know how to filter and refine the actual query that returns the tabular data. These filters are usually exposed via ad-hoc CLI arguments (for example, --id or --from YYYY-MM-DD, etc...). We can provide a standardized API to provide and parse such filters. Because yet another query DSL is going to be cumbersome, we want to make adoption as easy as possible. Most [[Domain Specific Language]] features should map to simple and well-document command line arguments. More complex [[Domain SDomain Specific Language|DSL]] constructs should be treated as a language in its own right, and provide interactive documentation as it is written (completion, documentation pane, and especially examples). 💡 Not sure how great implementing this in golang itself would be 💡 There might be something to be gained from [[Haskell]] [[Optics (FP)]] - [[openAPI]] - [[GraphQL]] - interactive [[Text Interfaces|TUI]] with autocomplete (think [[jq|ijq]] in usable) Depending on the actual data backend (for example, SQL), the library could also help output the actual SQL being run, or the sourcecode used to query and output said data. If we use lenses, this could potentialy be automated? ### Focus on making the DSL learnable / easily usable - One idea would be to output similar queries / provide examples based on what the user might be doing - See for example: [Continuous Onboarding](https://robhaisfield.com/notes/continuous-onboarding). He also wrote about [[Domain Specific Language]] in [Domain-specific languages as end-user software](https://robhaisfield.com/notes/domain-specific-languages-as-end-user-software). - Provide an interactive REPL for faster [[Feedback Loops]], see again [Rob Hainsfiel - Feedback loops are a more efficient method of communication](https://robhaisfield.com/notes/feedback-loops-are-a-more-efficient-method-of-communication). ## Output [[Data Schemas]] - [[JSON schema]] - [[XML]] schema - [[openAPI]] - [[GraphQL]]  The app already knows the schema, so why not output that too (it kind of already does in sqlite output). ## Present a HTTP API This is an extension of/related to [[Idea - Lightweight HTTP to SQL query server]]. - REST API - but also human webapp (dashboards, query language console, download different filetypes - [[Tailwind CSS]] [[React]] app - Similar to [[Datasette]], but more geared towards real users, less for hackers - Easy to use TUI app that can be used as a web app equivalent over SSH ## Snapshot data into the cloud Because [[ZK - Always Online Allows Us to Transform a Lot of Traditional Tools and Mediums]]: - [[charm.sh]] style file storage - [[SSH Application]] key vault / history saving - generate access keys to share specific files - deploy HTTP API to the cloud with one command - see [[Wolfram Data Drop]] ## "Intelligent" data output Not only does the application know the data schema, but it knows many more query methods and ways to aggregate it. It can provide a list of useful aggregations right there to choose from. - [[SQL]] views - [[Excel]] [[Spreadsheets]] with macros - [[Python Notebooks]] - [[R Markdown]] - full [[Information Dashboards]] that can be published as standalone apps ## 🧠 Brainstorm 🧠 - keep structured data in a local history (can weleverage schemas for example?) store query along with it - structured data printout that also outputs or scaffolds schemas - structured data output where templates can be fetched from the cloud