Cloudflare Developers•3y ago

We've only created an empty table through the dashboard, no other action was performed

SSamantas5855 We've only created an empty table through the dashboard, no other action was per...

Isaac McFadyen•10/7/23, 11:26 AM

If you even used the console then you'll see rows read, because when the console loads it introspects your database.

IIsaac McFadyen If you even used the console then you'll see rows read, because when the console...

Samantas5855OP•10/7/23, 11:29 AM

Thank you, do you happen to know whether row deletes count as writes or not?

SSamantas5855 Thank you, do you happen to know whether row deletes count as writes or not?

Matt Silverlock•10/7/23, 11:49 AM

Yes, deletes are writes.

SSamantas5855 Thank you, do you happen to know whether row deletes count as writes or not?

Matt Silverlock•10/7/23, 11:51 AM

Also keep in mind that the first 25 billion reads are included in the basic paid plan, so you could refresh the dash 271 million times a month and be OK

MMatt Silverlock Yes, deletes are writes.

Samantas5855OP•10/7/23, 12:06 PM

Thank you very much. One last question: Since we are migrating our python scripts from mongodb to D1 is there any way to mass insert rows from an external script using the api, outside of workers?

SSamantas5855 Thank you very much. One last question: Since we are migrating our python script...

Matt Silverlock•10/7/23, 12:08 PM

Yes, you can query from the REST API: https://developers.cloudflare.com/api/operations/cloudflare-d1-query-database#Request

Cloudflare API Documentation

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

SSamantas5855 Thank you very much. One last question: Since we are migrating our python script...

Matt Silverlock•10/7/23, 12:09 PM

e.g. via a Python or Go client. Read from Mongo, slice up the rows, insert.

MMatt Silverlock e.g. via a Python or Go client. Read from Mongo, slice up the rows, insert.

Samantas5855OP•10/7/23, 12:13 PM

Clarification, we aren't transferring any data from mongodb, just migrating the scripts. Is there a way to insert many rows with one API request?

Paul•10/7/23, 1:12 PM

I’m thinking about swapping my Postgres out for D1. Can I still perform large queries across all my users? Any good UI like Dbeaver?

Nikil•10/7/23, 3:09 PM

Why does D1 use SQLite vs Postgres or MySQL? Unless I misunderstood the docs

Nikil•10/7/23, 3:16 PM

Is 2GB per table very much? I've never really built anything at a scale of more than 1k users so I have no idea.

My best comparision metric right now is a CSV that I have with 82 columns and 101k rows listing every single K12 school in america. That CSV is 46MB, and the Google Sheets version is apparently 16MB

NNikil Why does D1 use SQLite vs Postgres or MySQL? Unless I misunderstood the docs

Hello, I’m Allie!•10/7/23, 3:25 PM

SQLite is a lot easier to embed into a platform(and much lighter) than other DB Systems

HHello, I’m Allie!SQLite is a lot easier to embed into a platform(and much lighter) than other DB ...

Nikil•10/7/23, 3:26 PM

easier in what way?

NNikil Is 2GB per table very much? I've never really built anything at a scale of more ...

forslunds•10/7/23, 3:29 PM

All depends how much data you want to save. I have 8 users and around 3GB in the database. It will be higher limit in the future but also already supports alot of DBs so you could setup one per user (soon)

Fforslunds All depends how much data you want to save. I have 8 users and around 3GB in the...

Nikil•10/7/23, 3:32 PM

One per user - that sounds like a crazy pain to keep all of those schemas in sync?

Nikil•10/7/23, 3:35 PM

And it also sounds incredibly un-performant for specific types of queries at least

Nikil•10/7/23, 3:39 PM

Hmm I guess that's a relatively smart way to shard things, especially if a user is almost always in a defined location.

E.g. if I was creating software to manage parking garages or movie theaters, it could make sense to have 1DB per theater, and 1DB for the "central office" objects that might need to be referenced for all of the DBs

But it still sounds crazy and seems like it might increase the total # of writes?

forslunds•10/7/23, 3:41 PM

Or you could just use the DB you think works best for your solution and slap hyperdrive on it?

forslunds•10/7/23, 3:44 PM

I’m using D1 bc it’s cheap when needing alot of small dbs/projects , and fast everywhere!

Fforslunds Or you could just use the DB you think works best for your solution and slap hyp...

Nikil•10/7/23, 3:53 PM

If I knew what I was doing, I would agree with this sentiment. But I'm a novice at building product-caliber stuff, so I don't know where to begin other than I want it to be simple to deploy and not eat more than like $10k/yr at scale with a million users in the year.

At smaller scales, I want my whole app to cost < $1/user/mo

Basically, I'm building a course app. We teach high schoolers how to build real products such as Wildfire Simulator in Unity or a Webapp. (I already sell my product to school districts, but am trying to build some in-house custom features and better reporting compared to the third-party tools we're currently using, and then also enable some AI tutoring features in the future).

We host videos, interactive exercises, and quizzes basically. It's kind of like a Masterclass + Brilliant.org

We also have a couple of interesting real-time features such as an office hours queue (so as students are going through one of our projects they can ask a question, and then we group similar questions together and answer the group on a live video call).

Would love to design the system right such that we don't have to completely redo stuff in the near future.

NNikil If I knew what I was doing, I would agree with this sentiment. But I'm a novice ...

malone•10/7/23, 3:59 PM

estimate how big your database needs to be (define your schema and ask chatgpt how large the db would be with x rows in each table) if you’re comfortably under 2gb go with d1

Mmalone estimate how big your database needs to be (define your schema and ask chatgpt h...

Nikil•10/7/23, 4:13 PM

you think ChatGPT will really give a reasonable answer for this?

Mmalone estimate how big your database needs to be (define your schema and ask chatgpt h...

Max (@rozenmd)•10/7/23, 4:15 PM

don't rely on LLMs for maths

malone•10/7/23, 4:20 PM

haha a rough estimate

malone•10/7/23, 4:20 PM

and if it’s anywhere near it you’re probably in trouble

malone•10/7/23, 4:20 PM

feels fine for this task. wouldn’t expect it to be exactly right and you shouldn’t expect your estimate of rows in your db to be exactly right

MMax (@rozenmd)don't rely on LLMs for maths

Nikil•10/7/23, 4:21 PM

that's what I figured

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

Common for a “user” row to be 500-1000 bytes. So 2GB (which is the current, beta limitation) gets you to 2 - 4 million users.

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

(Just for that database, before any sharding, or increased per-DB sizing)

MMatt Silverlock (Just for that database, before any sharding, or increased per-DB sizing)

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

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...

Nikil•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...

Nikil•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...

Nikil•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...

Nikil•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

Nikil•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.

Nikil•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

We've only created an empty table through the dashboard, no other action was performed

Similar Threads

Similar Threads

Similar Threads