Cloudflare Developers•3y ago

https://gist.github.com/elithrar/73e7340046a397ce427d45c037347d1c - this works as-is.

MMatt Silverlock https://gist.github.com/elithrar/73e7340046a397ce427d45c037347d1c - this works a...

captain•9/25/23, 5:50 PM

do you have example from .js code and not .ts

captain•9/25/23, 5:50 PM

i notice in other places of documentation it shows a toggle betweent the two

captain•9/25/23, 5:50 PM

and do u test with .js

MMatt Silverlock https://gist.github.com/elithrar/73e7340046a397ce427d45c037347d1c - this works a...

captain•9/25/23, 5:52 PM

with my js code the way I am binding things is different for the differnt env scenarios and I think its worth using that as an example as I think its more common

Ccaptain with my js code the way I am binding things is different for the differnt env sc...

Matt SilverlockOP•9/25/23, 5:55 PM

Share your full Worker. Are you sure you're naming the variables correctly? Are you using the same Worker format as I've shown?

Ccaptain with my js code the way I am binding things is different for the differnt env sc...

Matt SilverlockOP•9/25/23, 5:56 PM

export default {
    async fetch(request, env, ctx) {
        let res = await env.MYDATABASE.exec("PRAGMA table_list")
        console.log(res)
        return Response.json({res: res})
    },
};

export default {
    async fetch(request, env, ctx) {
        let res = await env.MYDATABASE.exec("PRAGMA table_list")
        console.log(res)
        return Response.json({res: res})
    },
};

(TypeScript is transpiled into JavaScript before it is deployed, so there should be no difference)

EvanPiro•9/25/23, 8:04 PM

How do I get access to the D1 transactions beta? I have an immediate need for it and users who are willing to deal with turbulence.

EEvanPiro How do I get access to the D1 transactions beta? I have an immediate need for it...

Matt SilverlockOP•9/25/23, 8:06 PM

There is not a transactions beta. What are you trying to do / how do you want to use transactions?

MMatt Silverlock There is not a transactions beta. What are you trying to do / how do you want to...

EvanPiro•9/25/23, 8:07 PM

I have a whole bunch of database updates attached to a workers call that need to run atomically.

EEvanPiro I have a whole bunch of database updates attached to a workers call that need to...

Matt SilverlockOP•9/25/23, 8:07 PM

Today the approach for that (if it fits your use-case) is db.batchdb.batch (or UPSERTUPSERT / ON CONFLICTON CONFLICT) - there is not a way to ship a transaction to D1 outside of that.

MMatt Silverlock Today the approach for that (if it fits your use-case) is `db.batch` (or `UPSER...

EvanPiro•9/25/23, 8:09 PM

What about D1 procedures? or is this article too outdated?

https://blog.cloudflare.com/whats-new-with-d1/

The Cloudflare Blog

D1: our quest to simplify databases

Get an inside look on the D1 experience today, what the team is currently working on and what’s coming up!

EEvanPiro What about D1 procedures? or is this article too outdated? https://blog.cloudfl...

Matt SilverlockOP•9/25/23, 8:14 PM

Lots of core stuff to build before we get there. We're prioritizing read replication + performance + stability first.

MMatt Silverlock Lots of core stuff to build before we get there. We're prioritizing read replica...

EvanPiro•9/25/23, 8:14 PM

Makes sense. I'll look into db.batchdb.batch for now.

captain•9/26/23, 4:14 PM

question regarding setting up a database which creates the best space savings for a field when nothing is set:
a. email TEXT
b. email TEXT DEFAULT ''

captain•9/26/23, 4:17 PM

so if nothing is set in the email in part a, the field would be filled with "NULL" just wondering if behind the scenes that takes up more space than choice b

digeratidave•9/26/23, 4:19 PM

what is the best practice for adding a column to an existing D1? Is the Alter command from wrangler the only method currently supported or are there any other methods such a Console from the Dashboard?

captain•9/26/23, 4:45 PM

@Matt just wondering if you had any incite on the "under the hood" storage of how D1 saves things, is there more saving a field as "null" or 0 character string?

Unsmart•9/26/23, 4:49 PM

I dont think you should be that worried about saving a few bytes an item like that tbh just do whatever you think best for your application that makes it easy for you to manage

UUnsmart I dont think you should be that worried about saving a few bytes an item like th...

captain•9/26/23, 4:49 PM

the OCD in me does worry...so would be good to know!

kian•9/26/23, 4:49 PM

https://www.sqlite.org/fileformat2.html#record_format

Kkian https://www.sqlite.org/fileformat2.html#record_format

captain•9/26/23, 4:50 PM

are you sure the way Standard SQL lite does thing behind the scenes i the same as how D1 does it? I don't think its always a 1 to 1 situation...only the cloudflare wizards would know what lies beyond the veil

Ccaptain are you sure the way Standard SQL lite does thing behind the scenes i the same a...

Max (@rozenmd)•9/26/23, 4:52 PM

it's just sqlite https://github.com/cloudflare/workerd/pull/302/files#diff-3009a3da17d76a94673aa828ce6c364fc24e34d889b4995adfc249a0e2a3c754R66-R69

MMax (@rozenmd)it's just sqlite https://github.com/cloudflare/workerd/pull/302/files#diff-3009a...

captain•9/26/23, 5:02 PM

so that means...its basically the same size?

captain•9/26/23, 5:04 PM

https://www.sqlite.org/fileformat2.html#record_format or i mean a someting wit a content length of 0 is Null?

captain•9/26/23, 5:45 PM

Another question regarding fields, i have a field set to:

time_stamp_updated INTEGER DEFAULT CURRENT_TIMESTAMP,

If I make an entry and don't set this field, it sets it to:

2023-09-26 17:40:44

I was thinking it would set it to to the epoch time similar to Date.now() which would be an integer like: 1695750044698

When going to dashboard and then attempting to update, an update can't occur because the dashboard flags the entry at that time_stamp_updated area.

That said, it still saved "2023-09-26 17:40:44" which is not an integer...how did it do that?

captain•9/26/23, 5:53 PM

Also noticing workers sets Date.now() to something like 1695750600382 while D1 sets unixepoch('now') to 1695750600

Ccaptain Also noticing workers sets Date.now() to something like 1695750600382 while D1 s...

Isaac McFadyen•9/26/23, 5:53 PM

Date.now() is in ms, SQLite is in secs.

captain•9/26/23, 5:53 PM

wondering if that would make sense if they were more aligned to both either be in ms or secs?

Ccaptain wondering if that would make sense if they were more aligned to both either be i...

Isaac McFadyen•9/26/23, 5:53 PM

It might, but that would deviate from SQLite behavior, which I doubt they'd want to do.

Isaac McFadyen•9/26/23, 5:54 PM

It would mean every time someone imported a SQLite database the user would need to know to convert manually.

captain•9/26/23, 5:54 PM

true

captain•9/26/23, 5:54 PM

perhaps the D1 wizzards could deviate slightly and created a new unixepoch function ie, unixepochD1

captain•9/26/23, 5:55 PM

that would use ms, given the environment its used in

captain•9/26/23, 6:00 PM

idk maybe its a silly idea

captain•9/26/23, 6:00 PM

i am going to set time_stamp_updated INTEGER DEFAULT (unixepoch('now') * 1000) to aleviate confusion

Ccaptain i am going to set time_stamp_updated INTEGER DEFAULT (unixepoch('now') * 1000) t...

Matt SilverlockOP•9/26/23, 6:30 PM

timestamp_updated_mstimestamp_updated_ms - give your names units

MMatt Silverlock `timestamp_updated_ms` - give your names units 🙂

captain•9/26/23, 6:37 PM

how would one define that...? I tried time_stamp_updated INTEGER DEFAULT (timestamp_updated_ms('now')) AND time_stamp_updated INTEGER DEFAULT timestamp_updated_ms

captain•9/26/23, 6:38 PM

but no success

captain•9/26/23, 6:43 PM

or are you just saying label it with the expected units?

Matt SilverlockOP•9/26/23, 6:43 PM

^ that. Give your column the name of the units it contains. Not a requirement - just a tip

captain•9/26/23, 6:44 PM

got it! I just set it via: time_stamp_updated INTEGER DEFAULT (unixepoch('now') * 1000)

captain•9/26/23, 6:44 PM

to normalize things which works for mee

captain•9/26/23, 6:45 PM

but that said, if setting time_stamp_updated INTEGER DEFAULT CURRENT_TIMESTAMP, i think wrangle should flag that as an error

captain•9/26/23, 6:45 PM

with type mismatch of some sort given CURRENT_TIMESTAMP will save a non integer result
wrangler could check for that when the following line is executed:
wrangler d1 execute <DB_NAME> --file=dbtablecreate.sql

captain•9/26/23, 6:48 PM

wrangler could check for that when the following line is executed:
wrangler d1 execute <DB_NAME> --file=dbtablecreate.sql

captain•9/26/23, 6:53 PM

https://developers.cloudflare.com/vectorize/ seeing this in the realease notes (3.10)...but its a dead link

https://gist.github.com/elithrar/73e7340046a397ce427d45c037347d1c - this works as-is.

Similar Threads