Best way to write standalone scripts for D1 (local and remote)?
Original message was deleted
OTL;DR: I want to write standalone scripts that interact with D1 and I want them to work with both local and remote instances of D1. Standalone scripts don't have access to the DB instance. What's the next best thing? Items 1-7 are things I tested and are suboptimal.
---
Hey, everybody. I'm working on a personal blog using Remix, Drizzle ORM and CF Pages + D1. I want to store the basic info of each post on D1, and I want my local copy and production to match.
I was working on a standalone script that I can run whenever I feel like updating the posts: this script would go through the posts on my local filesystem, extract their data and store this data on D1. It looks like this is very hard to do consistently:
So, what am I missing here? What's the best solution for my use case?
---
Hey, everybody. I'm working on a personal blog using Remix, Drizzle ORM and CF Pages + D1. I want to store the basic info of each post on D1, and I want my local copy and production to match.
I was working on a standalone script that I can run whenever I feel like updating the posts: this script would go through the posts on my local filesystem, extract their data and store this data on D1. It looks like this is very hard to do consistently:
- Drizzle doesn't have the context to connect to the DB to run insert queries, unless it's running inside of a local worker. If I move my code to run in a local worker, I won't be able to connect to my remote database.
- Connecting Drizzle to a local SQLite DB allows me to have a database that has exactly the content I would want, but D1 doesn't support restores from SQLite binary files.
- Connecting Drizzle to a local SQLite DB allows me to grab the queries that would be performed, but the queries are often separated as SQL and params, with ? placeholders that will be interpolated by the database driver. D1 REST API supports this format, but only for remote databases.
wrangler d1 executeworks for both local and remote DBs, but I would need to manually interpolate the question marks with the insert data myself, which feels sloppy.- I could go back to using the local DB solutioh to dump my local DB into a .sql file so I can use wrangler to execute that file; it "works", but I'll have to install sqlite on my computer just for the sake of dumping the DB as SQL (also, you can see how this would suck tremendously for large DBs).
- I could add an endpoint to my app that would receive a POST request and trigger this standalone script, but this feels like a messy workaround. It would require that I have my local environment to accept the POST request as well, which is not a huge deal, but is awfully inelegant.
- I could use the REST API to publish data to production, and then export the database so I can import it locally. It will, however, block any DB requests while the export is running - which is bad for large DBs.
So, what am I missing here? What's the best solution for my use case?
Jlooks like an x/y problem here, all kinds of mixed concerns and architecture which doesnt make sense to your goal
OThe question is the title; the description I added lists the solutions I've already tried and the problems with them, so people don't have to suggest 1-7 if I've done them and there's problems with all of them.
In a nutshell: I want to write standalone scripts that interact with D1 and I want them to work with both local and remote instances of D1. Standalone scripts don't have access to the DB instance. What's the next best thing? Items 1-7 are things I tested and are suboptimal.
In a nutshell: I want to write standalone scripts that interact with D1 and I want them to work with both local and remote instances of D1. Standalone scripts don't have access to the DB instance. What's the next best thing? Items 1-7 are things I tested and are suboptimal.
Jthe standalone scripts aspect is the x of the y issue
you want y, but there are better ways of managing an online blog that has nothing to do with syncing two databases over the wire
you want y, but there are better ways of managing an online blog that has nothing to do with syncing two databases over the wire
OI'm sure there are, but I also believe there are problems with those alternatives as well (less flexibility, less control, worse performance, etc). I'm following Remix's suggestion of storing posts in a database.
OThe issue for me is that the D1 setup almost works, so I wanted to see if I was missing something or if maybe this is a use case that hasn't been quite hashed out yet. I'll stick with creating a temporary SQLite DB, dumping it into a .sql file and importing it into both local and remote instances.
