Fetching Data

Now that you've created and seeded your database, let's discuss the different ways you can fetch data for your application, and build out your dashboard overview page.

Choosing how to fetch data

API layer

APIs are an intermediary layer between your application code and database. There are a few cases where you might use an API:

• If you're using 3rd party services that provide an API.
• If you're fetching data from the client, you want to have an API layer that runs on the server to avoid exposing your database secrets to the client.

In Qwik, you can create API endpoints using Endpoints.

Database queries

When you're developing a full-stack application with Qwik, you'll also need to write logic to interact with your database. For relational databases like Postgres, this can be done with SQL or an ORM likePrisma.

Here are a few cases where you'll need to write database queries:

• When creating your API endpoints, you need to write logic to interact with your database.
• With Qwik, using server functions like `server$()`, you can directly interact with your database without going through an external API layer, avoiding exposing your database secrets to the client.

Let's learn more about server$() functions.

Fetching data with server$() functions

In Qwik, `server$()` functions allow you to create server-side functions that execute solely on the server. This makes them an excellent choice for accessing databases or performing server-only tasks.

• `server$()` acts as an RPC (Remote Procedure Call) mechanism between the client and server, similar to a traditional HTTP endpoint but with strong typing thanks to TypeScript, which eases maintenance. 🛠️
• Additionally, `server$()` can read HTTP cookies, headers, or environment variables, offering versatile server-side functionality. 🌍
• `server$()` can accept any number of arguments and return any value that can be serialized by Qwik, including primitives, objects, arrays, big integers, JSX nodes, and even Promises. 📊

This integrated approach in Qwik ensures efficient and secure data retrieval while optimizing client-server interactions for enhanced application performance. ⚡

Using SQL

For your dashboard project, you'll write database queries using the Vercel Postgres SDK and SQL. There are a few reasons why we'll be using SQL:

• SQL is the industry standard for querying relational databases (e.g. ORMs generate SQL under the hood).
• Having a basic understanding of SQL can help you understand the fundamentals of relational databases, allowing you to apply your knowledge to other tools.
• SQL is versatile, allowing you to fetch and manipulate specific data.
• The Vercel Postgres SDK provides protection against SQL injections.

Don't worry if you haven't used SQL before - we have provided the queries for you.

Fetching specific data from @vercel/postgres database

In `src/lib` folder, create a new file called data.ts and add the following code:

Note: If you used your own database provider in Chapter 6, you'll need to update the database queries to work with your provider.

Here are some key points about this code:

• Creating a connection pool: The `getPool` function creates a connection pool using the connection string from the environment variables.
• server$(): The `server$()` function is used to create server-side functions that interact with the database.
• Opening a connection: Each function opens a connection to the database before executing the query.
• Ending the connection: Each function closes the connection to the database after the query is complete.
• Fetching revenue data: The `fetchRevenue` function fetches revenue data from the database.
• Fetching latest invoices: The `fetchLatestInvoices` function fetches the latest invoices from the database.
• Fetching card data: The `fetchCardData` function fetches data for the cards on the dashboard.
• Handling errors: Each function catches any errors that occur during the database query and logs them to the console.
• Formatting currency: The `formatCurrency` function formats the currency amount for display, import from utils.ts.👇

⚠️ Download utils.ts file and place it in the `src/lib/` folder:

• utils.js 💾

Fetching data for the dashboard overview page

Now that you understand different ways of fetching data, let's fetch data for the dashboard overview page. Navigate to `src/routes/dashboard/index.tsx`, paste the following code, and spend some time exploring it:

In the code above:

• There are also 3 components which receive data: <Card>, <RevenueChart>, and <LatestInvoices>. They are currently commented out to prevent the application from erroring.

⚠️ You must download the 3 components file and place it in the `src/components/ui/dashboard/` folder: 👇

• revenue-chart.tsx 💾
• cards.tsx 💾
• latest-invoices.tsx 💾

You can delete `// @ts-nocheck` line from the top of the file.

Fetching data for <RevenueChart/>

In `src/routes/dashboard/layout.tsx`, import `fetchRevenue()`functions you created in src/lib/data.ts

