What is your schema? What is the query? Types?

MMatt Silverlock What is your schema? What is the query? Types?

E

Query:

await env.DB.prepare("SELECT * FROM products WHERE amazonAsin = ?").bind(amazonAsin).first()

await env.DB.prepare("SELECT * FROM products WHERE amazonAsin = ?").bind(amazonAsin).first()

await env.DB.prepare("SELECT * FROM products WHERE amazonAsin = ?").bind(amazonAsin).first()

await env.DB.prepare("SELECT * FROM products WHERE amazonAsin = ?").bind(amazonAsin).first() ... It is not typed. My schema is like the following:

CREATE TABLE publishers
(
    `id`           INTEGER PRIMARY KEY,
    `name`         TEXT     NOT NULL,
    `dateRecorded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `isDifficult`  BOOLEAN  NOT NULL DEFAULT 0
);
CREATE INDEX idx_publishers_name ON publishers (name);
CREATE TABLE products
(
    `id`                      INTEGER PRIMARY KEY,
    `name`                    TEXT    NOT NULL,
    `publisherId`             INTEGER NOT NULL,
    `amazonAsin`              TEXT UNIQUE,
    `dateSeen`                TEXT    NOT NULL,
    `publisherApprovalStatus` TEXT    NOT NULL DEFAULT 'pending',
    `sellable`                BOOLEAN NOT NULL DEFAULT 0,
    `lastSalesRank`           INTEGER,
    `lastSalesRankDate`       DATETIME, bookPublishingDate DATETIME,
    FOREIGN KEY (publisherId) REFERENCES publishers (id) ON DELETE CASCADE
);
CREATE INDEX idx_products_publisherId ON products (publisherId);
CREATE INDEX idx_products_amazonAsin ON products (amazonAsin);
CREATE INDEX idx_products_publisherApprovalStatus ON products (publisherApprovalStatus);
CREATE INDEX idx_products_sellable ON products (sellable);
CREATE INDEX idx_products_lastSalesRank ON products (lastSalesRank);

CREATE TABLE publishers
(
    `id`           INTEGER PRIMARY KEY,
    `name`         TEXT     NOT NULL,
    `dateRecorded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `isDifficult`  BOOLEAN  NOT NULL DEFAULT 0
);
CREATE INDEX idx_publishers_name ON publishers (name);
CREATE TABLE products
(
    `id`                      INTEGER PRIMARY KEY,
    `name`                    TEXT    NOT NULL,
    `publisherId`             INTEGER NOT NULL,
    `amazonAsin`              TEXT UNIQUE,
    `dateSeen`                TEXT    NOT NULL,
    `publisherApprovalStatus` TEXT    NOT NULL DEFAULT 'pending',
    `sellable`                BOOLEAN NOT NULL DEFAULT 0,
    `lastSalesRank`           INTEGER,
    `lastSalesRankDate`       DATETIME, bookPublishingDate DATETIME,
    FOREIGN KEY (publisherId) REFERENCES publishers (id) ON DELETE CASCADE
);
CREATE INDEX idx_products_publisherId ON products (publisherId);
CREATE INDEX idx_products_amazonAsin ON products (amazonAsin);
CREATE INDEX idx_products_publisherApprovalStatus ON products (publisherApprovalStatus);
CREATE INDEX idx_products_sellable ON products (sellable);
CREATE INDEX idx_products_lastSalesRank ON products (lastSalesRank);

CREATE TABLE publishers
(
    `id`           INTEGER PRIMARY KEY,
    `name`         TEXT     NOT NULL,
    `dateRecorded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `isDifficult`  BOOLEAN  NOT NULL DEFAULT 0
);
CREATE INDEX idx_publishers_name ON publishers (name);
CREATE TABLE products
(
    `id`                      INTEGER PRIMARY KEY,
    `name`                    TEXT    NOT NULL,
    `publisherId`             INTEGER NOT NULL,
    `amazonAsin`              TEXT UNIQUE,
    `dateSeen`                TEXT    NOT NULL,
    `publisherApprovalStatus` TEXT    NOT NULL DEFAULT 'pending',
    `sellable`                BOOLEAN NOT NULL DEFAULT 0,
    `lastSalesRank`           INTEGER,
    `lastSalesRankDate`       DATETIME, bookPublishingDate DATETIME,
    FOREIGN KEY (publisherId) REFERENCES publishers (id) ON DELETE CASCADE
);
CREATE INDEX idx_products_publisherId ON products (publisherId);
CREATE INDEX idx_products_amazonAsin ON products (amazonAsin);
CREATE INDEX idx_products_publisherApprovalStatus ON products (publisherApprovalStatus);
CREATE INDEX idx_products_sellable ON products (sellable);
CREATE INDEX idx_products_lastSalesRank ON products (lastSalesRank);

CREATE TABLE publishers
(
    `id`           INTEGER PRIMARY KEY,
    `name`         TEXT     NOT NULL,
    `dateRecorded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `isDifficult`  BOOLEAN  NOT NULL DEFAULT 0
);
CREATE INDEX idx_publishers_name ON publishers (name);
CREATE TABLE products
(
    `id`                      INTEGER PRIMARY KEY,
    `name`                    TEXT    NOT NULL,
    `publisherId`             INTEGER NOT NULL,
    `amazonAsin`              TEXT UNIQUE,
    `dateSeen`                TEXT    NOT NULL,
    `publisherApprovalStatus` TEXT    NOT NULL DEFAULT 'pending',
    `sellable`                BOOLEAN NOT NULL DEFAULT 0,
    `lastSalesRank`           INTEGER,
    `lastSalesRankDate`       DATETIME, bookPublishingDate DATETIME,
    FOREIGN KEY (publisherId) REFERENCES publishers (id) ON DELETE CASCADE
);
CREATE INDEX idx_products_publisherId ON products (publisherId);
CREATE INDEX idx_products_amazonAsin ON products (amazonAsin);
CREATE INDEX idx_products_publisherApprovalStatus ON products (publisherApprovalStatus);
CREATE INDEX idx_products_sellable ON products (sellable);
CREATE INDEX idx_products_lastSalesRank ON products (lastSalesRank);

