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.
Say you have an PostgreSQL database with a table like this:
CREATE TABLE users (
id SERIAL,
name TEXT,
department TEXT
);
And you have initilised it like this:
INSERT INTO "users" (name, department) VALUES ('jack', 'design');
INSERT INTO "users" (name, department) VALUES ('jill', 'engineering');
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
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;
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:
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-person: $p
for: $p in $people
Which will look something like this: