Use the console to describe your schema. `PRAGMA table_xinfo(table)`

Use the console to describe your schema.

PRAGMA table_xinfo(table)

PRAGMA table_xinfo(table)

PRAGMA table_xinfo(table)

PRAGMA table_xinfo(table)

Cchientrm.com I completely move from PlanetScale to D1, have stronk faith 💪

Tony Leung•8/4/23, 4:25 PM

is there anyway to migrate old D1 to new engine rather than dumping data from old database and import it to the new one?

TTony Leung is there anyway to migrate old D1 to new engine rather than dumping data from ol...

chientrm.com•8/4/23, 4:27 PM

nope I guess

chientrm.com•8/4/23, 4:27 PM

just backup in old engine, download, copy paste script to new db.

Tony Leung•8/4/23, 4:27 PM

chientrm.com•8/4/23, 4:28 PM

it's simple, they're not all SQLite,

Unsmart•8/4/23, 4:29 PM

I'm pretty sure its been said in the past that there would not be any sort of auto migration of them. It is an alpha product so weird things like this are to be expected

UUnsmart I'm pretty sure its been said in the past that there would not be any sort of au...

Tony Leung•8/4/23, 4:31 PM

yeah... it's not recommended to be used in production

benthecarman•8/4/23, 5:07 PM

Hey, we're using the D1 experimental backend, currently we're at 50mb in storage and are worried we could hit the 500mb cap, if we get close to that, is it possible to get that increased

Bbenthecarman Hey, we're using the D1 experimental backend, currently we're at 50mb in storage...

Cyb3r-Jak3•8/4/23, 5:12 PM

Right now the 500 MB is a global limit. I believe the blog post had 1GB for workers paid so they are probably working their way up

Caleb•8/4/23, 5:21 PM

anyone know when d1 will be out of open alpha

CCaleb anyone know when d1 will be out of open alpha

Epailes•8/4/23, 6:58 PM

https://tenor.com/view/ogvhs-crystal-ball-prediction-future-predict-gif-18199743

Tenor

Caleb•8/4/23, 7:16 PM

So no?

Cyb3r-Jak3•8/4/23, 7:19 PM

There is no date for when it leaves alpha stage

Caleb•8/4/23, 8:27 PM

sad

Caleb•8/4/23, 8:31 PM

I want to use it for production enviroment. I have a very small amount of data (maybe 200MB) with a pretty simple scheme with one table. I know D1 current state is not recommended for production environment, but for my case do you think it would be ok?

Mozzy•8/4/23, 8:31 PM

You'd be fine, until you're not

Mozzy•8/4/23, 8:42 PM

I use it in production, but for non-essential things

Trixrabbit•8/4/23, 10:00 PM

is having a

wrangler.conf

wrangler.conf

with d1 information

[[d1_databases]]
binding = "DB"                                       
database_name = "local_db"
database_id = "123456789"

[[d1_databases]]
binding = "DB"                                       
database_name = "local_db"
database_id = "123456789"

the same as using wrangler with --remote -d1=DB--remote -d1=DB ?

I'm struggling to see if/when/why I need wrangler.conf when using Pages

