https://github.com/WordPress/sqlite-database-integration/pull/9

GitHub

SQLite support via a MySQL parser by adamziel · Pull Request #9 · W...

Description
Implements SQLite support using a MySQL lexer instead of regular expressions to:

Greatly improve security
Solve most WordPress core unit test failures
Make the plugin more maintenable
...

Zzensin nvm. It apparently doesn't support remote connections outside cloudflare. Someon...

Chaika•7/26/24, 1:03 AM

well I mean it's Sqlite so there's no native remote connection format, but you can send queries via the Cloudflare API (but they're all funneled through one of the cores right now, so user -> api in core -> d1 running somewhere) or make a Worker for it which run everywhere (but you'd have to set it up/secure it and pay for each invocation)

CChaika well I mean it's Sqlite so there's no native remote connection format, but you c...

zensinOP•7/26/24, 1:05 AM

you can send queries via the Cloudflare API

you mean via the implementation already in D1 client library?

they're all funneled through one of the cores

What is meant by this?

Zzensin > you can send queries via the Cloudflare API you mean via the implementation a...

Chaika•7/26/24, 1:07 AM

you mean via the implementation already in D1 client library?

I mean you can send a query via the Cloudflare API to be executed
https://developers.cloudflare.com/api/operations/cloudflare-d1-raw-database-query?schema_url=https%3A%2F%2Fraw.githubusercontent.com%2Fcloudflare%2Fapi-schemas%2Fmain%2Fopenapi.yaml

What is meant by this?

Worker goes
User -> Cloudflare data center (hopefully pretty close) -> D1 Instance (running somewhere)
API Queries go
User -> Cloudflare data center -> Cloudflare core (either us or eu, I think the D1 API is just US/west coast but not 100% sure) -> D1 Instance (running somewhere) and back. Of course very big difference espec if your D1 instance was in Oceanic region/Australia for example, lots of going back and forth

Cloudflare API Documentation

Interact with Cloudflare's products and services via the Cloudflare API

Zzensin https://github.com/WordPress/sqlite-database-integration/pull/9

Chaika•7/26/24, 1:08 AM

this is just for local sqlite as far as I can see but you could probably use some of the base work and hook it up to a worker/the api, would just have to handle responses/latency/etc, not sure how well Wordpress would play with it, espec with some of those plugins which make a crazy amount of queries, when D1 isn't same machine/can have decent query times

Isaac McFadyen•7/26/24, 1:10 AM

I'd be doubtful that you'd be able to hook that Wordpress SQLite update up to D1 in an efficient/effective way. It's already a bit tenous last I tried it because it's translating the MySQL commands to SQLite, adding an extra layer in there with a bunch of latency will only make things worse.

CChaika this is just for local sqlite as far as I can see but you could probably use som...

zensinOP•7/26/24, 1:10 AM

I wasn't thinking of only wordpress, though one does occasionally get forced to deal with it. I mentioned WP bc they have an effort underway (I'd say it's in beta/RC stage) to adapt all their mysql queries to sqlite. I was thinking about maybe porting their mysql lexer to a cloudflare worker.

zensinOP•7/26/24, 1:10 AM

that is if the lexer plays nice with D1

IIsaac McFadyen I'd be doubtful that you'd be able to hook that Wordpress SQLite update up to D1...

Chaika•7/26/24, 1:10 AM

start batching all of the queries or something lol

Chaika•7/26/24, 1:11 AM

yea would probably be a ton of work to try to get it semi-usable

Zzensin that is if the lexer plays nice with D1

Chaika•7/26/24, 1:12 AM

well D1 is just SQlite so I would assume it'd be fine if they got it working with sqlite there, there is some additional security restrictions (like with query/etc anti-abuse limits) they use that have cropped up in the past but I think they've eased up on a lot of them

Isaac McFadyen•7/26/24, 1:13 AM

There's also a few commands that are restricted in D1 vs regular SQLite for security reasons (some of the PRAGMAs) but yeah, otherwise it's pretty similar.

zensinOP•7/26/24, 1:13 AM

then probably most annoying points would be:

