CCyb3r-Jak3 Local dev doesn't have all the data in a single database. Best way to query is t...

N

Nicolas•3/11/24, 12:49 AM

oh ok interesting, so i suppose there is no way to visually inspect the data locally without making direct queries?

NNicolas oh ok interesting, so i suppose there is no way to visually inspect the data loc...

C

Cyb3r-Jak3OP•3/11/24, 12:51 AM

There might be if you look through all the sqlite databases but the way that is split is changed. See https://github.com/cloudflare/miniflare/releases/tag/v3.20230918.0

R

Register•3/11/24, 1:02 AM

-- Migration number: 0000      2024-01-29T06:07:39.777Z

CREATE TABLE searchLogs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ip TEXT,
    type TEXT NOT NULL CHECK (type IN ('BLID', 'NAME')),
    query TEXT NOT NULL
);

-- Migration number: 0000      2024-01-29T06:07:39.777Z

CREATE TABLE searchLogs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ip TEXT,
    type TEXT NOT NULL CHECK (type IN ('BLID', 'NAME')),
    query TEXT NOT NULL
);

-- Migration number: 0000      2024-01-29T06:07:39.777Z

CREATE TABLE searchLogs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ip TEXT,
    type TEXT NOT NULL CHECK (type IN ('BLID', 'NAME')),
    query TEXT NOT NULL
);

-- Migration number: 0000      2024-01-29T06:07:39.777Z

CREATE TABLE searchLogs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ip TEXT,
    type TEXT NOT NULL CHECK (type IN ('BLID', 'NAME')),
    query TEXT NOT NULL
);

I have this searchLogs table. The createdAt is a datetime that defaults to the current timestamp.
Youd think this would be valid but when I try to insert:

async function logSearch(DB: D1Database, ip: string, type: string, search: string) {
  try {
    const logSearchQuery = /*sql*/ `INSERT INTO searchLogs(ip, type, query) VALUES(?, ?, ?)`;
    await DB.prepare(logSearchQuery).bind(ip, type.toUpperCase(), search).run();
  } catch (error) {
    console.error("Failed to log search:", error);
  }
}

async function logSearch(DB: D1Database, ip: string, type: string, search: string) {
  try {
    const logSearchQuery = /*sql*/ `INSERT INTO searchLogs(ip, type, query) VALUES(?, ?, ?)`;
    await DB.prepare(logSearchQuery).bind(ip, type.toUpperCase(), search).run();
  } catch (error) {
    console.error("Failed to log search:", error);
  }
}

async function logSearch(DB: D1Database, ip: string, type: string, search: string) {
  try {
    const logSearchQuery = /*sql*/ `INSERT INTO searchLogs(ip, type, query) VALUES(?, ?, ?)`;
    await DB.prepare(logSearchQuery).bind(ip, type.toUpperCase(), search).run();
  } catch (error) {
    console.error("Failed to log search:", error);
  }
}

async function logSearch(DB: D1Database, ip: string, type: string, search: string) {
  try {
    const logSearchQuery = /*sql*/ `INSERT INTO searchLogs(ip, type, query) VALUES(?, ?, ?)`;
    await DB.prepare(logSearchQuery).bind(ip, type.toUpperCase(), search).run();
  } catch (error) {
    console.error("Failed to log search:", error);
  }
}

I get this error:

✘ [ERROR] Failed to log search: Error: D1_ERROR: NOT NULL constraint failed: searchLogs.createdAt                                                                                                                                                
[1]
[1]       at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)
[1]       at async D1PreparedStatement.run (cloudflare-internal:d1-api:172:29)

✘ [ERROR] Failed to log search: Error: D1_ERROR: NOT NULL constraint failed: searchLogs.createdAt                                                                                                                                                
[1]
[1]       at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)
[1]       at async D1PreparedStatement.run (cloudflare-internal:d1-api:172:29)

✘ [ERROR] Failed to log search: Error: D1_ERROR: NOT NULL constraint failed: searchLogs.createdAt                                                                                                                                                
[1]
[1]       at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)
[1]       at async D1PreparedStatement.run (cloudflare-internal:d1-api:172:29)