TTrixrabbit is having a `wrangler.conf` with d1 information ``` [[d1_databases]] binding = ...

Cyb3r-Jak3•8/4/23, 10:06 PM

Pages doesn't use

wrangler.toml

wrangler.toml

. You set all the bindings via the dashboard or API

CCyb3r-Jak3 Pages doesn't use `wrangler.toml`. You set all the bindings via the dashboard or...

Trixrabbit•8/4/23, 10:12 PM

I think there is undocumented behaviour with pages and d1 because wrangler says it parses

wrangler.conf

wrangler.conf

when using

wranger pages dev

wranger pages dev

MMozzy I use it in production, but for non-essential things

Caleb•8/4/23, 10:21 PM

yeah i need it for nonessentials

TTrixrabbit I think there is undocumented behaviour with pages and d1 because wrangler says ...

Cyb3r-Jak3•8/4/23, 10:21 PM

It might parse it due to it being wrangler but you need to pass binding in order for them to work locally

CCyb3r-Jak3 It might parse it due to it being wrangler but you need to pass binding in order...

Trixrabbit•8/4/23, 10:24 PM

gotcha, I just started playing with d1 and wrangler and there are things that aren't trivial or well explained.. most of the

wrangler d1

wrangler d1

commands seems to be only for remote, not local e.g.

list

list

...but it seems possible to apply migrations to local d1
very cool thing though, just requires a lot of trial and error to see what works

MMatt Silverlock Use the console to describe your schema. `PRAGMA table_xinfo(table)`

nick•8/5/23, 2:49 AM

After query, user_ id is a text type, but the content stored in the database has become a numerical type, and the stored code is the same as before. The old version of the database is normal

Vishnu Sankar•8/5/23, 8:34 AM

Does D1 support transaction?

I’m trying build a web app with next-on-pages, D1 and drizzle-orm.

Drizzle transaction API seems to be working fine for local development. However, it fails on production and preview deployments with the following error “Error: D1_Error: Error: not authorized”

This doesn’t looks like a binding issue as all other non-transaction calls to D1 works without any errors.

VVishnu Sankar Does D1 support transaction? I’m trying build a web app with next-on-pages, D1...

itsmatteomanf•8/5/23, 9:19 AM

D1 commands run in an implicit transaction, you can't write a specific transaction yourself.

Iitsmatteomanf D1 commands run in an implicit transaction, you can't write a specific transacti...

Vishnu Sankar•8/5/23, 9:29 AM

Are there any plans to support this? Without it, how do we ensure the integrity of data which requires multi step writes?

VVishnu Sankar Are there any plans to support this? Without it, how do we ensure the integrity ...

itsmatteomanf•8/5/23, 9:43 AM

The team mentioned possible plans to expose a transaction API, but not soon. Haven’t tried, but can’t you just add multiple commands in the same statement?

Vishnu Sankar•8/5/23, 9:57 AM

It’s not practical to write all commands in a single statement. Here’s a simple snippet of creating and org and adding a admin to it.

/**

Create org
@param userId
@returns*/export const createOrg = async (userId: string,input: CreateOrgFormSchemaType,) => {return db.transaction(async (txn) => { // Create org const org = await txn .insert(orgs) .values({ ...input, id: getNanoId() }) .returning() .get() // Create admin member await txn .insert(members) .values({ id: getNanoId(), userId, orgId: org.id, role: 'admin' }) .returning() .get() return org})}

VVishnu Sankar It’s not practical to write all commands in a single statement. Here’s a simple ...

itsmatteomanf•8/5/23, 9:59 AM

Not saying it's easy, but it can be done and I am not seeing anything there that doesn't allow it.

itsmatteomanf•8/5/23, 9:59 AM

It's a workaround, after all.

Vishnu Sankar•8/5/23, 10:01 AM

The only option is to use OUPUT INTO, however the above two statements are not the full logic, it involves even more inserts to multiple other tables.

Writing them all into one statement will make it hard to debug or maintain.

itsmatteomanf•8/5/23, 10:02 AM

Oh yeah, definitely not easy. From what the team has been saying they have some priorities before this. It's an alpha, after all.

Vishnu Sankar•8/5/23, 10:09 AM

Hmm, hope the team address this issue. It’s a must for any sort of complex logic

Bram•8/5/23, 11:19 AM

you can use batch statements in your case, considering you generate the ID client side

Original message was deleted

Vishnu Sankar•8/5/23, 11:20 AM

@nora8008 Looks like batching only execute array of statements, it can be useful for bulk writes.

From the docs, it doesn’t looks like it returns any values from previous statements in the batch array.

In my case, I’m trying to create an org and adding a member to it, to create a member row, I need to know the org id, which only returns from previous steps

Bram•8/5/23, 11:20 AM

but unfortunately Drizzle (or kysely) transaction api's are not supported yet, there are plans for a JS transaction API in the future

BBram you can use batch statements in your case, considering you generate the ID clien...

Vishnu Sankar•8/5/23, 11:22 AM

@alphadog_ Yes, for this sample code snippet, I’m generating the id. However I mentioned in my early message that it’s a small part of the logic.

The full org creation logic involves creation of other tables which are using incremental ids. Which are non-deterministic.

Original message was deleted

Vishnu Sankar•8/5/23, 11:26 AM

@nora8008 I’m sorry I didn’t get you. What benefit it would make by adding a status column?

Interactive transactions are really important for complex logic management.

Bram•8/5/23, 11:37 AM

I think nora means that you could coordinate the state of a transaction yourself by utilising an additional column. Let's say you have two batch statements and the 2nd goes wrong, you have a way to identify till how far your unit of work has executed. But I fully agree that it would be a great DX improvement if we could have a transaction API, but it is (as far as I know) not actively worked on. They are adding other core functionality first. Keep in mind it is in alpha and clearly states that it is not intended for production usage.

Vishnu Sankar•8/5/23, 11:42 AM

@alphadog_ Hmm, that’s bit confusing. Aren’t they supposed to rollback all the changes if any of the statement fails? In that case, what purpose the additional column serves?

I think for now, I’ll have to sacrifice the non-deterministic id part. That seems to be only option till the team implements js transaction api

VVishnu Sankar @alphadog_ Hmm, that’s bit confusing. Aren’t they supposed to rollback all the c...

Bram•8/5/23, 11:49 AM

I am not too sure if this is what Nora meant, but in a microservice architecture they have something called the "Saga pattern" (https://microservices.io/patterns/data/saga.html) where you coordinate a distrubuted transaction yourself. It boils down to having some kind of statemachine where you are responsible for rolling back changes along the way. You could apply a similar (simplified) approach in your app, but imo it's not worth the complexity, especially considering you have everything in one database. I would indeed avoid using auto incementing ids or use another database that integrates well with cloudflare workers (see https://developers.cloudflare.com/workers/databases/connecting-to-databases/)

microservices.io

Microservices Pattern: Sagas

Implement transactions using a saga, which is sequence of local transactions

Bram•8/5/23, 11:52 AM

Maybe turso could be a viable option, considering it is SQLite as well (it is build upon libsql & their client supports interactive tranactions: https://github.com/libsql/libsql-client-ts)

Vishnu Sankar•8/5/23, 11:55 AM

Thanks for the resources. The product I’m working on is in its MVP stage. So, I don’t want introduce any complex pattern for transaction handling.

I’m planning to use drizzle orm for all one-off queries and D1 bindings for writing batch transactions with deterministic ids.

CCaleb I want to use it for production enviroment. I have a very small amount of data (...

Epailes•8/5/23, 11:58 AM

A common recommendation is to use Planetscale as an interim solution until D1 matures.

It's an alpha, and whilst its unlikely for there to be something like unrecoverable data loss, or extended downtimes, they can happen. The size of your data isn't particularly relevant unless you're likely to breach the new limit they just pushed out.

Can you easily re-create your data? How much data can you afford to lose before it causes an issue? Can your application operate with a downtime outside of your control?

Original message was deleted

Adi•8/5/23, 3:05 PM

ngl this is the first time I've read code that shows a way to explicitly handle the CF max file upload limits on the different plans. Learned something new, thank you

VVishnu Sankar Thanks for the resources. The product I’m working on is in its MVP stage. So, I ...

Bram•8/5/23, 3:23 PM

You can still use drizzle for building the query, I am using Kysely and have a simple utility function:

export function batch(d1: D1Database, statements: InsertQueryBuilder<DB, any, any>[]) {
  return d1.batch(
    statements.map((s) => {
      const compiled = s.compile()
      return d1.prepare(compiled.sql).bind(...compiled.parameters)
    })
  )
}

export function batch(d1: D1Database, statements: InsertQueryBuilder<DB, any, any>[]) {
  return d1.batch(
    statements.map((s) => {
      const compiled = s.compile()
      return d1.prepare(compiled.sql).bind(...compiled.parameters)
    })
  )
}

I assume that drizzle has something similar to kysely's

compile

compile

function

BBram You can still use drizzle for building the query, I am using Kysely and have a s...

Vishnu Sankar•8/5/23, 3:28 PM

Yes, drizzle has sql statement generator. However, I implemented it inline. Looks much cleaner

Use the console to describe your schema. `PRAGMA table_xinfo(table)`

Similar Threads