porting from PHP (Cause who wants to run php in a worker, though you can transpile)
transport of mysql connection over websockets (Idk if that's natively supported/option)

Zzensin then probably most annoying points would be: - porting from PHP (Cause who wants...

Isaac McFadyen•7/26/24, 1:14 AM

D1 can't connect via the MySQL protocol at all, so the second part wouldn't really be a consideration - you'd need to just adapt the commands to be proper SQLite queries before sending to D1.

Isaac McFadyen•7/26/24, 1:15 AM

If you mean connecting to actual MySQL from Workers then I believe there were some efforts to get some of the MySQL drivers to work with the Workers TCP Sockets API.

Isaac McFadyen•7/26/24, 1:15 AM

Not sure how those ended up.

IIsaac McFadyen D1 can't connect via the MySQL protocol at all, so the second part wouldn't real...

zensinOP•7/26/24, 1:15 AM

right, that's what the worker would be for. Translating the mysql connection to sqlite commands

Chaika•7/26/24, 1:16 AM

well workers can't do inbound tcp and websocket stuff would be inefficient

Chaika•7/26/24, 1:16 AM

think your best luck if you really wanted to throw some time away is to take the work they did there to get local sqlite working and hook up their translated queries up to a worker just proxying the query/results

Chaika•7/26/24, 1:18 AM

if you wanted to actually run wordpress itself on workers that's a whole different beast lol, I know people got php working (wasm compiled, not just transpiled iirc) but not sure about the rest, Workers have a size limit of 10 mb and a startup limit of 400ms which can be a bit bothersome

CChaika if you wanted to actually run wordpress itself on workers that's a whole differ...

zensinOP•7/26/24, 1:18 AM

I think it's 128MB on paid plan but that's still too low

Zzensin I think it's 128MB on paid plan but that's still too low

Chaika•7/26/24, 1:19 AM

no that's memory, 128mb of memory

Chaika•7/26/24, 1:19 AM

https://developers.cloudflare.com/workers/platform/limits/ free gets 1 mb max size, paid gets 10 mb max workers size

zensinOP•7/26/24, 1:19 AM

ooh

Chaika•7/26/24, 1:19 AM

(after gzip)

zensinOP•7/26/24, 1:20 AM

what about websockets make it a problem? I know it has binary mode

Chaika•7/26/24, 1:22 AM

Depends on how you want to do it. Workers are short lived, part of the problem. If you wanted to actually run php/wp on workers establishing a websocket for each incoming request to another worker/durable object I think would be more expensive then just simple queries (espec if you can batch them into a few)

Chaika•7/26/24, 1:23 AM

if you were going to do wp on an actual server you could use DOs to keep the websockets alive/sleep them, maybe more efficient to keep them open long term

Chaika•7/26/24, 1:24 AM

sounds like a mess eitherway though lol, D1's cool for a lot of things, espec if we get read replicas, but WP is its own beast with the amount of queries it can make espec with plugins, not natively supporting sqlite even, and trying to run it is a whole different story

CChaika Depends on how you want to do it. Workers are short lived, part of the problem. ...

zensinOP•7/26/24, 1:31 AM

Wouldn't want to deal with added latency of API defeating much of the purpose.

A DO with hibernation seems okay as long as nothing about websockets makes it rly bad. Assuming the mysql lexer can relatively painlessly be ported to typescript or rust even, and then take some mysql proxy code (Of which there must be plenty) and slapt that in there. Better yet something like this would be an accelerated start running alongside wordpress (Or whatever): https://github.com/novnc/websockify

Zzensin Wouldn't want to deal with added latency of API defeating much of the purpose. ...

Chaika•7/26/24, 1:35 AM

you're trying to run wp on a normal server, and use D1 as its database? Or what's your end goal, exactly?

CChaika you're trying to run wp on a normal server, and use D1 as its database? Or what'...

zensinOP•7/26/24, 1:35 AM

wp or anything else

zensinOP•7/26/24, 1:36 AM

I'm not claiming it would be good. just that it might be good

zensinOP•7/26/24, 1:36 AM

since the benefit (or one of them) of d1 is it's always close-by, right?

Zzensin since the benefit (or one of them) of d1 is it's always close-by, right?

Chaika•7/26/24, 1:37 AM

as it is right now a D1 database is only in a singular location, no read replicas yet
and only where Durable Objects are supported https://where.durableobjects.live/

CChaika as it is right now a D1 database is only in a singular location, no read replica...

zensinOP•7/26/24, 1:37 AM

I was not aware there are no replicas

Chaika•7/26/24, 1:38 AM

even when there is read replicas it's not going to be anywhere anywhere but it might make your idea more sensible

Original message was deleted

zensinOP•7/26/24, 1:38 AM

I addressed that above

Original message was deleted

Chaika•7/26/24, 1:38 AM

well that's what he was talking about, translating the queries

Chaika•7/26/24, 1:38 AM

idea could be cool espec with read replicas come out, just complex

Isaac McFadyen•7/26/24, 1:39 AM

Also:

take some mysql proxy code (of which there must be plenty)

There's likely plenty of proxy code, but almost certainly none currently that work on Workers.

Isaac McFadyen•7/26/24, 1:39 AM

Workers uses a custom implementation of a TCP Sockets API, not standard Node.JS APIs.

Chaika•7/26/24, 1:39 AM

yeaaa would have to adapt to the web js environment, or hope there's enough node compat to make it work if it was just largely based on buffers and such, could potentially steal a good chunk of it

IIsaac McFadyen Also: > take some mysql proxy code (of which there must be plenty) There's like...

zensinOP•7/26/24, 1:39 AM

https://github.com/brettsmentek/Node-MySQL-to-SQLite
making no claims to how good or bad this is

GitHub

GitHub - brettsmentek/Node-MySQL-to-SQLite: Express app for convert...

Express app for converting MySQL data to SQLite. Contribute to brettsmentek/Node-MySQL-to-SQLite development by creating an account on GitHub.

Zzensin https://github.com/brettsmentek/Node-MySQL-to-SQLite making no claims to how goo...

Isaac McFadyen•7/26/24, 1:40 AM

Yes, and this would work with Node, but Workers aren't Node and can't run Express.

Original message was deleted

zensinOP•7/26/24, 1:40 AM

yes, rust can come too

IIsaac McFadyen Yes, and this would work with Node, but Workers aren't Node and can't run Expres...

zensinOP•7/26/24, 1:41 AM

I wouldn't use express. I'd use the mysql websocket proxy linked above

zensinOP•7/26/24, 1:41 AM

and obviously just take the part of the code from mysql-to-sqlite repo that does the translating

Original message was deleted

zensinOP•7/26/24, 1:45 AM

if I had to guess, this is probably the most advanced/worked on translator to sqlite: https://github.com/WordPress/sqlite-database-integration/pull/9 though idk if any of that wound up being wp-specific/not portable

Original message was deleted

zensinOP•7/26/24, 1:46 AM

sometimes 'because we can' is as important as 'because we should'

zensinOP•7/26/24, 1:55 AM

a rust mysql/sqlite lexer parser doesn't feel like a lot of overhead. websockets to a worker doesn't either but D1 not being replicated is a nonstarter. I will relent. For now, the dream is dashed.

https://github.com/WordPress/sqlite-database-integration/pull/9

Similar Threads