✘ [ERROR] Failed to log search: Error: D1_ERROR: NOT NULL constraint failed: searchLogs.createdAt                                                                                                                                                
[1]
[1]       at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)
[1]       at async D1PreparedStatement.run (cloudflare-internal:d1-api:172:29)

???

R

Register•3/11/24, 1:03 AM

But additionally, this works fine...

-- Migration number: 0001     2024-02-04T19:30:58.384Z

CREATE TABLE IF NOT EXISTS handles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    auth TEXT NOT NULL,
    blid INTEGER NOT NULL,
    handle TEXT NOT NULL,
    lastSeen DATETIME,
    notes TEXT,
    UNIQUE(blid, handle)
);

INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('AAAAA', 0, 'Badspot', datetime('now', '-10 days'));
INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('ABCDE', 130, 'Space Guy', datetime('now', '-5 days'));
INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('ABCDE', 130, 'Other Guy', datetime('now', '-3 days'));
INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('ABCDE', 239, 'Lalam', datetime('now', '-7 days'));

-- Migration number: 0001     2024-02-04T19:30:58.384Z

CREATE TABLE IF NOT EXISTS handles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    auth TEXT NOT NULL,
    blid INTEGER NOT NULL,
    handle TEXT NOT NULL,
    lastSeen DATETIME,
    notes TEXT,
    UNIQUE(blid, handle)
);

INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('AAAAA', 0, 'Badspot', datetime('now', '-10 days'));
INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('ABCDE', 130, 'Space Guy', datetime('now', '-5 days'));
INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('ABCDE', 130, 'Other Guy', datetime('now', '-3 days'));
INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('ABCDE', 239, 'Lalam', datetime('now', '-7 days'));

-- Migration number: 0001     2024-02-04T19:30:58.384Z

CREATE TABLE IF NOT EXISTS handles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    auth TEXT NOT NULL,
    blid INTEGER NOT NULL,
    handle TEXT NOT NULL,
    lastSeen DATETIME,
    notes TEXT,
    UNIQUE(blid, handle)
);

INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('AAAAA', 0, 'Badspot', datetime('now', '-10 days'));
INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('ABCDE', 130, 'Space Guy', datetime('now', '-5 days'));
INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('ABCDE', 130, 'Other Guy', datetime('now', '-3 days'));
INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('ABCDE', 239, 'Lalam', datetime('now', '-7 days'));

-- Migration number: 0001     2024-02-04T19:30:58.384Z

CREATE TABLE IF NOT EXISTS handles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    auth TEXT NOT NULL,
    blid INTEGER NOT NULL,
    handle TEXT NOT NULL,
    lastSeen DATETIME,
    notes TEXT,
    UNIQUE(blid, handle)
);

INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('AAAAA', 0, 'Badspot', datetime('now', '-10 days'));
INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('ABCDE', 130, 'Space Guy', datetime('now', '-5 days'));
INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('ABCDE', 130, 'Other Guy', datetime('now', '-3 days'));
INSERT INTO handles (auth, blid, handle, lastSeen) VALUES ('ABCDE', 239, 'Lalam', datetime('now', '-7 days'));

R

Register•3/11/24, 1:04 AM

Im pretty baffled that D1 would throw an error for the logSearch table but not for handles table

M

matt•3/11/24, 7:59 AM

does d1 have json columns?

K

kian•3/11/24, 8:09 AM

D1 is SQLite so there isn't really JSON columns

K

kian•3/11/24, 8:09 AM

You store JSON in a TEXT column and can use all of the functions at https://www.sqlite.org/json1.html with it

