Hi is there a bug right now? even after successful login, I am getting this: `not authorized [code:

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

H

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 DuranOP•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 DuranOP•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 DuranOP•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"

The

myD1DatabaseName

myD1DatabaseName

is the

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

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 DuranOP•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"

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:

B

bun•2/12/24, 7:32 PM

any updates on d1 database itself?

Original message was deleted

V

Vy•2/12/24, 7:43 PM

No, D1 GA is planned for early April.

VVy No, D1 GA is planned for early April.

A

Asher•2/12/24, 7:51 PM

Any plans for a document database similar to mongodb?

AAsher Any plans for a document database similar to mongodb?

V

Vy•2/12/24, 7:53 PM

No plans atm but would like to hear what document database features you care most about (or even what added functionality could make R2 work for your use case)?

W

Who Knows•2/12/24, 10:06 PM

¯\_(ツ)_/¯

Original message was deleted

M

Max (@rozenmd)•2/12/24, 10:07 PM

i think the DB might be in CDG, France

VVy No plans atm but would like to hear what document database features you care mos...

D

down•2/12/24, 10:12 PM

I would be interested in a full text search solution like sphinx search or elastic search, are there any plans for this?

T

typesafeui•2/12/24, 10:18 PM

is there a way to do

env.DB.prepare('select * from table where id in (?)').bind([1,2,3]).all()

env.DB.prepare('select * from table where id in (?)').bind([1,2,3]).all()

env.DB.prepare('select * from table where id in (?)').bind([1,2,3]).all()

env.DB.prepare('select * from table where id in (?)').bind([1,2,3]).all()

NOTE: the above code does not work

T

typesafeui•2/12/24, 10:19 PM

because the two alternatives I see is querying the whole db and filtering with js or querying for each one

U

Unsmart•2/12/24, 10:20 PM

const ids = [1,2,3]

env.DB.prepare(`select * from table where id in (${ids.map(() => '?').join(', ')})`).bind(ids).all()

const ids = [1,2,3]

env.DB.prepare(`select * from table where id in (${ids.map(() => '?').join(', ')})`).bind(ids).all()

const ids = [1,2,3]

env.DB.prepare(`select * from table where id in (${ids.map(() => '?').join(', ')})`).bind(ids).all()

const ids = [1,2,3]

env.DB.prepare(`select * from table where id in (${ids.map(() => '?').join(', ')})`).bind(ids).all()

U

Unsmart•2/12/24, 10:20 PM

something like that basically

Hi is there a bug right now? even after successful login, I am getting this: `not authorized [code:

Similar Threads