ok so if I want to track users' progress through sections of a course (and let's say a course has 50

ok so if I want to track users' progress through sections of a course (and let's say a course has 50 sections)
there would be ~50 rows per user in the user progress table.

Does that mean I'm actually limited to something like 2GB * (1 progress row / 1MB) * (1 user / 50 progress rows) = 2 million / 50 = 40,000 users?

MMatt Silverlock Common for a “user” row to be 500-1000 bytes. So 2GB (which is the current, _bet...

NikilOP•10/7/23, 4:27 PM

that's v helpful btw

NNikil ok so if I want to track users' progress through sections of a course (and let's...

itsmatteomanf•10/7/23, 4:29 PM

Wait, I doubt you'd need that much data to store the progress...

Iitsmatteomanf Wait, I doubt you'd need that much data to store the progress...

Matt Silverlock•10/7/23, 4:31 PM

What Matteo said. Increment a counter. Store a “last chapter” variable. I wouldn’t store a “row per event” in any database as it makes your queries far more complex.

MMatt Silverlock What Matteo said. Increment a counter. Store a “last chapter” variable. I wouldn...

itsmatteomanf•10/7/23, 4:32 PM

But even if they wanted a progress per section, just do a table with user_id, section_id, progress, with the first two as primary key.

MMatt Silverlock What Matteo said. Increment a counter. Store a “last chapter” variable. I wouldn...

NikilOP•10/7/23, 4:34 PM

we're storing completion times also

Iitsmatteomanf But even if they wanted a progress per section, just do a table with user_id, se...

NikilOP•10/7/23, 4:34 PM

agreed about the joined primary key

NNikil we're storing completion times also

itsmatteomanf•10/7/23, 4:34 PM

Then do as I said, it's definitely less than 1000B per row...

itsmatteomanf•10/7/23, 4:35 PM

Just add a time completed column, and probably remove the progress, if it's straight done or not per section.

Iitsmatteomanf Just add a time completed column, and probably remove the progress, if it's stra...

NikilOP•10/7/23, 4:36 PM

yea something like that. I think there's a field for manual checkoff or something as well, but you're right that join table doesn't seem like it should be that complicated

NikilOP•10/7/23, 4:37 PM

so the way it gets somewhat complicated is that we count a person's progress as done if they've met the criteria which would be to get certain quiz questions right and at least submit a certain set of quiz questions (graded for completion) or if they have a manual check off.
But I guess my worker should be calculating that per user and not storing that result in the table?

NNikil yea something like that. I think there's a field for manual checkoff or somethin...

itsmatteomanf•10/7/23, 4:38 PM

In SQL you should always join, never repeat data (except in particular cases, but doubt it's gonna be this one)

NNikil so the way it gets somewhat complicated is that we count a person's progress as ...

itsmatteomanf•10/7/23, 4:39 PM

Data structure is a pain to figure out, you might also consider storing a JSON column with the answers... and a column with the calculation result, no need to redo it for a single boolean column.

NikilOP•10/7/23, 4:42 PM

Yea, we've been using Coda (similar to Notion but with much more powerful tables and APIs) to prototype this thing. Working surprisingly well with our group of <100 users. So some of the columns in Coda are just there for calculations and probably wouldn't need to be there in a real SQL table thanks to joins and having code on front or backend that would do the calculations

w3bcode•10/7/23, 4:47 PM

How to create D1 database programmatically while applying exisiting migrations ? And bind it to same worker ?

I want to achieve the multi tenant as database per tenant.

MMatt Silverlock FYI: you will be able to create up to 50,000 D1 databases per account as of this...

w3bcode•10/7/23, 4:58 PM

I couldnt find any docs/tutorials related to create a d1 database programmatically for the same worker. I wonder if I can keep same binding name for all the database that I create for the same worker.

How does the migrations work for those databases ?

Manually running command to apply migrations will take a lot of time.

forslunds•10/7/23, 5:18 PM

This is something they (CF) are working on, programmatically creating/binding. Needed for the other services also

Anuraag•10/7/23, 5:20 PM

Hi, quick question, does D1 require connection pooling? I'm using Next.js for a project and it doesn't support singletons for the purpose of supporting serverless, and all of its docs point to connecting to the DB when a request is received, but will that be a problem with D1?

zegevlier•10/7/23, 5:22 PM

No, D1 is designed to work in serverless enviornmens where there are no persistent connection.s

Fforslunds This is something they (CF) are working on, programmatically creating/binding. N...

w3bcode•10/7/23, 5:22 PM

Got it. Till then I will do it manually.

Zzegevlier No, D1 is designed to work in serverless enviornmens where there are no persiste...

Anuraag•10/7/23, 5:23 PM

Oh, that's good news then. Thanks

w3bcode•10/7/23, 5:25 PM

I am just curious to know if I xan have multiple DB on same binding name or do i have to create seperate binding for each db.

Reason for this is because, I am not sure how multiple bindings for same service like d1 would work ?

Ww3bcode I am just curious to know if I xan have multiple DB on same binding name or do i...

forslunds•10/7/23, 5:30 PM

If you do an insert on DB-binding. Do you want it on all DBs or just the “current users” db? I guess you need separate for every DB you want to connect

Mmalone haha a rough estimate

NikilOP•10/7/23, 6:48 PM

@malone yea Don't trust it for math @rozenmd

malone•10/7/23, 6:55 PM

when i gave it my schema and said "estimate how big the table would be with 3 million rows" it got it pretty spot on, said 500mb when it is 400mb (due to not taking up the full size of the columns)

malone•10/7/23, 6:55 PM

ymmv

malone•10/7/23, 6:57 PM

it estimating 1kb per row was definately a miss, im guessing it doesn't have enough context of your table schema bc that's way too high

NNikil @malone yea Don't trust it for math @rozenmd

malone•10/7/23, 6:57 PM

that math is correct

malone•10/7/23, 6:57 PM

anyway, ignore me lmao

Fforslunds If you do an insert on DB-binding. Do you want it on all DBs or just the “curren...

w3bcode•10/7/23, 10:18 PM

on Seperate DB depending on tenant

cristiandley•10/8/23, 3:39 AM

Hey Guys, i have a db and corresponding preview, but now i can't apply mirgations since it returns an error

✘ [ERROR] Can't find a DB with name/binding 'preview-mydb' in local config. Check info in wrangler.toml...

✘ [ERROR] Can't find a DB with name/binding 'preview-mydb' in local config. Check info in wrangler.toml...

but i have a definition like so

[[d1_databases]]
binding = "DB"
database_name = "mydb"
database_id = "main-id"
preview_database_id = "preview-id"

[[d1_databases]]
binding = "DB"
database_name = "mydb"
database_id = "main-id"
preview_database_id = "preview-id"

전전현우 Hi. Im trying to use Cloudflare D1 on Next.js Project. But I can't found any res...

James•10/8/23, 8:05 AM

You could use something like cf-bindings-proxy to access it in

next dev

next dev

Adi•10/8/23, 9:42 AM

The introduction of Hyperdrive got me thinking. Query caching is a very useful tool for any database. Are there plans to implement query caching in D1 at some point? Or is this something we would be expected to build ourselves?

Michał•10/8/23, 9:47 AM

Hi! Is global read replication already available for D1?

MMichał Hi! Is global read replication already available for D1?

Hello, I’m Allie!•10/8/23, 9:47 AM

Not yet

Michał•10/8/23, 9:48 AM

Thanks. Is there a timeline for that? Or maybe a closed alpha we could participate in?

MMichał Thanks. Is there a timeline for that? Or maybe a closed alpha we could participa...

Hello, I’m Allie!•10/8/23, 9:52 AM

is the only answer we can provide atm

w3bcode•10/8/23, 10:19 AM

Can anyone help me to access multiple D1 database from same worker ? I want to use the multiple database feature per tenant. But couldnt figure out how

Ww3bcode Can anyone help me to access multiple D1 database from same worker ? I want to u...

Michał•10/8/23, 10:23 AM

You should be able to add multiple bindings under different names

MMichał You should be able to add multiple bindings under different names

w3bcode•10/8/23, 11:24 AM

but with this, I wont be able to use same API for multitenant

I would need to deploy each worker seperately for each worker I guess.

Is there any better way to support multitenant with D1 database ?

Michał•10/8/23, 11:45 AM

You can have separate workers for each tenant. I'm not sure if that's the "official" way of doing things but that's how we're doing it.

MMichał You can have separate workers for each tenant. I'm not sure if that's the "offic...

w3bcode•10/8/23, 11:49 AM

got it. thanks

Mmalone that math is correct

NikilOP•10/8/23, 11:59 AM

oh yea it is huh

Paul•10/8/23, 3:03 PM

Is d1 production ready?

Paul•10/8/23, 3:16 PM

I’m super pumped to switch things off supabase

bkyerv•10/8/23, 3:17 PM

hello everyone, I'm encountering a problem when applying drizzle generated migrations to Cloudflare D1. The initial migration is processed without any hiccups, but I run into an issue with subsequent migrations, especially those that modify existing columns. Here's the error I'm receiving:

/*
 SQLite does not support "Changing existing column type" out of the box, we do not generate automatic migration for that, so it has to be done manually
 Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php
                  https://www.sqlite.org/lang_altertable.html
                  https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3

 Due to that we don't generate migration automatically and it has to be done manually
*/

/*
 SQLite does not support "Changing existing column type" out of the box, we do not generate automatic migration for that, so it has to be done manually
 Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php
                  https://www.sqlite.org/lang_altertable.html
                  https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3

 Due to that we don't generate migration automatically and it has to be done manually
*/

I feel this is related to D1 than to drizzle as applying migrations locally runs smooth and raise errors only when attempting to run migrations to remote db

Bbkyerv hello everyone, I'm encountering a problem when applying drizzle generated migra...

Adi•10/8/23, 3:23 PM

That looks like the output of drizzle kit and not a D1 error.

The comment it's generated is accurate. Nothing to do with D1.

bkyerv•10/8/23, 3:26 PM

I see. why can it then apply them locally and raise error in case remote db is attempted?

/* SQLite does not support "Changing existing column type" out of the box, we do not generate automatic migration for that, so it has to be done manually Please refer to: https://www.techonthenet.com/sqlite/tables/alter_table.php https://www.sqlite.org/lang_altertable.html https://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3 Due to that we don't generate migration automatically and it has to be done manually */

ok so if I want to track users' progress through sections of a course (and let's say a course has 50

Similar Threads