Learn full-stack web development using fastn in a week

Fullstack

Querying PostgreSQL

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

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.

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

Lang:

The FASTN_PG_URL must contain a valid connection string.

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

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

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

Environment Variables

`FASTN_PG_URL`

The FASTN_PG_URL must contain a valid connection string.

This processor will not work if this environment variable is not present.

`FASTN_PG_DANGER_DISABLE_SSL`

By default fastn connects to PostgreSQL over a secure connection. You can set FASTN_PG_DANGER_DISABLE_SSL to false if you want to connect to a insecure connection.

This is not recommended in production.

`FASTN_PG_SSL_MODE`

fastn can connect to a PostgreSQL in a few different secure mode. See PostgreSQL official documentation on SSL Mode Descriptions.

FASTN_PG_SSL_MODE=require is default and recommended for production.

FASTN_PG_SSL_MODE=prefer is allowed but not recommended for production as it offers no benefits of encryption (is suseptible to MITM attack).

verify-ca and verify-full are both better than require, but we do not support them yet because the underlying we are using, deadpool, does not support it yet. We have created a tracking issue for this.

`FASTN_PG_DANGER_ALLOW_UNVERIFIED_CERTIFICATE`

fastn can ignore invalid ceritificates when connecting to PostgreSQL if you set FASTN_PG_DANGER_ALLOW_UNVERIFIED_CERTIFICATE to true. This is not recommended for production.