Is there a way to insert many data during a worker run. I'm thinking about 2k-3k of rows. I only kno

Y

YanniOP•2/10/24, 3:33 PM

There is currently a limit of just 1000 queries which is like nothing

Y

YanniOP•2/10/24, 3:33 PM

the job should detect increments which means that It hast to first query by id and the insert new datasets

Y

YanniOP•2/10/24, 3:34 PM

and this ~2'000 - 3'000 times. I would also be fine by deleting everything first and then inserting it which would work if there is a option to insert multiple rows at the same time

something like this

INSERT INTO db VALUES (1), (2)

INSERT INTO db VALUES (1), (2)

INSERT INTO db VALUES (1), (2)

INSERT INTO db VALUES (1), (2)

Y

YanniOP•2/10/24, 3:37 PM

this job should only run all 24h or maybe only every week the data doesn't really change over time but I'm to lazy to do it manually via cli or a local python script.

YYanni Is there a way to insert many data during a worker run. I'm thinking about 2k-3k...

M

Max (@rozenmd)•2/10/24, 7:23 PM

db.batch

T

Tonic•2/10/24, 9:09 PM

A

aunyks•2/11/24, 3:08 AM

Hey y’all! Has the ability to export a D1 DB to a sqlite3 file or

.sql

.sql

.sql

.sql file been added yet? The ability to back up / download the DB locally, ideally from the Dashboard, is what’s keeping me from using this over Turso / sqld

Aaunyks Hey y’all! Has the ability to export a D1 DB to a sqlite3 file or `.sql` file be...

C

Cyb3r-Jak3•2/11/24, 3:14 AM

It has not been added yet. Still need to use community tools

G

GuillaumeLakano•2/11/24, 9:45 AM

Hi! I got this error while I'm trying to execute an import of a SQL file, is it an important problem ? My content seems to be correctly imported

npx wrangler --persist-to ../../../.wrangler d1 execute content --local --file sql/content.articles.sql
wrangler 3.28.1
-------------------
Mapping SQL input into an array of statements
Executing on local database content (00000000-0000-0000-0007-000000000000) from ../../../.wrangler/v3/d1:
workerd/jsg/setup.c++:493: error: CODE_MOVED for unknown code block?
workerd/jsg/setup.c++:493: error: CODE_MOVED for unknown code block?

D

Denver James Duran•2/11/24, 5:10 PM

Hi! Can I ask for assistance.. so I am using D1 as my database I am required to store dates, so what I did was to first convert the date into numbers with this function

const MS_PER_DAY = 24 * 60 * 60 * 1000;
const EPOCH_1900 = new Date('1899-12-30T00:00:00.000Z').getTime();
export function dateToExcelNumberWithTime(date: Date): number {
    const gmtOffset = date.getTimezoneOffset();
    const adjustment = (0 - gmtOffset) * 60 * 1000;
    const finalDate = (date.getTime() - EPOCH_1900 + adjustment) / MS_PER_DAY;

    return finalDate;
}

const MS_PER_DAY = 24 * 60 * 60 * 1000;
const EPOCH_1900 = new Date('1899-12-30T00:00:00.000Z').getTime();
export function dateToExcelNumberWithTime(date: Date): number {
    const gmtOffset = date.getTimezoneOffset();
    const adjustment = (0 - gmtOffset) * 60 * 1000;
    const finalDate = (date.getTime() - EPOCH_1900 + adjustment) / MS_PER_DAY;

    return finalDate;
}

const MS_PER_DAY = 24 * 60 * 60 * 1000;
const EPOCH_1900 = new Date('1899-12-30T00:00:00.000Z').getTime();
export function dateToExcelNumberWithTime(date: Date): number {
    const gmtOffset = date.getTimezoneOffset();
    const adjustment = (0 - gmtOffset) * 60 * 1000;
    const finalDate = (date.getTime() - EPOCH_1900 + adjustment) / MS_PER_DAY;

    return finalDate;
}

const MS_PER_DAY = 24 * 60 * 60 * 1000;
const EPOCH_1900 = new Date('1899-12-30T00:00:00.000Z').getTime();
export function dateToExcelNumberWithTime(date: Date): number {
    const gmtOffset = date.getTimezoneOffset();
    const adjustment = (0 - gmtOffset) * 60 * 1000;
    const finalDate = (date.getTime() - EPOCH_1900 + adjustment) / MS_PER_DAY;

    return finalDate;
}

