search

infogulch / xtemplate

A html/template-based hypertext preprocessor and rapid application development web server written in Go.
Apache License 2.0
73 stars 1 forks source link

Db scale out of SQLite using NATS #16

Open gedw99 opened 5 months ago

gedw99 commented 5 months ago

I would like to suggest https://github.com/maxpert/marmot be considered as a SQLite db scale out approach.

It uses NATS and so it’s a nice synergy for the nats kv provider.

Marmot is currently used for SQLite Db. It provides a multi master strategy at the moment, using a LWW ( last write wins ) strategy. There are some trade offs with this like any multi master scenario.

This will mean that all instances of caddy can have a db, so any downtime of any instance will not affect the global system. When it comes back up it will catchup.

New instances will also catchup I believe.

DB Schema migrations are not yet part of marmot. There is an issue for it.

it’s currently used with pocketbaae too. I have run it and it worked well.

please let me know how you feel about this @infogulch

infogulch commented 5 months ago

That looks cool. I'm not sure if xtemplate needs to do anything in particular to enable this, it should work out of the box given that marmot is a sidecar process.

gedw99 commented 4 months ago

true.

Maybe add it to the README or some pertinent area of the docs.

SQLITE and scalout / backup is a common need.

gedw99 commented 2 weeks ago

I have since moved to use the SQL Server that another golang htmx system uses.

I still use xtemplate, but I had to marry it with other things to get the DB and other things working with it.

https://github.com/delaneyj/toolbelt/tree/main/sqlc-gen-zombiezen

Which is used by the datastar project which is a type of htmx rendering system.

Its has SSE built in, and so the DB changes can feed the HTMX. It uses NATS Jetstream so marry up SQL changes to be a stream that feeds the HTMX layer. So now it's all reactive. Change the data and everything above it changes.

SO NATS and the DB are married up, allowing DB scale out also. All SQL goes to the DB via NATS too. SO a SQL migration is detected, and NATS will just run that on all the DB instances. A Mutation is the same in that its run on all of the instances. a Query is just a NATS Queue, which needs to only hit one of the DB instances.

NATS can run InProcess now too, so you can do it without any sockets IF you want. Or at Leaf node level or at Cluster or super cluster level. SO you end uo with this triple layer cache, where the DB can live at those 2 places also if you want. NATS ensures all DBS stay up to date filly. It's fully serialisable and so you never have the split brain problem.

Marmot does it the other way around, and sits behind the DB listening to the WAL. This means that the transaction has already been make on one of the DB's. It's good, and faster but less safe and also cant do real time migrations, because the schema has already changed on 1 of the instances.

When the data flows through NATS, it's much easier. It does not have to be slow, because you can run as many NATS Servers as you want in your architecture, with each doing different things at different layers of your architecture.

infogulch commented 2 weeks ago

Can you say a bit more about which projects you're integrating with xtemplate? I'm curious about your design here.

gedw99 commented 2 weeks ago

Open science

it’s an EU project to allow scientists to collaborate. Like Google workspace but self hosted on premise .

The data resides on premise , and nats provides a tunnel .

infogulch commented 2 weeks ago

That sounds like a cool project.

I'm also curious about what other Go libraries you are integrating with xtemplate to build it. Specifically, you mentioned:

I have since moved to use the SQL Server that another golang htmx system uses.

I still use xtemplate, but I had to marry it with other things to get the DB and other things working with it.

What is the other golang htmx system, and what are the other things you had to marry to get the db and other things working?

gedw99 commented 2 weeks ago

Well the project is in a state of flux.

The main thing is that it's fully reactive so just use a SQLITE DB for now with NATS tracking what SQL query a HTTP Endpoint is mapped to. It's like a presence system, so we know when to do a HTMX push over SSE when the DB changes. Does that makes sense ?

When the user moves to a different http or http fragment, we just have to track that. When they leave also we track that.

Nats has so many uses !!

gedw99 commented 1 week ago

I will open other issues about other aspects I am working on with Xtemplate .

For now it was about sql scale out , so that the db is HA .

marmot runs as a separate process , but I need to test it with the current SQLite provider .

Can then submit a proper PR . It will probably just need a task or make file to set it up , run it and massage the caddy config perhaps .