Does D1 support transaction? I’m trying build a web app with next-on-pages, D1 and drizzle-orm. D

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 SankarOP•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 SankarOP•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 SankarOP•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 SankarOP•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 SankarOP•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 SankarOP•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 SankarOP•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 SankarOP•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 SankarOP•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 SankarOP•8/5/23, 3:28 PM

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

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

Vishnu SankarOP•8/5/23, 4:44 PM

Yes there’s a ‘toSQL’ function. Which returns sql statement and params array

Trixrabbit•8/6/23, 12:09 AM

Is there a type definition for

D1Database

D1Database

available anywhere?

TTrixrabbit Is there a type definition for `D1Database` available anywhere?

Trixrabbit•8/6/23, 12:18 AM

solved, found the npm package

@cloudflare/workers-types

@cloudflare/workers-types

chientrm.com•8/6/23, 2:41 AM

how can I add a unique column? like add column

email

email

to my existing table

user

user

Nnarumi hello, I'm curious whether D1 supports or will support restore from dump file? I...

Matt Silverlock•8/6/23, 12:44 PM

https://developers.cloudflare.com/d1/learning/importing-data/#converting-sqlite-database-files

Importing data · Cloudflare D1 docs

D1 allows you to import existing SQLite tables and their data directly, enabling you to migrate existing data into D1 quickly and easily. This can be …

Greg•8/6/23, 8:40 PM

hey im just starting to play around with d1 and the latest version of wrangler doesn't seem to support

--experimental-backend

--experimental-backend

as mentioned in the docs

Codename_404•8/7/23, 2:37 AM

Will it be a good idea to start building an application with d1 now? Or should we use other dbs?

CCodename_404 Will it be a good idea to start building an application with d1 now? Or should w...

Dahoom•8/7/23, 3:24 AM

I would say test it right now. If you decide its not for you, then you can easily switch to another database with little refactoring since its just regular sql commands

Dahoom•8/7/23, 3:25 AM

The main upside with it is the setup with workers and pages functions is painless

LeftSpace•8/7/23, 3:32 AM

beta engine looks scary tho you don't have dump or download backup

LeftSpace•8/7/23, 3:32 AM

Im still using alpha

LeftSpace•8/7/23, 3:35 AM

just tested if we can migrate from alpha to beta,yeah it works i'll move to beta when its safer to use

LeftSpace•8/7/23, 3:42 AM

if anyone looking for migrate alpha to beta engine here's what i did

/*update your wrangler first*/
>> wrangler update
npm install -g wrangler

>> create new db with beta engine
wrangler d1 create dbname

/*you can use your sqlite3 if you want I had xampp already so*/
make sure to run as admin
C:\xampp\MercuryMail\sqlite3.exe "backup-file-from-cf.sqlite3"
>> sqlite
.output c:/backup.sql
.dump
.exit

>> wrangler
wrangler d1 execute dbname --file="c:\backup.sql"

/*update your wrangler first*/
>> wrangler update
npm install -g wrangler

>> create new db with beta engine
wrangler d1 create dbname

/*you can use your sqlite3 if you want I had xampp already so*/
make sure to run as admin
C:\xampp\MercuryMail\sqlite3.exe "backup-file-from-cf.sqlite3"
>> sqlite
.output c:/backup.sql
.dump
.exit

>> wrangler
wrangler d1 execute dbname --file="c:\backup.sql"

LLeftSpace beta engine looks scary tho you don't have dump or download backup

Bulbasaur•8/7/23, 4:54 AM

agreed, though time travel for normal usecases of restoring if there's issues is great, definitely still want a first-party way to dump and download a backup!

LeftSpace•8/7/23, 5:19 AM

imagine what will happen if you wanted to move to somewhere else

LeftSpace•8/7/23, 5:20 AM

since there's no download backup!

Dahoom•8/7/23, 10:40 AM

Perfect, it even retains the comments and indexes

Dahoom•8/7/23, 10:40 AM

Thank you

Codename_404•8/7/23, 12:34 PM

is there any maximum table limit on d1 alpha?

Original message was deleted

Matt Silverlock•8/7/23, 2:32 PM

Typically an empty table is about 4KB

samratarmas•8/7/23, 8:06 PM

Hello team, is the 10 database limit for D1 only temporary during beta? If not, is there any fundamental reason to have this limit in place?

samratarmas•8/7/23, 8:11 PM

And second question, is js transactions for d1 a planned feature that is on the pipeline or is durable objects the way to do transactions

Ssamratarmas Hello team, is the 10 database limit for D1 only temporary during beta? If not, ...

Matt Silverlock•8/7/23, 9:24 PM

Paid users will be able to create more databases once we hit beta.

Ssamratarmas And second question, is js transactions for d1 a planned feature that is on the ...

Matt Silverlock•8/7/23, 9:24 PM

Search "transactions" here in chat

Greg•8/8/23, 4:12 AM

my worker seems to start throwing an error as soon as I add the D1 config to my wrangler.toml:

[[d1_databases]]
binding = "DB"
database_name = "<DATABASE_NAME>"
database_id = "<unique-ID-for-your-database>"

[[d1_databases]]
binding = "DB"
database_name = "<DATABASE_NAME>"
database_id = "<unique-ID-for-your-database>"

It's a TypeError:

[mf:err] TypeError: head is not a function

[mf:err] TypeError: head is not a 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) }) ) }

/*update your wrangler first*/ >> wrangler update npm install -g wrangler >> create new db with beta engine wrangler d1 create dbname /*you can use your sqlite3 if you want I had xampp already so*/ make sure to run as admin C:\xampp\MercuryMail\sqlite3.exe "backup-file-from-cf.sqlite3" >> sqlite .output c:/backup.sql .dump .exit >> wrangler wrangler d1 execute dbname --file="c:\backup.sql"

Does D1 support transaction? I’m trying build a web app with next-on-pages, D1 and drizzle-orm. D

Similar Threads

Similar Threads

Similar Threads