EElie Zedeck R.Query: `await env.DB.prepare("SELECT * FROM products WHERE amazonAsin = ?").bind...

M

Matt SilverlockOP•3/29/23, 3:56 PM

but it happens as soon as realistic traffic is sent to wranger, and other tables and other types of queries happen at the same time.

What kind of query volume (in QPS) - ?

EElie Zedeck R.Query: `await env.DB.prepare("SELECT * FROM products WHERE amazonAsin = ?").bind...

M

Matt SilverlockOP•3/29/23, 4:12 PM

Can you also send me your accountId + databaseId? Here or in DM?

MMatt Silverlock > but it happens as soon as realistic traffic is sent to wranger, and other tabl...

E

Elie Zedeck R.•3/29/23, 8:01 PM

Really small, I didn't measure or anything, but I guess not more than 10/s ... and it's just a short burst.

MMatt Silverlock Can you also send me your accountId + databaseId? Here or in DM?

E

Elie Zedeck R.•3/29/23, 8:01 PM

Ok, I'll DM you, I don't know my account ID

E

Elie Zedeck R.•3/29/23, 8:04 PM

I don't know if the DM was received or not ... anyway, DB ID = 2d159a31-757e-46c2-865b-095792688269

E

Elie Zedeck R.•3/29/23, 8:05 PM

I believe you should be able to find the account info from that

J

justaname•3/29/23, 10:06 PM

is there any way of doing a connection link instead of using workers?

Jjustaname is there any way of doing a connection link instead of using workers?

H

Hello, I’m Allie!•3/29/23, 10:08 PM

Not at the moment

P

Pinheiro•3/30/23, 12:06 PM

Is there any limit on server locations when using D1? I'm from Brazil and there's about ~500ms to make a simple crud GET/POST. When I use another database (Cloudflare pages/workers + any other DB service) this value decreases to ~150ms

Original message was deleted

P

Pinheiro•3/30/23, 12:22 PM

Thank you! =)

C

chientrm.com•3/30/23, 1:19 PM

does D1 support full text search?

Original message was deleted

C

chientrm.com•3/30/23, 1:40 PM

is that D1 atomic write?

Original message was deleted

C

chientrm.com•3/30/23, 1:42 PM

can perform transaction?

M

Mozzy•3/30/23, 2:30 PM

No worries

B

Ben Seitz•3/30/23, 3:26 PM

If I use

wrangler d1 execute

wrangler d1 execute

wrangler d1 execute

wrangler d1 execute to run an SQL query, will this query be all in one db transaction, even if there are multiple udates, inserts and deletes in there?

K

kian•3/30/23, 5:17 PM

At least 3.39.4 - it's probably much newer by now, but that's the last version I recall mentioned.

EElie Zedeck R.Query: `await env.DB.prepare("SELECT * FROM products WHERE amazonAsin = ?").bind...

S

SmallShen•3/31/23, 4:23 AM

I think this is multiple queries and sqlite doesn't support it
d1-database

Ccomputeronix can I run two queries back to back? ``` const { results } = await env.BIND...

M

Max (@rozenmd)•3/31/23, 4:42 AM

you can, but it'll always be called

results

results

results

results, so you'll need to make the second call

const { results: countResults...

const { results: countResults...

const { results: countResults...

const { results: countResults...

MMax (@rozenmd)you can, but it'll always be called `results`, so you'll need to make the second...

M

Matt SilverlockOP•3/31/23, 12:51 PM

To add to what Max said: this isn't a D1 thing, it's Object Destructuring in JS: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Destructuring_assignment

Destructuring assignment - JavaScript | MDN

The destructuring assignment syntax is a JavaScript expression that makes it possible to unpack values from arrays, or properties from objects, into distinct variables.

MMatt Silverlock To add to what Max said: this isn't a D1 thing, it's Object Destructuring in JS:...

M

Matt SilverlockOP•3/31/23, 12:53 PM

countResults

countResults

countResults

countResults isn't a property of

results

results

results

results and so it'll always be undefined if it doesn't exist on the object.

Each destructured property can have a default value. The default value is used when the property is not present, or has value undefined. It is not used if the property has value null.

U

Unsmart•3/31/23, 6:47 PM

it depends on your use case

N

Noel•4/1/23, 11:33 AM

Can we have math functions?

NNoel Can we have math functions? 🥺

M

Max (@rozenmd)•4/1/23, 2:44 PM

long term you'll be able to have arbitrary functions

M

Max (@rozenmd)•4/1/23, 2:44 PM

also which math functions are you after

N

Noel•4/1/23, 2:46 PM

i only needed pow so it wasn't a big issue

N

Noel•4/1/23, 2:47 PM

i have a query to find places near a given coordinate and i had to calculate the squared distance so i can order the results

What is your schema? What is the query? Types?

Similar Threads

Similar Threads

Similar Threads