This feature works better with dynamic hosting. If you are using fastn in static site mode, then how the page looked when fastn build was called will be shown to everyone. But if you are using dynamic mode 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
);

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');

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

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

Lang:

sh

The FASTN_PG_URL must contain a valid connection string.

.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;

-- 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:

-- 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$ :

show data in page

-- show-person: $p
$loop$: $people as $p

-- show-person: $p
$loop$: $people as $p

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 to show your support!

⭐️

Getting Help

Have a question or need help?

Visit our GitHub Q&A discussion to get answers and subscribe to it to stay tuned.

Join our Discord channel and share your thoughts, suggestion, question etc.

Connect with our community!

💻️

Found an issue?

If you find some issue, please visit our GitHub issues to tell us about it.

Quick links:

Join us

We welcome you to join our Discord 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.

Querying PostgreSQL Using `fastn`

Note: This document is about querying PostgreSQL Database. You can also query SQLite using fastn.

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, then how the page looked when fastn build was called will be shown to everyone. But if you are using dynamic mode 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
);

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');

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

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

Lang:

sh

The FASTN_PG_URL must contain a valid connection string.

.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;

-- 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:

-- 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$ :

show data in page

-- show-person: $p
$loop$: $people as $p

-- show-person: $p
$loop$: $people as $p

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 to show your support!

⭐️

Getting Help

Have a question or need help?

Visit our GitHub Q&A discussion to get answers and subscribe to it to stay tuned.

Join our Discord channel and share your thoughts, suggestion, question etc.

Connect with our community!

💻️

Found an issue?

If you find some issue, please visit our GitHub issues to tell us about it.

Quick links:

Join us

We welcome you to join our Discord 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.

Querying PostgreSQL Using fastn

Telling fastn about your database

Querying Data

Person

Person

Support fastn!

Getting Help

Found an issue?

Quick links:

Join us

Querying PostgreSQL Using fastn

Telling fastn about your database

Querying Data

Person

Person

Support fastn!

Getting Help

Found an issue?

Quick links:

Join us

Querying PostgreSQL Using `fastn`

Telling `fastn` about your database

Support `fastn`!

Querying PostgreSQL Using `fastn`

Telling `fastn` about your database

Support `fastn`!