Displaying data dynamically from SQL database with Golang, JSON, and JavaScript

Question

I have a Golang server that fetches all the rows from a database table.

SELECT * FROM people;

The fetched data is 'marshaled' into JSON:

json.NewEncoder(w).Encode(people)

JavaScript can fetch the rows through its Fetch API.

Now let's say the database table has 10,000 rows but I only want to display as many rows as they fit on the screen.

As I scroll the page I'd like more rows to be fetched dynamically from the database.

Does the client need to send data to the server telling the server to fetch the JSON again with more data?

I would be grateful for any suggestion. Thank you!

To get those extra rows to load dynamically, you'll need to handle a client-side event and perform an ajax call in the handler. The server-side (the part actually written in Go) can't know when the user scrolled all the way down — Elias Van Ootegem
– Elias Van Ootegem, Commented Dec 28, 2019 at 12:46
No worries, had a quick look at how this stuff might work, and found the scroll event + a way to verify whether the client scrolled all the way down to the bottom of the page or not. Just add the AJAX call there, and it should be fairly simple to get this to work, if you're willing to do it yourself, otherwise like I said: there's a couple of plugins out there already — Elias Van Ootegem
– Elias Van Ootegem, Commented Dec 28, 2019 at 12:58

Elias Van Ootegem · Accepted Answer · 2019-12-28 12:56:25Z

Assuming what you're looking for is pagination, then the answer is quite simple, but it requires changes both on the client and the server side:

Getting the data: You'll want the client-side to tell the server how big the batches of data should be (typically 10, 20, 30, 40, or 50 results per call, with a default value of 10).
The second parameter you'll want from the client is to indicate how many results the client has already loaded on their end.

With these two values, you'll be able to enrich the query to include a limit and offset value. The default offset being 0, and default limit being 10.

That means the first query will look something like:

SELECT * FROM people LIMIT 10 OFFSET 0;

This is often shortened to LIMIT 10; or LIMIT 0, 10;.

Now if the client scrolls down, to load the next 10 records, you'll want them to perform an AJAX call providing you the batch size, and the offset value (how many records are already displayed), then just plug in these values, and you'll get:

SELECT * FROM people LIMIT 10 OFFSET 10;

This query tells the DB to skip the first 10 results, and return the next 10.

If you're using actual pages, another common way to handle this is to have the client provide the page size value, and the page number. For example: I want to see 20 people per page, and I want to jump directly from page 1 to page 5, the parameters passed to the server would be something like: page_size=20&page=5

That means that I need a query that skips the first 80 records (20 times 4 pages), a trivial computation:

offset := pageNr * pageSize - pageSize // or (pageNr -1 ) * pageSize

For the query to be:

SELECT * FROM people LIMIT 20 OFFSET 80;

Some general tips:

Avoid SELECT * as much as possible, always be explicit about the fields you select. Adding fields to a table is common, and using select * can result in exposing data you don't want people to see, or your code messing up because it can't handle new fields
Specify the order for your data. Have a created_at field in your table, and sort by that value, or sort by name or whatever. If you don't sort the results in a particular way, how can you guarantee that the first 10 results will always be the same? If you can't guarantee that, why wouldn't it be possible that you're going to skip some records, and display others twice?
Create a People type server-side that represents the records in the DB if you haven't already

It's been quite a number of years since I've done any front-end work, but to know when you need to load more records, you'll want to write some JS that handles the scroll event. There's a number of plugins out there already, and my JS is most likely outdated, but here's an attempt (untested, and to be treated as pseudo-code):

document.querySelector('#person_container').addEventListener('scroll', (function(ls) {
    var last = ls[ls.length-1] // last person already loaded
    let batchSize = ls.length // IIRC, let is used instead of var now
    // returning the actual handler
    return function(e) {
        // scroll reached bottom of the page
        if ((window.innerHeight + window.scrollY) >= document.body.offsetHeight) {
            // make AJAX call with batchSize + offset, append new elements after last element
        }
    };
}(document.querySelectorAll('.person_item')))

Collectives™ on Stack Overflow

Displaying data dynamically from SQL database with Golang, JSON, and JavaScript

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related