Sqlite database column types

Hello fellow org-roamers,

I am trying to build a graph visualizer for myself in Rust. I use sqlx for interaction with the database, which allows you to load rows into a user-defined struct, very convenient. It does require however that all columns have a set datatype, which is not the case with the org-roam database. Can I just naively define types? What should I take into consideration? Thank you and have a great day!

Org-roam relies on another library called emacsql for interfacing with the database (sqlite). Have a look at this FAQ from that project:

Why are all values stored as strings?

EmacSQL is not intended to interact with arbitrary databases, but to be an ACID-compliant database for Emacs extensions. This means that EmacSQL cannot be used with a regular SQL database used by other non-Emacs clients.

All database values must be s-expressions. When EmacSQL stores a value — string, symbol, cons, etc. — it is printed and written to the database in its printed form. Strings are wrapped in quotes and escaped as necessary. That means “bare” symbols in the database generally look like strings. The only exception is nil, which is stored as NULL.

Will EmacSQL ever support arbitrary databases?

The author of EmacSQL thinks that it was probably a design mistake to restrict it to Emacs by storing only printed values, and that it would be a lot more useful if it just handled primitive database types.

However, EmacSQL is in maintenance mode and there are no plans to make any fundamental changes, not least because they would break all existing packages and databases that rely on the current EmacSQL behavior.

Doesn’t that mean that I can just set all columns to TEXT to maintain compatibility with EmacSQL and get the values as String and if I need to cast any of them I can do it in rust?

That’d be my guess. I suppose you can try and find out if this understanding holds.