{
  "$type": "site.standard.document",
  "content": {
    "$type": "site.standard.content.markdown",
    "text": "Wanted to play with the new DuckDB Node Neo library and thought it would be interesting to see if I could make it work in Astro. Astro supports MDX, so I thought it would be a good fit.\n\nThe query will be executed at build time and data rendered into a plain HTML file (no JS). Very useful to reference remote datasets (e.g: doing dashboards) while keeping things minimal. Let's see how it works!\n\nThe first thing I did was to install the DuckDB Node Neo library.\n\n```bash\nnpm install @duckdb/node-api\n```\n\nThen I created a new component called `DuckDBQuery.astro` that would take a query as a prop and execute it using the DuckDB Node Neo library.\n\n```astro\n---\nconst { query } = Astro.props;\nimport { DuckDBInstance } from \"@duckdb/node-api\";\nconst instance = await DuckDBInstance.create();\nconst connection = await instance.connect();\nconst reader = await connection.runAndReadAll(query);\nconst rows = reader.getRows();\nconst columns = reader.columnNames();\n---\n\n<div class=\"table-container\">\n  <table>\n    <thead>\n      <tr>\n        {columns.map((column) => <th>{column}</th>)}\n      </tr>\n    </thead>\n    <tbody>\n      {\n        rows.map((row) => (\n          <tr>\n            {Object.values(row).map((value) => (\n              <td>{value}</td>\n            ))}\n          </tr>\n        ))\n      }\n    </tbody>\n  </table>\n</div>\n\n<style>\n  .table-container {\n    overflow-x: auto;\n    margin: 1rem 0;\n  }\n</style>\n```\n\nThe component creates a DuckDB instance, connects to it, runs the query, and then displays the results in a _poorly formatted_ table.\n\nNow, I can use the component in an MDX file.\n\n```astro\nimport DuckDBQuery from \"../../components/DuckDBQuery.astro\";\n\n<DuckDBQuery query=\"SELECT....;\" />\n\n```\n\nThat's it!\n\nFor example, let's run a query to get the total price for each customer for the `orders.parquet` file that DuckDB provides.\n\n```sql\nselect\no_custkey as customer_id,\nsum(o_totalprice) as total_price\nfrom 'https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet'\ngroup by o_custkey\nlimit 10;\n```\n\nimport DuckDBQuery from \"../../components/DuckDBQuery.astro\";\n\n<DuckDBQuery query={`\n  select\n    o_custkey as customer_id,\n    sum(o_totalprice) as total_price\n  from 'https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet'\n  group by o_custkey\n  limit 10;\n`} />\n\nRudimentary, but it works and is quite minimal.\n\nA similar pattern can be done at the Astro Content Collection level to generate a page for each row of the dataset. E.g: get a list of countries, and build a page for each country with their stats from datasets like the [World Development Indicators](https://huggingface.co/datasets/datonic/world_development_indicators).\n\nThoughs, ideas, feedback? [Reach out](/)!",
    "version": "1.0"
  },
  "description": "Wanted to play with the new DuckDB Node Neo library and thought it would be interesting to see if I could make it work in Astro. Astro supports MDX, so I thought it would be a good fit. The query will be executed at build time and data rendered into a plain HTML file (no JS)....",
  "path": "/duckdb-in-astro",
  "publishedAt": "2025-01-03T00:00:00.000Z",
  "site": "at://did:plc:4z5i7njrld66ew36htufcwry/site.standard.publication/3mo43d2tmt2ov",
  "textContent": "Wanted to play with the new DuckDB Node Neo library and thought it would be interesting to see if I could make it work in Astro. Astro supports MDX, so I thought it would be a good fit.\n\nThe query will be executed at build time and data rendered into a plain HTML file (no JS). Very useful to reference remote datasets (e.g: doing dashboards) while keeping things minimal. Let's see how it works!\n\nThe first thing I did was to install the DuckDB Node Neo library.\n\nThen I created a new component called DuckDBQuery.astro that would take a query as a prop and execute it using the DuckDB Node Neo library.\n\nThe component creates a DuckDB instance, connects to it, runs the query, and then displays the results in a poorly formatted table.\n\nNow, I can use the component in an MDX file.\n\nThat's it!\n\nFor example, let's run a query to get the total price for each customer for the orders.parquet file that DuckDB provides.\n\nimport DuckDBQuery from \"../../components/DuckDBQuery.astro\";\n\nRudimentary, but it works and is quite minimal.\n\nA similar pattern can be done at the Astro Content Collection level to generate a page for each row of the dataset. E.g: get a list of countries, and build a page for each country with their stats from datasets like the World Development Indicators.\n\nThoughs, ideas, feedback? Reach out!",
  "title": "DuckDB in Astro"
}