DEV Community

Yaroslav Polyakov
Yaroslav Polyakov

Posted on

2

sql-export - create .md / .json files from database content

Hugo (and other SSG, such as Gatsby, Next.js, and others) are great tools to generate static and very fast websites (which are highly rated by google and comfortable for users). But if you have source data in SQL database, you need to generate thousands of markdown files (e.g. one file for each item you are selling).

sql-export export database data into all-in-one JSON file or into many files (one file per SQL result row) in JSON, Markdown with YAML frontmatter or custom format (using text/template, same syntax as you use is HTML layouts in Hugo)

Very simple:

# Export query results to JSON file
./sql-export -q 'SELECT title, price FROM libro LIMIT 2' > /tmp/books.json

# Export to many JSON files
./sql-export -q 'SELECT id, title, price FROM libro' -f json -o '/tmp/libro/{{.id}}.json'
cat /tmp/libro/123.json 
{
    "id": 123,
    "price": 45,
    "title": "ALLGEMEINE GESCHICHTE DER HANDFEUERWAFFEN. Eine Übersicht ihrer Entwickelung. Mit 123 Abbildungen und 4 Ubersichtstafeln. - Günther Reinhold. - Reprint Verlag, - 2001"
}

# Export to markdown files with YAML frontmatter
./sql-export -q 'SELECT id, title, price FROM libro' -f md -o '/tmp/libro/{{.id}}.md'
cat /tmp/libro/123.md 
---
id: 123
price: 45
title: "ALLGEMEINE GESCHICHTE DER HANDFEUERWAFFEN. Eine Übersicht ihrer Entwickelung."
    Mit 123 Abbildungen und 4 Ubersichtstafeln. - Günther Reinhold. - Reprint Verlag,
    - 2001
---

# Export to any custom template
cat out-template.html 
id: {{.id}}
title: {{.title}}
price: {{.price}}

./sql-export -q 'SELECT id, title, price FROM libro' -o '/tmp/libro/{{.id}}.txt' --tpl out-template.html 

cat /tmp/libro/123.txt 
id: 123
title: ALLGEMEINE GESCHICHTE DER HANDFEUERWAFFEN. Eine Übersicht ihrer Entwickelung. Mit 123 Abbildungen und 4 Ubersichtstafeln. - Günther Reinhold. - Reprint Verlag, - 2001
price: 45
Enter fullscreen mode Exit fullscreen mode

Full documentation and git repo: https://github.com/yaroslaff/sql-export

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay