use API

MMax (@rozenmd)If you need performance, write a Worker then talk to it over HTTP from python

M

MrXOOP•10/11/23, 2:56 AM

so we will use python -> call HTTP request to worker -> worker handle d1 db logic?

MMrXO use API

F

Fabian•10/11/23, 2:57 AM

How to select the most recent day's data from the database?
The database content is as follows:
id ip UA time
41 139.162.86.143 Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36 2023/10/7 09:40:22

Can this be done?
SELECT * FROM m_table
WHERE time >= DATE_SUB(NOW(), INTERVAL 1 DAY);

F

Fabian•10/11/23, 2:57 AM

大佬帮看看吧

M

MrXOOP•10/11/23, 2:58 AM

like this

curl --request POST \
--url https://api.cloudflare.com/client/v4/accounts/account-identifier/d1/database/database-identifier/query \
--header 'Authorization: Bearer undefined' \
--header 'Content-Type: application/json' \
--data '{
"params": [
"firstParam",
"secondParam"
],
"sql": "SELECT * FROM m_table
WHERE time >= DATE_SUB(NOW(), INTERVAL 1 DAY);"
}'

M

MrXOOP•10/11/23, 2:58 AM

change your --header 'Authorization: Bearer undefined' \

DDaniFoldi I recommend drizzle, we recently started migrating all projects to it and it wor...

P

Paolo•10/11/23, 4:50 AM

I had a look and it does not seem to have models? It seems only to create data rows rather than instantiate classes.

S

soup•10/11/23, 7:52 AM

Hello!

Does anybody know if there's a way to natively insert a new row and get the auto incremented id from the insertion?

Ssoup Hello! Does anybody know if there's a way to natively insert a new row and get ...

M

Max (@rozenmd)•10/11/23, 7:59 AM

add

RETURNING *

RETURNING *

RETURNING *

RETURNING * to the end of the query

M

Max (@rozenmd)•10/11/23, 8:00 AM

or

RETURNING id

RETURNING id

RETURNING id

RETURNING id just for the id column

A

andyt•10/11/23, 8:55 AM

I have setup my local D1 using wrangler and --local, I have inserted a row and can retrieve the row using wrangler execute, when I run my function I get

Error: D1_ERROR: no such table: company

Error: D1_ERROR: no such table: company

Error: D1_ERROR: no such table: company

Error: D1_ERROR: no such table: company, I have my D! config in my toml file and when I run my pages I pass

--d1=DB

--d1=DB

--d1=DB

--d1=DB which matches the binding name in my toml, what have I done wrong ?

M

MrXOOP•10/11/23, 9:02 AM

took 0.2s to insert a row via API, so slow

M

MrXOOP•10/11/23, 9:02 AM

maybe I need use worker

MMrXO maybe I need use worker

I

itsmatteomanf•10/11/23, 9:02 AM

This, yes.

MMrXO took 0.2s to insert a row via API, so slow

I

itsmatteomanf•10/11/23, 9:03 AM

The insert didn't take 200ms, the full API request did have a 200ms roundtrip+processing time.

M

MrXOOP•10/11/23, 9:03 AM

yes

I

itsmatteomanf•10/11/23, 9:03 AM

The time to insert I presume is returned in the metadata along with the query results (I assume, never used that API myself)

Iitsmatteomanf The time to insert I presume is returned in the metadata along with the query re...

M

MrXOOP•10/11/23, 9:04 AM

correct

M

MrXOOP•10/11/23, 9:04 AM

I did not work with worker before

M

MrXOOP•10/11/23, 9:05 AM

so basically we build worker handler d1 query - and use python call to the worker, is it right?

I

itsmatteomanf•10/11/23, 9:05 AM

Yeah, exactly

Aandyt I have setup my local D1 using wrangler and --local, I have inserted a row and c...

P

Paolo•10/11/23, 9:05 AM

did you migrate the local DB?

M

MrXOOP•10/11/23, 9:06 AM

ok lets me try

MMrXO ok lets me try

I

itsmatteomanf•10/11/23, 9:06 AM

Be careful with SQL injections...

M

MrXOOP•10/11/23, 9:06 AM

M

MrXOOP•10/11/23, 9:06 AM

sure

PPaolo did you migrate the local DB?

A

andyt•10/11/23, 9:33 AM

no, I have created the same table on both local and remote

A

andyt•10/11/23, 9:41 AM

also when I create a migration it is empty

P

Paolo•10/11/23, 9:45 AM

well, you can seed the migration if you want

P

