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.
And say you have an SQLite database file with table like this:
-- 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
);
And you have initilised it like this:
INSERT INTO user (name, department) VALUES ("amit", "engineering");
INSERT INTO user (name, department) VALUES ("jack", "ops");
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;
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 data in page ([view full source](https://github.com/fastn-stack/fastn.com/blob/main/ftd-host/package-query.ftd))
-- show-person: $p
for: $p in $people
Which will look something like this: