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

Kkrsr they don't seem to be the same database

H

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

What does Pages Dev output?

H

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

For the bindings section

Kkrsr the message I just wrote? or do you mean something else

H

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

Is the one you just posted from the Worker, or Pages?

Kkrsr that's part of the output from: `wrangler pages dev .svelte-kit/cloudflare --d1 ...

H

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

OK, I’m currently walking but if you still have issues in 20 minutes or so, I’ll see if I can help you further

Kkrsr so, my database in `wrangler.toml` is called `app`. the binding is named `DB`. w...

D

Denver James Duran•2/12/24, 7:41 AM

can you try

npx wrangler d1 execute app --local --file="./db/my_file.sql"

npx wrangler d1 execute app --local --file="./db/my_file.sql"

npx wrangler d1 execute app --local --file="./db/my_file.sql"

npx wrangler d1 execute app --local --file="./db/my_file.sql"
or it doesn't work even with the ' " ' quotation marks.. also, did you put your queries on "./db/my_file.sql"? also how did you setup the query..

Kkrsr No rush - I’m PST and should probably be asleep 😅 I’ll check back in the mornin...

H

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

Here's how I got it working:

# Create Table
bun wrangler d1 execute -j --local --command "CREATE TABLE demo2 ( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), hint TEXT ); INSERT INTO demo2 (name, hint) VALUES ('hey', 'hi')" app
# Run Pages
bun wrangler pages dev . --d1 DB=1476805e-7b64-4922-90c5-003d06f3bd36

# Create Table
bun wrangler d1 execute -j --local --command "CREATE TABLE demo2 ( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), hint TEXT ); INSERT INTO demo2 (name, hint) VALUES ('hey', 'hi')" app
# Run Pages
bun wrangler pages dev . --d1 DB=1476805e-7b64-4922-90c5-003d06f3bd36

# Create Table
bun wrangler d1 execute -j --local --command "CREATE TABLE demo2 ( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), hint TEXT ); INSERT INTO demo2 (name, hint) VALUES ('hey', 'hi')" app
# Run Pages
bun wrangler pages dev . --d1 DB=1476805e-7b64-4922-90c5-003d06f3bd36

# Create Table
bun wrangler d1 execute -j --local --command "CREATE TABLE demo2 ( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), hint TEXT ); INSERT INTO demo2 (name, hint) VALUES ('hey', 'hi')" app
# Run Pages
bun wrangler pages dev . --d1 DB=1476805e-7b64-4922-90c5-003d06f3bd36

wrangler.json148B

V

Vy•2/12/24, 6:47 PM

https://github.com/cloudflare/cloudflare-docs/pull/12940 FYI D1 team will enable billing on March 4, 2024. Developers on the Workers Free plan and D1's Free plan will not be charged. Only usage on and after March 4th will incur charges according to D1's pricing - https://developers.cloudflare.com/d1/platform/pricing.

GitHub

[D1] Add D1 billing date by vy-ton · Pull Request #12940 · cloudfla...

M

Max (@rozenmd)•2/12/24, 7:28 PM

@D1 Database :this: