Support us by giving us a

Overview

Design

Fetching Data Using `http`

Querying PostgreSQL Using `fastn`

Note: This document is about querying PostgreSQL Database. You can also [query SQLite using `fastn`](/sqlite/). `pg` processor allows you to execute SQL queries against a PostgreSQL database.

⚠️

Static Vs Dynamic

This feature works better with dynamic hosting. If you are using `fastn` in [static site mode](/static/), then how the page looked when `fastn build` was called will be shown to everyone. But if you are using [dynamic mode](/server/) then this page would be regenerated on every page load.

Learn More

Deploying `fastn` Sites

Say you have an PostgreSQL database with a table like this:

creating table

CREATE TABLE users (
id SERIAL,
name TEXT,
department TEXT
);

Lang:

sql

And you have initilised it like this:

inserting data

INSERT INTO "users" (name, department) VALUES ('jack', 'design');
INSERT INTO "users" (name, department) VALUES ('jill', 'engineering');

Lang:

sql

Telling `fastn` about your database

Before we make any queries we have to inform `fastn` about your PostgreSQL database credentials. We do this by creating a `.env` file:

export FASTN_PG_URL=postgres://username:password@db-host/db-name

Lang:

The `FASTN_PG_URL` must contain a valid [connection string](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING). `.env` file must be present in the same folder as your `FASTN.ftd` file.

Querying Data

If `.env` file is properly setup you can fetch data from the SQLite database using `pg` processor:

querying database and storing result in a list

-- import: fastn/processors as pr

-- person list people:
$processor$: pr.pg
db: db.sqlite

SELECT * FROM users;

Lang:

ftd

For this to work you have to also create a record with same data as the result of your SQL query. In this query you are using `SELECT *`, which will fetch all three columns, `id`, `name` and `department`, so your record will look something like this:

a record corresponding to your query result

-- record person:
integer id:
string name:
string department:

Lang:

ftd

Note that the type columns in query result must match the type of fields in the record. The order of fields of record must also match the order of columns in the query result. Also note that since the result of this query can be multiple rows (or one or none), we have to read the result in a `person list`, so all data can be stored in corresponding list.

Now that you have data in a variable, you can pass it to some component to view it using the [`$loop$`](/list/#using-loop):

show data in page

-- show-person: $p
for: $p in $people

Lang:

ftd

Which will look something like this:

Person

Name

jack

Department

design

Person

Name

jill

Department

engineering

Support `fastn`!

Enjoying `fastn`? Please consider giving us a star ⭐️ on [GitHub](https://github.com/fastn-stack/fastn) to show your support!

[⭐️](https://github.com/fastn-stack/fastn)

Getting Help

Have a question or need help? Visit our [GitHub Q&A discussion](https://github.com/fastn-stack/fastn/discussions/categories/q-a) to get answers and subscribe to it to stay tuned. Join our [Discord](https://discord.gg/a7eBUeutWD) channel and share your thoughts, suggestion, question etc. Connect with our [community](/community/)!

[💻️](/community/)

Found an issue?

If you find some issue, please visit our [GitHub issues](https://github.com/fastn-stack/fastn/issues) to tell us about it.

Quick links:

- [Install `fastn`](install/) - [Create `fastn` package](create-fastn-package/) - [Expander Crash Course](expander/) - [Syntax Highlighting in Sublime Text](/sublime/)

Join us

We welcome you to join our [Discord](https://discord.gg/a7eBUeutWD) community today. We are trying to create the language for human beings and we do not believe it would be possible without your support. We would love to hear from you.