Querying SQLite Using `fastn`

Note: This document is about querying SQLite Database that is part of your fastn package. You can also query PostgreSQL using fastn.

package-query processor allows you to execute SQL queries against SQLite files that are part of your fastn package.

⚠️

Deprecated

This processor has been deprecated in favor of sql processor, starting from version 0.3.81.

⚠️

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

And say you have an SQLite database file with table like this:

creating table

-- run `sqlite3 db.sqlite` in shell to create the database
-- and paste this

CREATE TABLE user (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    department TEXT
);

Lang:

sql

And you have initialised it like this:

inserting data

INSERT INTO user (name, department) VALUES ("amit", "engineering");
INSERT INTO user (name, department) VALUES ("jack", "ops");

Lang:

sql

Assuming the SQLite file is db.sqlite, you can fetch data from the SQLite database using package-query processor:

querying database and storing result in a list

-- import: fastn/processors as pr

-- person list people:
$processor$: pr.package-query
db: db.sqlite

SELECT * FROM user;

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 (view full source)