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

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.

Zzensin a rust mysql/sqlite lexer parser doesn't feel like a lot of overhead. websockets...

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

a lot of overhead vs not doing it and just having a local DB instead/replicating a local DB.
It might make sense for some simpler things once Read Replicas come out, but WP is a ton of queries, ehh

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

wait. I bet a DO could store an sqlite database as a blob, cache some queries, and replicate it across the network... lol

Zzensin wait. I bet a DO could store an sqlite database as a blob, cache some queries, a...

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

the original d1 implementation (now called alpha and going away) was using DO storage/all in DO afaik

Chaika•7/26/24, 2:00 AM

I still have..a few

zensinOP•7/26/24, 2:00 AM

I have stuff to do too. Just like to waste some time before the boring parts. back to it I guess

Zzensin wait. I bet a DO could store an sqlite database as a blob, cache some queries, a...

Chaika•7/26/24, 2:00 AM

DO's storage is pretty limited though, max value size is 128 KiB per key

Original message was deleted

zensinOP•7/26/24, 2:01 AM

yeah was about to say bring it in from object storage, which is a relatively dubious proposition

Chaika•7/26/24, 2:02 AM

problem is R2's performance can be a bit weak and it's not replicated either

Chaika•7/26/24, 2:02 AM

KV only has two central stores us/eu, and caching on every edge, so slow cold pulls

Chaika•7/26/24, 2:02 AM

cached it's not bad

Chaika•7/26/24, 2:02 AM

uncached..

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

If you really wanted a super fast cheap storage, embed your values straight into a worker and do a worker update when you need to update them, Workers max size is 10 MB on paid and if you only need infrequent updating.. lol

zensinOP•7/26/24, 2:04 AM

I could have sworn I once saw a repo that was doing some replication across DOs

Zzensin I could have sworn I once saw a repo that was doing some replication across DOs

Chaika•7/26/24, 2:04 AM

maybe https://github.com/cloudflare/dog ?

zensinOP•7/26/24, 2:04 AM

that's it. not remembering all it did

Chaika•7/26/24, 2:05 AM

hmm, should probably move to #workers-discussions or #off-topic since this isn't D1 related

zensinOP•7/26/24, 2:06 AM

right. anyway I can't get down that rabbit-hole or I won't get anything done. thanks for the going on about it with me

Phaze•7/26/24, 1:25 PM

Hi guys, i'm getting response times of >1 second when looking data up on D1, is this normal or am I doing something wrong?

Fabian•7/26/24, 2:03 PM

Hey, is there any known ETA yet for dynamic D1 bindings? :)

RexNormous•7/26/24, 2:07 PM

I'm trying to insert a potentially large amount of rows (up to 10,000 at max) at once to a D1 database. I'm getting failures due to maxing out the number of parameters to my sql statement.

I understand

batch

batch

is the solution to this, but can I send 10k sql inserts in one batch? Is there a limit to batch?

RRexNormous I'm trying to insert a potentially large amount of rows (up to 10,000 at max) at...

Hard@Work•7/26/24, 2:10 PM

The limit to a batch is just how many you can cram into memory. I would say batch is a decent bit more efficient than a single large insert

Chaika•7/26/24, 2:10 PM

yea it was said before someone got 100k queries in a batch without issues: https://discord.com/channels/595317990191398933/992060581832032316/1180147342373044274

I use batch with upserts up to ~1-2k rows without issue

CChaika yea it was said before someone got 100k queries in a batch without issues: https...

RexNormous•7/26/24, 2:15 PM

Excellent, thank you both. Great to hear that batch isn't hard-limited!

CChaika https://developers.cloudflare.com/workers/platform/limits/ free gets 1 mb max si...

Chinoman10•7/26/24, 10:34 PM

Yea this 1MB max. size limitation hit me when I built a Functions backend with some stuff from the Typescript package... as soon as I imported it it exploded

. So I had to scrap that whole beautiful development I made, and now it's exclusive to our own company (where we are gladly paying for Workers Paid), since none of our clients want to pay every month for things that get called once per week or something similar.

debug•7/26/24, 11:05 PM

Hi guys, does anyone tried using the D1 rest api ?

Ddebug Hi guys, does anyone tried using the D1 rest api ?

Chinoman10•7/27/24, 11:46 AM

Yes, we use it, why do you ask?

CChinoman10 Yes, we use it, why do you ask?

debug•7/27/24, 11:47 AM

All good, i had lots of troubles to set it up, mostly because of cors and preflight requests

Fishy!•7/28/24, 2:16 AM

Hey, is there a link to the specification for how dynamic bindings and migrations will fit together?

surgery18•7/28/24, 6:03 PM

Does D1 support triggers? Whenever I try to run the migration with --remote to create a trigger, I get "X [ERROR] incomplete input: SQLITE_ERROR [code: 7500]" Example code of what I am trying to run is like this

-- Create a simple table
DROP TABLE IF EXISTS test_table;

CREATE TABLE IF NOT EXISTS test_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Drop any existing trigger
DROP TRIGGER IF EXISTS test_trigger;

-- Create a simple trigger
CREATE TRIGGER test_trigger
AFTER UPDATE ON test_table
FOR EACH ROW
BEGIN
  UPDATE test_table
  SET updated_at = CURRENT_TIMESTAMP
  WHERE id = OLD.id;
END;

-- Create a simple table
DROP TABLE IF EXISTS test_table;

CREATE TABLE IF NOT EXISTS test_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Drop any existing trigger
DROP TRIGGER IF EXISTS test_trigger;

-- Create a simple trigger
CREATE TRIGGER test_trigger
AFTER UPDATE ON test_table
FOR EACH ROW
BEGIN
  UPDATE test_table
  SET updated_at = CURRENT_TIMESTAMP
  WHERE id = OLD.id;
END;

surgery18•7/28/24, 6:04 PM

But if I run it with --local it is fine.

Ssurgery18 Does D1 support triggers? Whenever I try to run the migration with --remote to ...

boop•7/28/24, 10:18 PM

It does support triggers d1-database

surgery18•7/28/24, 11:09 PM

Ok, this is strange. I cannot do the triggers during migrations, but I can run a query manually via the console on the web to create the triggers. That might be something to look at if that is a bug. I would think it should be able to create triggers during migrations.

Darkblacks•7/29/24, 5:43 AM

Hey guys. Is there a column limit on the D1 database? I heard it was 100. If so is there a way to increase that?

몰

몰라세스•7/29/24, 5:45 AM

If Sqlite's column limit is 100 col, no ways of increase.

몰

몰라세스•7/29/24, 5:46 AM

But, it has using relation or DB normalization.

Darkblacks•7/29/24, 5:48 AM

I see that the the SQLite has a 2000 column limit but not sure if D1 is restricting it

Darkblacks•7/29/24, 5:49 AM

Also is there a limit on storage per database? I know it says 0.76/GB after 10gb but is there a limit

Darkblacks•7/29/24, 5:49 AM

I am trying to migrate from MS SQL so looking at options

Darkblacks•7/29/24, 6:00 AM

I just tried creating a table with over 100 columns and it gives me an error saying too many columns. Why is there a limit on this when native SQLite supports way more?

Chaika•7/29/24, 6:12 AM

D1 follows sqlites high security recommendations (although they've relaxed some), SQLite recommends 100 max https://www.sqlite.org/security.html

Darkblacks•7/29/24, 6:16 AM

Thanks for that. Would you happen to know if there is a restriction on storage for D1. I am in the worker paid plain and it says 5GB free then 0.75/GB after that. Is there a limit on how much I can store since the docs have conflicting answers

Chaika•7/29/24, 6:20 AM

Limits page has those answers: https://developers.cloudflare.com/d1/platform/limits/

Cloudflare Docs

Limits · Cloudflare D1 docs

If you would like to explore other storage solutions for your application, Cloudflare also offers Workers KV, Durable Objects, and R2.

Chaika•7/29/24, 6:21 AM

On paid, 10 GB max per db and 250 GB per account (account max can be raised, per DB is more of a hard limit they haven't been raising for each customer)

-- Create a simple table DROP TABLE IF EXISTS test_table; CREATE TABLE IF NOT EXISTS test_table ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- Drop any existing trigger DROP TRIGGER IF EXISTS test_trigger; -- Create a simple trigger CREATE TRIGGER test_trigger AFTER UPDATE ON test_table FOR EACH ROW BEGIN UPDATE test_table SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id; END;

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

Similar Threads