RRegister ```sql -- Migration number: 0000 2024-01-29T06:07:39.777Z CREATE TABLE sea...

T

TigersWay•3/11/24, 8:23 AM

You should probably try:

createdAt DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP),

createdAt DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP),

createdAt DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP),

createdAt DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP),

J

James•3/11/24, 9:46 AM

According to the documentation, you mentioned support for more storage per database, and in Discord, you indicated a limit of 10GB. However, my database is 15GB. How can both small and large companies migrate to D1, considering its limited database size compared to other platforms that offer unlimited scaling and storage?

Ccopper can i set the d1 location myself or is it automatically chosen based on where th...

J

James•3/11/24, 9:49 AM

You can select the region when creating a database on the dashboard, and that region is where your main node exists for read and write operations. The read replica automatically spawns near the user's location from where the request originates.

As per the docs said.

Ccopper thanks, found it as well, this is nice do you use d1 in prod already?

J

James•3/11/24, 9:51 AM

As my database is big in size and d1 is not support big database yet so my answer is no

JJames According to the documentation, you mentioned support for more storage per datab...

M

Max (@rozenmd)•3/11/24, 9:55 AM

D1 is not a good fit for large databases (10GB+), we recommend folks look at Hyperdrive instead in that use case.

MMax (@rozenmd)D1 is not a good fit for large databases (10GB+), we recommend folks look at Hyp...

J

James•3/11/24, 9:57 AM

If I use Turbo that support Unlimited Storage, do I still need a hyperdrive, or is it necessary?

JJames If I use Turbo that support Unlimited Storage, do I still need a hyperdrive, or ...

M

Max (@rozenmd)•3/11/24, 9:57 AM

what's Turbo?

MMax (@rozenmd)what's Turbo?

J

James•3/11/24, 9:58 AM

Oh sorry this autocorrect I mean turso https://turso.tech/

JJames Oh sorry this autocorrect I mean turso https://turso.tech/

M

Max (@rozenmd)•3/11/24, 9:59 AM

We have a guide for setting that up here, no need for Hyperdrive: https://developers.cloudflare.com/workers/tutorials/connect-to-turso-using-workers/

MMax (@rozenmd)We have a guide for setting that up here, no need for Hyperdrive: https://develo...

J

James•3/11/24, 10:01 AM

Thx

TTigersWay You should probably try: `createdAt DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP...

R

Register•3/11/24, 10:26 AM

works thanks

M

Madyan•3/11/24, 11:02 AM

Hey all! I'm migrating into D1, and I have a seeding script that reads files from a directory (containing mock profile images of users, where each filename represents name of person), then it creates the relevant data and inserts it into the DB.
The challenge is that Node.js seems to be unable to access D1, while a wrangler runtime is unable to access the filesystem.
I'm now thinking of creating a combination of both (a Node script that sends an http request to a wrangler endpoint with the data it should insert into the DB). Any suggestions for an easier solution?

Ccopper @Max (@rozenmd) when will Hyperdrive will be avail for MySQL?

M

Max (@rozenmd)•3/11/24, 11:06 AM

worth asking in the Hyperdrive channel

M

Madyan•3/11/24, 11:13 AM

Thanks @copper!
The reason I was using Node is to be able to access the filesystem. My understanding is that three frameworks you're referring to would be using the wrangler runtime too, and hence won't be able to access the filesystem either, right?

M

Madyan•3/11/24, 11:15 AM

makes sense

M

Madyan•3/11/24, 11:15 AM

this is just a script to provide data for local / staging environments

Ccopper ok and why not put those files in s3 (or cf r2)

M

Madyan•3/11/24, 11:19 AM

ah makese sense. The script currently does both populating DB and uploading images (with my old provider). Perhaps I should do the image upload first like you suggested. Thank you!

M

Madyan•3/11/24, 11:24 AM

yeah, ideally I'd like to keep it all automated so that I can copy the script across my differnt project in the future

M

Madyan•3/11/24, 11:25 AM

but that's very cool! wasn't aware of aws4fetch

M

Madyan•3/11/24, 11:25 AM

I'll look into r2 first and have that as a plan B

M

Madyan•3/11/24, 11:37 AM

It creates fake data, adhering to the TS types generated by ORM to ensure everything is in sync. It's using images from the filesystem to ensure the files exist (e.g. I have a directory with food photos with filenames like

pad-thai.jpeg

pad-thai.jpeg

pad-thai.jpeg

pad-thai.jpeg, and I use the script to generate data based on the available files)

M

Madyan•3/11/24, 11:38 AM

We created a prototype, so we were hoping to keep the mock data realistic so that it resonates better with potential buyers when we demo it to them

O

Oleksii Kilevoi•3/11/24, 12:09 PM

Is there any way to create d1 database via http with token?

Local dev doesn't have all the data in a single database. Best way to query is to use miniflare

???

Similar Threads

Similar Threads

???

Similar Threads