Documentation request: use other DBs than SQLite

[taophp]

Currently, only the use of SQLite3 is documented in xtemplate (did I miss something?). But what about others DBs ? How should I do if I want to use PostgreSQL for example ?

[infogulch]

xtemplate exposes a pretty thin wrapper around Go's database/sql package. This means that it should work for any driver registered with the sql package. Drivers are registered automatically by just importing them, then you should be able to refer to it by its registered name.

For example the github.com/lib/pq package, which appears to be the most popular PostgreSQL library for Go by a wide margin, registers as the driver name "postgres", which you can see in the pq docs:

connStr := "user=pqgotest dbname=pqgotest sslmode=verify-full"
db, err := sql.Open("postgres", connStr)
// url connection strings are also supported

The parameters passed to sql.Open correspond to the --db-driver, --db-connstr CLI flags and Config.Database.Driver, Config.Database.Connstr config object, respectively, which is used to open the database/sql connection here.

---

TL;DR:

1. Import your desired driver at compilation time:

import (
    _ "github.com/lib/pq"
)

2. Configure xtemplate to use the driver name and connection string for the driver, formatted according to the driver's requirements.

---

Now, this leaves some open questions:

• Should the default builds of xtemplate and xtemplate-caddy include more drivers?
  • If so, maybe the list should include: sqlite3 (requires CGO), sqlite, pq, mysql.
• How should you add drivers in a xcaddy-based build? It may be as simple as adding the driver package to the list, I haven't tested.

I'll add a summary to the docs. Thanks. :)

[taophp]

My opinion regarding open questions:

• if it is decided to include more drivers, we do not need to include both sqliteand sqlite3 as they correspond to the same use case; choose one;
• the chosen one should be the default driver provided: it's enough to test xtemplate and to code an app in dev environment;
• it seems that providing support for another db at build should no be a problem in this case: their support is required for production and for advanced users who should be able to handle personalized builds if documented;
• as I see it, custom docker-xcaddy based builds are the best way to get Caddy working; the download feature of the Caddy website is impressive but did not convince me to use it in my workflow (manual downloads vs procedural, versioned, reproducible way provided by Dockerfile and docker-compose.yml), so if other drivers than sqliteor sqlite3 could not be distributed by Caddy website download, it's not a big deal to my eyes.

Hope this helps !

[infogulch]

Yes that's all quite reasonable.

My equivocation between sqlite3 (github.com/mattn/go-sqlite3) and sqlite (modernc.org/sqlite) drivers is caused by some tradeoffs between them. (Honestly there are other options as well.)

Mainly, sqlite3 is the most popular and probably the most correct implementation, but it requires CGO to be enabled at compile time, which precludes using the Caddy download website. While I agree that in general it's not too much to expect advanced users to customize their own builds, it would be unfortunate if people trying xtemplate through the Caddy download site would be unable to use the db feature at all.

The sqlite driver uses a version of sqlite that has been translated from C to Go using automated tools, thus it is pure Go and doesn't require CGO at compile time and should work fine via the Caddy download site. It's also a bit faster in most workloads if I understand the benchmarks correctly. However the great lengths that sqlite devs go to ensure correctness are somewhat attenuated when their source code is translated using tools into a different language, though I suspect many people use it without issue.

There's probably a good solution here somewhere. I'll keep thinking about it.

[infogulch]

Ok I think I've figured out what I want to do:

I will use github.com/mattn/go-sqlite3 for cli builds, and add github.com/ncruces/go-sqlite3 module to the Caddy build server. Download server users can add the ncruces/go-sqlite3 module to get a version of sqlite3 driver built entirely in Go in caddy. I will recommend custom build users (via xtemplate cmd or xcaddy) to use mattn/go-sqlite3.

I still need to validate that this will work, but I think this is a good plan for now.

[gedw99]

https://github.com/ncruces/go-sqlite3/blob/main/go.mod Is excellent choice

the wazero import allows the use of a WAL allowing it to be used with Marmot too enable scale out if we want

[gedw99]

I think we can also eventually use surrealdb and nats

https://github.com/AltairInglorious/nexus/issues/1 has the flow of ideas to do it.

the package is quite simple and uses nats as a transport to surreal db. The nats payload holds the query in its payload and is then bound to the surreal db wuery.

There is no cgo required.

the surreal db server must be run in its own process.

Surreal db has live queries ! So when the data changes in the db ( due to a mutation ) it can be sent over nats and then the template rendered and then pushed over SSE and htmx.

This is something that SQLite cannot do

surrealdb is easily scaled out using tiDB and its tiup provisioning system too.

See: https://github.com/AltairInglorious/nexus/issues/2#issue-2208939709

[nickchomey]

I agree that surrealdb + nats support would be fantastic

[gedw99]

I agree that surrealdb + nats support would be fantastic

good news. It's a nice match with NATS IMHO. Both are designed to live queries. Someone needs to invent a new DB Term called a "SQuery" - a "subscription query" :)

[infogulch]

It should not be too difficult to write a surrealdb-specific dot provider if that is desired, since it wouldn't work through Go's sql.DB interface.

Once a surrealdb+nats integration is created I'm not sure what changes would be needed in xtemplate considering nats support already exists.

[gedw99]

I have not had any time to devote to surrealdb so am using SQLite for now.

just wanted to let everyone know .