the thing is, no matter what I try, I really find it hard to synchronize time.. it just wont behave as what I am expecting.. my question is, can D1 support the actual DATE/ Timestamp to be saved on rows instead of me converting it to number then converting it back to date I think it complicates things (do you have any pointers on this or any tips?)

Original message was deleted

D

Denver James Duran•2/11/24, 5:50 PM

what do you mean?

Original message was deleted

D

Denver James Duran•2/11/24, 5:52 PM

So that I can have something like this

45332.058900462966

45332.058900462966

45332.058900462966

45332.058900462966 which I thought was easier to query. the numeric part is the number of days while the decimal is the time.. if I am querying a BETWEEN statement, the process will be straightforward..

Original message was deleted

D

Denver James Duran•2/11/24, 8:02 PM

OMG I am so dumb I just realized, what you meant, the getTime() already gives me a reference, I didn't need to create my own epoch etc, it's already a timestamp thanks! took me so long

K

Kaique Nunes•2/11/24, 9:13 PM

Will Cloudflare still charging us before the write bug has been fixed ? I received an email informing the charge will start on Feb 12th

KKaique Nunes Will Cloudflare still charging us before the write bug has been fixed ? I receiv...

M

Max (@rozenmd)•2/11/24, 9:38 PM

No, you will not be charged

MMax (@rozenmd)No, you will not be charged

Ɽ

ⱤØ₥₳₦•2/12/24, 1:18 AM

Btw how to cache worker's response that is using D1? In previous messages you said I've got to use fetch api?

ⱤⱤØ₥₳₦Btw how to cache worker's response that is using D1? In previous messages you sa...

C

Cyb3r-Jak3•2/12/24, 1:35 AM

You'd just use the cache api same as caching anything else

CCyb3r-Jak3 You'd just use the cache api same as caching anything else

Ɽ

ⱤØ₥₳₦•2/12/24, 1:41 AM

But cache api for what? Fetch(url) is okay to use cache but this is d1

ⱤⱤØ₥₳₦But cache api for what? Fetch(url) is okay to use cache but this is d1 🫠

I

Isaac McFadyen•2/12/24, 2:09 AM

https://developers.cloudflare.com/workers/runtime-apis/cache/

I

Isaac McFadyen•2/12/24, 2:09 AM

You'd put the response from D1 into a Response() and then cache that along with the request.

I

Isaac McFadyen•2/12/24, 2:10 AM

You can construct the request and response used in the Cache API yourself so you have full control over it.

Ɽ

ⱤØ₥₳₦•2/12/24, 2:49 AM

Is it possible to sync 2 D1 database that one is in Singapore and one in new york without Wrangler

Original message was deleted

C

Cyb3r-Jak3•2/12/24, 2:57 AM

Please don't ping employees

ⱤⱤØ₥₳₦Is it possible to sync 2 D1 database that one is in Singapore and one in new yor...

C

Cyb3r-Jak3•2/12/24, 3:00 AM

My guess is not. You have to do the sync with the API

Nnoman has anyone been able to create an FTS5 virtual table in D1? searching this chann...

C

Cyb3r-Jak3•2/12/24, 3:02 AM

What is the command?

D

Denver James Duran•2/12/24, 6:35 AM

Hi is there a bug right now?
even after successful login, I am getting this:

not authorized [code: 7500]

not authorized [code: 7500]

not authorized [code: 7500]

not authorized [code: 7500] on executing sql scripts

Kkrsr Using `wrangler pages dev --d1=DB` seems to create a new database called `local-...

H

Hello, I’m Allie!•2/12/24, 6:37 AM

Try doing

wrangler pages dev —d1 DB=ID

wrangler pages dev —d1 DB=ID

wrangler pages dev —d1 DB=ID

wrangler pages dev —d1 DB=ID

DDenver James Duran Hi is there a bug right now? even after successful login, I am getting this: `n...

D

Denver James Duran•2/12/24, 6:39 AM

here's my code:

I stumbled upon this blog post which is relevant to my case as well I think but there's no resolution so far...

same with this github issue

Basically what I was trying to do is to add a new column to my existing table.. Am I doing something wrong?

-- Disable foreign key enforcement
PRAGMA foreign_keys = OFF;

-- Create a temporary table to hold the existing data
CREATE TEMPORARY TABLE temp_stocks AS SELECT * FROM stocks;

-- Drop the existing stocks table
DROP TABLE stocks;