Paolo•10/11/23, 9:45 AM

beside

Aandyt no, I have created the same table on both local and remote

P

Paolo•10/11/23, 9:46 AM

what does

wrangler d1 execute DB --local --command "select * from company"

wrangler d1 execute DB --local --command "select * from company"

wrangler d1 execute DB --local --command "select * from company"

wrangler d1 execute DB --local --command "select * from company" return?

A

andyt•10/11/23, 9:46 AM

the row i inserted

Aandyt the row i inserted

P

Paolo•10/11/23, 9:47 AM

can I have a look at the toml file? also, are you using pages or workers?

A

andyt•10/11/23, 10:04 AM

Looking at my db on the web site, it says I have two tables but clearly only have 1

Aandyt Looking at my db on the web site, it says I have two tables but clearly only hav...

P

Paolo•10/11/23, 10:24 AM

that is also a bug I found. it mysteriously vanished on its own after a while

G

GuillaumeLakano•10/11/23, 10:29 AM

Hi! With the future system of dynamic binding ( without explicit name in wrangler.toml ), will you permit us to connect only with the name of the database, or this will be only with the UUID value ?
I need to known if I need to store somewhere (KV, DO, D1...) an association table between the databases names and their UUID.

R

roftt•10/11/23, 11:50 AM

When I call D1's REST API directly like: https://api.cloudflare.com/client/v4
/accounts/{account-identifier}/d1/database/{database-identifier}/query
Do I automatically trigger a Worker call? So the pricing counts as Worker request + D1 rows read?

Rroftt When I call D1's REST API directly like: https://api.cloudflare.com/client/v4 /a...

M

Max (@rozenmd)•10/11/23, 11:52 AM

D1's REST API doesn't involve a Worker, which is why it's considerably slower

M

Max (@rozenmd)•10/11/23, 11:52 AM

you just pay for the rows read/written

D

Dudu•10/11/23, 12:02 PM

Can we not set default values for a certain column?

D

Dudu•10/11/23, 12:05 PM

I have this

status text NOT NULL DEFAULT 'active'

status text NOT NULL DEFAULT 'active'

status text NOT NULL DEFAULT 'active'

status text NOT NULL DEFAULT 'active' assuming that the status would default to active but it's not working, I could swear this worked in the past but I'm having issues right now

DDudu I have this `status text NOT NULL DEFAULT 'active'` assuming that the status wou...

M

Max (@rozenmd)•10/11/23, 12:06 PM

you have to insert null for DEFAULT to work

M

Max (@rozenmd)•10/11/23, 12:06 PM

(i think)

MMax (@rozenmd)you have to insert null for DEFAULT to work

D

Dudu•10/11/23, 12:08 PM

isn't it what this does on the UI?

D

Dudu•10/11/23, 12:09 PM

I mean the issues I'm having is with the CF ui, didn't try yet on my worker, let me check

D

Dudu•10/11/23, 12:13 PM

Yep it's just UI, works on console

MMax (@rozenmd)D1's REST API doesn't involve a Worker, which is why it's considerably slower

R

roftt•10/11/23, 12:14 PM

Got it, thanks! And if I want rate limiting functionality for my D1 database, I have to use a Worker, correct?

Rroftt Got it, thanks! And if I want rate limiting functionality for my D1 database, I ...

M

Max (@rozenmd)•10/11/23, 12:16 PM

Yeah

DDudu I mean the issues I'm having is with the CF ui, didn't try yet on my worker, let...

M

Max (@rozenmd)•10/11/23, 12:16 PM

do you have a quick create table statement i can run to repro the issue?

D

Dudu•10/11/23, 12:19 PM

oh wait

D

Dudu•10/11/23, 12:19 PM

CREATE TABLE IF NOT EXISTS licenses (
  id integer PRIMARY KEY AUTOINCREMENT,
  status text NOT NULL DEFAULT 'active',
  plan text NOT NULL DEFAULT 'basic',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS licenses (
  id integer PRIMARY KEY AUTOINCREMENT,
  status text NOT NULL DEFAULT 'active',
  plan text NOT NULL DEFAULT 'basic',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS licenses (
  id integer PRIMARY KEY AUTOINCREMENT,
  status text NOT NULL DEFAULT 'active',
  plan text NOT NULL DEFAULT 'basic',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS licenses (
  id integer PRIMARY KEY AUTOINCREMENT,
  status text NOT NULL DEFAULT 'active',
  plan text NOT NULL DEFAULT 'basic',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Similar Threads

Similar Threads

Similar Threads