In Qwik, you can use the routeLoader$ function to load data for a route. This function is called on the server and client when the route is loaded, allowing you to fetch data for the route.

In this code snippet, the `useFetchData()` function uses the `routeLoader$` function to fetch revenue data from the database.

`routeLoader$` load data in the server so it becomes available to use inside Qwik Components. They trigger when SPA/MPA navigation happens so they can be invoked by Qwik Components during rendering.

`routeLoader$` can only be declared inside the src/routes folder, in a layout.tsx or index.tsxfile, and they MUST be exported.

For more information on `routeLoader$`, check out the Qwik documentation

Diplaying data for <RevenueChart/>

To displaying data for the <RevenueChart/> component, import the useFetchRevenue() function from ./layout and call it inside your component:

Then, uncomment the <RevenueChart/> component. Check your localhost, you should be able to see a chart that uses revenue data.

Let's continue importing some more data queries!

Fetching data for <LatestInvoices/>

For the <LatestInvoices /> component, we need to get the latest 5 invoices, sorted by date.

You could fetch all the invoices and sort through them using JavaScript. This isn't a problem as our data is small, but as your application grows, it can significantly increase the amount of data transferred on each request and the JavaScript required to sort through it.

Instead of sorting through the latest invoices in-memory, you can use an SQL query to fetch only the last 5 invoices. For example, this is the SQL query from your data.ts file:

In `src/routes/dashboard/layout.tsx`, import `fetchLatestInvoices()`functions you created in src/lib/data.ts

As before, use the `routeLoader$` function to load data for the route.

Diplaying data for <LatestInvoices/>

To displaying data for the <LatestInvoices/> component, import the useFetchLatestInvoices() function from ./layout and call it inside your component:

⚠️ For display user's images from latest invoices, you must download `customers.zip` folder, unzip and place them in the `public` folder: 👇

• customers.zip 💾

Then, uncomment the <LatestInvoices/> component. Check your localhost, you should be able to see a list of the latest invoices.

Practice: Fetch data for the <Card> components

Now it's your turn to fetch data for the <Card> components. The cards will display the following data:

• Total amount of invoices collected.
• Total amount of invoices pending.
• Total number of invoices.
• Total number of customers.

Again, you might be tempted to fetch all the invoices and customers, and use JavaScript to manipulate the data. For example, you could use Array.length to get the total number of invoices and customers:

But with SQL, you can fetch only the data you need. It's a little longer than using Array.length, but it means less data needs to be transferred during the request. This is the SQL alternative:

The function you will need to import is called fetchCardData. You will need to destructure the values returned from the function.

Hint:

• Check the data.ts file to see what the function returns.
• Import the fetchCardData() function into your layout.tsx file and use it in the routeLoader$()
• Use this routeLoader$() to display the data in your index.tsx file.

Once you're ready, expand the toggle below for the final code:

Great! You've now fetched all the data for the dashboard overview page. Your page should look like this:

What are request waterfalls?

A "waterfall" refers to a sequence of network requests that depend on the completion of previous requests. In the case of data fetching, each request can only begin once the previous request has returned data.

For example, we need to wait for fetchRevenue() to execute before fetchLatestInvoices() can start running, and so on.

This pattern is not necessarily bad. There may be cases where you want waterfalls because you want a condition to be satisfied before you make the next request. For example, you might want to fetch a user's ID and profile information first. Once you have the ID, you might then proceed to fetch their list of friends. In this case, each request is contingent on the data returned from the previous request.

However, this behavior can also be unintentional and impact performance.

Parallel data fetching

A common way to avoid waterfalls is to initiate all data requests at the same time - in parallel.

In JavaScript, you can use the Promise.all() or Promise.allSettled() functions to initiate all promises at the same time. For example, in data.ts, we're using Promise.all() in the fetchCardData() function:

By using this pattern, you can:

• Start executing all data fetches at the same time, which can lead to performance gains.
• Use a native JavaScript pattern that can be applied to any library or framework.

However, there is one disadvantage of relying only on this JavaScript pattern: what happens if one data request is slower than all the others?

Source code

You can find the source code for chapter 7 on GitHub.