-- Create a new stocks table with the additional uniqueStockId column
CREATE TABLE stocks (
    id INTEGER PRIMARY KEY,
    conversionFactor REAL NOT NULL,
    createdBy INTEGER NOT NULL,
    createdAt REAL NOT NULL,
    updatedBy INTEGER,
    updatedAt REAL,
    branch INTEGER NOT NULL,
    product INTEGER NOT NULL,
    stock INTEGER NOT NULL,
    uniqueStockId TEXT UNIQUE, -- New column with UNIQUE constraint
    FOREIGN KEY (branch) REFERENCES branches (id),
    FOREIGN KEY (product) REFERENCES products (id),
    FOREIGN KEY (createdBy) REFERENCES users (id),
    FOREIGN KEY (updatedBy) REFERENCES users (id)
);

-- Populate the new stocks table with data from the temporary table, computing the uniqueStockId
INSERT INTO stocks (id, conversionFactor, createdBy, createdAt, updatedBy, updatedAt, branch, product, stock, uniqueStockId)
SELECT id, conversionFactor, createdBy, createdAt, updatedBy, updatedAt, branch, product, stock, CAST(branch || '-' || product AS TEXT) AS uniqueStockId
FROM temp_stocks;

PRAGMA foreign_keys = ON;

-- Disable foreign key enforcement
PRAGMA foreign_keys = OFF;

-- Create a temporary table to hold the existing data
CREATE TEMPORARY TABLE temp_stocks AS SELECT * FROM stocks;

-- Drop the existing stocks table
DROP TABLE stocks;

-- Create a new stocks table with the additional uniqueStockId column
CREATE TABLE stocks (
    id INTEGER PRIMARY KEY,
    conversionFactor REAL NOT NULL,
    createdBy INTEGER NOT NULL,
    createdAt REAL NOT NULL,
    updatedBy INTEGER,
    updatedAt REAL,
    branch INTEGER NOT NULL,
    product INTEGER NOT NULL,
    stock INTEGER NOT NULL,
    uniqueStockId TEXT UNIQUE, -- New column with UNIQUE constraint
    FOREIGN KEY (branch) REFERENCES branches (id),
    FOREIGN KEY (product) REFERENCES products (id),
    FOREIGN KEY (createdBy) REFERENCES users (id),
    FOREIGN KEY (updatedBy) REFERENCES users (id)
);

-- Populate the new stocks table with data from the temporary table, computing the uniqueStockId
INSERT INTO stocks (id, conversionFactor, createdBy, createdAt, updatedBy, updatedAt, branch, product, stock, uniqueStockId)
SELECT id, conversionFactor, createdBy, createdAt, updatedBy, updatedAt, branch, product, stock, CAST(branch || '-' || product AS TEXT) AS uniqueStockId
FROM temp_stocks;

PRAGMA foreign_keys = ON;

-- Disable foreign key enforcement
PRAGMA foreign_keys = OFF;

-- Create a temporary table to hold the existing data
CREATE TEMPORARY TABLE temp_stocks AS SELECT * FROM stocks;

-- Drop the existing stocks table
DROP TABLE stocks;

-- Create a new stocks table with the additional uniqueStockId column
CREATE TABLE stocks (
    id INTEGER PRIMARY KEY,
    conversionFactor REAL NOT NULL,
    createdBy INTEGER NOT NULL,
    createdAt REAL NOT NULL,
    updatedBy INTEGER,
    updatedAt REAL,
    branch INTEGER NOT NULL,
    product INTEGER NOT NULL,
    stock INTEGER NOT NULL,
    uniqueStockId TEXT UNIQUE, -- New column with UNIQUE constraint
    FOREIGN KEY (branch) REFERENCES branches (id),
    FOREIGN KEY (product) REFERENCES products (id),
    FOREIGN KEY (createdBy) REFERENCES users (id),
    FOREIGN KEY (updatedBy) REFERENCES users (id)
);

-- Populate the new stocks table with data from the temporary table, computing the uniqueStockId
INSERT INTO stocks (id, conversionFactor, createdBy, createdAt, updatedBy, updatedAt, branch, product, stock, uniqueStockId)
SELECT id, conversionFactor, createdBy, createdAt, updatedBy, updatedAt, branch, product, stock, CAST(branch || '-' || product AS TEXT) AS uniqueStockId
FROM temp_stocks;

PRAGMA foreign_keys = ON;

-- Disable foreign key enforcement
PRAGMA foreign_keys = OFF;

-- Create a temporary table to hold the existing data
CREATE TEMPORARY TABLE temp_stocks AS SELECT * FROM stocks;

