Ask HN: How to build static website that displays database data | Hacker News Reader

Ask HN: How to build static website that displays database data

14 pointslowpro24 comments

I've been trying to figure out how to build a "static" website that displays data pulled from a database. I know SQL fairly well, but have no experience in web dev. I would think website builders would have basic functionality for this, but maybe it's a much harder problem than I'm thinking.

I would also think this could be done on the server side, so the user should never interact directly with the database. Is web development usually difficult to find answers for? I work in networking so rarely interactive with web dev.

Thank you.

View on HN

See https://gohugo.io/templates/data-templates/. If data is SQL, a simple API server to serve up the data at generation-time is the preferred approach.

Pelican and Hugo are the two generators I'm comfortable. Both have a plugin ecosystem - my guess is that something close already exists.

Otherwise, Pelican source code is simple to understand and extend. If I were to implement this and if the data was static or changed only occasionally, I'd read my custom data source, add that data to the Jinja template variables and access them from Jinja HTML templates. For fast changing data, I agree with the export to JSON and render from Javascript approach already suggested.

Do you know the meaning of 'static website'? In some ways your question is an oxymoron. You're misunderstanding the term and have asked a bit of a silly question by accident.

If you explained why you think it needs to be static, we might be able to explain what you've misunderstood.

Ultimately some part of your system will need to be dynamic to get the data and transmit it across the internet. A static website can technically use JavaScript to call a dynamic one using an API, and then display data, but part of the overall setup is still dynamic. A normal website is still involved, that has to talk to the db. The setup is just more complicated.

As for the actual question, pick any framework (Rails, Django, etc). Follow the tutorial for beginners, they cover how to access the db within an hour or so.

> Do you know the meaning of 'static website'? In some ways your question is an oxymoron. You're misunderstanding the term and have asked a bit of a silly question by accident.

By that definition, any static website that uses Google Analytics is no longer a static website, as it's interacting with a database.

As you point out, a static website (one that is deployed as plain HTML and some css / js / image assets) can be part of a larger ecosystem.

So maybe tone down the noob bashing...

You can still go hybrid. Majority of site is static but for some pages you have services that touch db.

How is that 'hybrid'? We've been doing exactly that for literally 20 years. How do you think all the pages like "privacy" or "terms and conditions" or the countless other non-dynamic pages are done?

It's just a website. Doesn't need anything fancy to do it.

Honestly, makes me despair at how over-complicated people make really basic stuff.

Its hybrid because you use 2 separates tools/languages/systems to create parts. You use something like jekyl for static parts and something like node to create services that require dynamic stuff.

Yes, we did it for decades but within single framework such as ruby on rails. This is somewhat different although end result looks the same.

So far other answers are limited in what they consider a 'database'. If you need full blown relational db support, you could for example utilize PostgreRest to get db API with minimal effort that you can use as a service. This is not generation time (it could be), the point is to work run time. This is similar to how you would handle comments via for example discuss service. You basically have full SQL capabilities via REST interface - you can choose, order, sort and filter any db column, 100% of CRUD and even have actions via stored procedures. After taking a time to learn it, you can create a production grade service in few hours.

Data onboarding is as trivial as creating a table in a database - it will get automagically exposed as REST endpoint.

Performance is epic (my tests show ~2K req/s).

There are alternative services, such as hasura, you might wanna take a look at.

http://postgrest.org/en/v7.0.0/

Here is my Windows setup/test and in the notes section you have another one for Nix.

https://github.com/majkinetor/postgrest-test

Here’s an idea: Generate the data and publish it as JSON file. Then you can use some JavaScript to load and display the data. I think Datatables can do this.

https://datatables.net/

I think the correct answer to this is:

- Create an API that exposes the data from the data you need

- Consume that API from your static site.

As others have mentioned, there are tools out there that help you in implementing the API fairly simply. The reason you shouldn't just connect to the database from the browser is primarily security - you should build your API to be a gatekeeper, and expose only the data you need to expose.

How you consume that is up to you. You may find you have some issues with things like CORS, depending on how you set this up - but these issues can usually be worked around.

[edit: Note that depending on why you want a static site, e.g. if bandwidth costs are your big limit and you don't want your API hit with every request, you might find that setting up an automated worker to scrape your data and deploy it makes life easier - assuming that all your visitors to a page will want access to exactly the same data. In fact, if you save it as JSON with a filename ending .js, prepend "window.page_content = " to your file as part of your worker, then you can load your database content at the top of your page in a simple script tag and use that data in the rest of your page.]

Static site generators can generally load serialized data in formats such as CSV, JSON, or YAML when the site is generated. If you can export data from your database in one of those formats, then you're good to go. The documentation for the generator you're using explains how to make use of this data in your markdown or HTML source files.

Before I say this, I have to say: I am not making fun of you or insulting you at all.

However, your question is like asking "How do I make a horse that is actually a car?"

The two things are just completely different. Static websites, by definition, don't have databases and constantly changing information.

So, it sounds like you want to learn some web development. I recommend trying The Rails Tutorial.[1] It teaches your everything you need to know to start making web applications and doesn't assume you have lots of previous experience.

Good luck! And when you make something cool come back and post a "Show HN".

[1]https://www.railstutorial.org/book

I found this library a couple years ago when making simple HTML reports from Powershell scripts, to be viewed locally from a network share:

    AlaSQL JavaScript SQL Database Library
    http://alasql.org/

Works really nicely to show your data in a table and allow sorting by columns and running predefined or ad hoc queries. It's a JavaScript SQL database, so you need to get your data into it primarily as JSON, but it supports importing from text, CSV, and some other common formats, and exporting too.

Usually this is done server-side in something like PHP. It will not be a "static" website, though. The script will generate an HTML page on each access and return it to the user.

Alternatively, if DB data rarely changes, you can generate and save static HTML's on the server and then serve them. But usually if people are accessing database, they work with dynamic data.

Both sqlite and postgresql have options where you can change query result format into html. I think many others have too.

They have an option to change query result into JSON, not HTML AFAIK (although nothing stops you to do that with strings). You still need to provide UI for json via some javascript frameworks such as Vue.js.

There is an option to change query result to HTML in both. I checked.

You are wrong. Give us reference for native html function in postgres and not string massage.

https://www.sqlite.org/cli.html#dotmode

I have just checked it via cli:

    sqlite3 -header -html foo.db

That is feature of CLI tool, not sqlite.

From command line use

     psql -H

Alternatively

     \pset format html

Again, feature of CLI tool not postgresql.

You can dump your tables to a .html file on a schedule and then serve it via a CloudFlare cache with an expiry so that your reports are generated once every 5 minutes and the first request is served by your server then it's served via the cache until you refresh it.

One strange direction to explore could be to set up a webserver to serve a directory full of files, rig your db to look like a filesystem, then compose the two.

This is a probably a daft idea but searching "fuse postgresql" produces results, so it should be an easy daft idea!

Is the data "static?" Perhaps something like https://www.sheet2site.com is worth looking at.

Reply on news.ycombinator.com