-- Drop the existing stocks table
DROP TABLE stocks;

-- Create a new stocks table with the additional uniqueStockId column
CREATE TABLE stocks (
    id INTEGER PRIMARY KEY,
    conversionFactor REAL NOT NULL,
    createdBy INTEGER NOT NULL,
    createdAt REAL NOT NULL,
    updatedBy INTEGER,
    updatedAt REAL,
    branch INTEGER NOT NULL,
    product INTEGER NOT NULL,
    stock INTEGER NOT NULL,
    uniqueStockId TEXT UNIQUE, -- New column with UNIQUE constraint
    FOREIGN KEY (branch) REFERENCES branches (id),
    FOREIGN KEY (product) REFERENCES products (id),
    FOREIGN KEY (createdBy) REFERENCES users (id),
    FOREIGN KEY (updatedBy) REFERENCES users (id)
);

-- Populate the new stocks table with data from the temporary table, computing the uniqueStockId
INSERT INTO stocks (id, conversionFactor, createdBy, createdAt, updatedBy, updatedAt, branch, product, stock, uniqueStockId)
SELECT id, conversionFactor, createdBy, createdAt, updatedBy, updatedAt, branch, product, stock, CAST(branch || '-' || product AS TEXT) AS uniqueStockId
FROM temp_stocks;

PRAGMA foreign_keys = ON;

GitHub

🐛 BUG: unable to create temp view or temp table · Issue #4298 · clo...

Which Cloudflare product(s) does this pertain to? D1 What version(s) of the tool(s) are you using? 2.6.2 What version of Node are you using? v16.18.1 What operating system are you using? Linux Desc...

DDenver James Duran here's my code: I stumbled upon this [blog post](https://community.cloudflare.c...

D

Denver James Duran•2/12/24, 6:44 AM

Nevermind team, I found a workaround, so apparently, what causes the "not authorized" issue is that I am using TEMPORARY in creating the table, what I did to fix the problem was remove the TEMPORARY keyword then just DROPped the temp_stocks afterwards. this is fixed.. just a reference for anyone who might encounter it and for devs to find a fix for it if it isn't intended to behave that way..

Kkrsr Is it possible to execute migrations against the local db created by `wrangler p...

D

Denver James Duran•2/12/24, 6:55 AM

I just did this,

my script looks like this in case that you might wanna see

npx wrangler d1 execute myD1DatabaseName --local --file="./src/lib/server/d1/migrations/stocks.sql"

npx wrangler d1 execute myD1DatabaseName --local --file="./src/lib/server/d1/migrations/stocks.sql"

npx wrangler d1 execute myD1DatabaseName --local --file="./src/lib/server/d1/migrations/stocks.sql"

npx wrangler d1 execute myD1DatabaseName --local --file="./src/lib/server/d1/migrations/stocks.sql"

The

myD1DatabaseName

myD1DatabaseName

myD1DatabaseName

myD1DatabaseName is the

database_name

database_name

database_name

database_name in

wrangler.toml

wrangler.toml

I don't know if this answers your inquiry...

Kkrsr wrangler.toml: ``` name = "app" compatibility_date = "2024-02-12" node_compat = ...

H

Hello, I’m Allie!•2/12/24, 7:12 AM

Use the database_id field

H

Hello, I’m Allie!•2/12/24, 7:12 AM

On the Pages Dev command

H

Hello, I’m Allie!•2/12/24, 7:13 AM

wrangler pages dev —d1 DB=1476805e-7b64-4922-90c5-003d06f3bd36

wrangler pages dev —d1 DB=1476805e-7b64-4922-90c5-003d06f3bd36

wrangler pages dev —d1 DB=1476805e-7b64-4922-90c5-003d06f3bd36

wrangler pages dev —d1 DB=1476805e-7b64-4922-90c5-003d06f3bd36

Kkrsr but that’s my production database id

H

Hello, I’m Allie!•2/12/24, 7:16 AM

Yeah, but Pages Dev can’t interact with production DBs anyway

H

Hello, I’m Allie!•2/12/24, 7:16 AM

It will always use a local version

H

Hello, I’m Allie!•2/12/24, 7:16 AM

So theoretically you could give it any ID you want

H

Hello, I’m Allie!•2/12/24, 7:16 AM

As long as it is set correctly in the dashboard

Is there a way to insert many data during a worker run. I'm thinking about 2k-3k of rows. I only kno

Similar Threads

Similar Threads

Similar Threads