M

matt•3/9/24, 7:20 AM

is this the right way of setting up the column? it's from the d1-drizzle-remix-example:

 createdAt: integer("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),

 createdAt: integer("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),

F

forslunds•3/9/24, 10:19 AM

Really hope we have some ”dynamic binding” when it goes GA, cant see going prod otherwise with it

Aalpha1119179 Exactly my problem. I'm doing the same thing. Pages built on nextjs 14. I've bee...

I

Ian•3/9/24, 11:36 AM

DM denied. Have sent friend request.

Mmatt is this the right way of setting up the column? it's from the d1-drizzle-remix-e...

M

mw10013•3/9/24, 3:34 PM

I also used the remix example and it didn't work so well for me. What works better for me so far is:

  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`(unixepoch())`),

  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`(unixepoch())`),

  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`(unixepoch())`),

  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .default(sql`(unixepoch())`),

MMax (@rozenmd)there's no limit, we just put one so people would believe us

O

ozgurgul1203•3/9/24, 5:12 PM

I have a hobby project with potentially a few TB of storage need for database. can d1 handle that?

Oozgurgul1203 I have a hobby project with potentially a few TB of storage need for database. c...

M

Max (@rozenmd)OP•3/9/24, 5:19 PM

no, I'd recommend you look into Hyperdrive if you're using Workers

Hhannes quick question regarding programmatic D1 usage (idea of many databases, per user...

D

DaniFoldi•3/9/24, 5:29 PM

You can use the query endpoint to run a

CREATE TABLE

CREATE TABLE

query: https://developers.cloudflare.com/api/operations/cloudflare-d1-query-database

W

wasauce•3/9/24, 11:01 PM

Did https://developers.cloudflare.com/d1/changelog/ move? I am having trouble finding

U

Unsmart•3/9/24, 11:01 PM

https://developers.cloudflare.com/d1/platform/changelog/

Cloudflare Docs

Changelog · Cloudflare D1 docs

As of 2024-03-05, D1 usage will start to be counted and may incur charges for an account’s future billing cycle.

U

Unsmart•3/9/24, 11:02 PM

Also you can search it works well (how I found it)

W

wasauce•3/9/24, 11:44 PM

Thank you

MMax (@rozenmd)there's no limit, we just put one so people would believe us

W

wasauce•3/9/24, 11:46 PM

Is the thought that the 2GB limit will remain or that that also might increase?

Wwasauce Did https://developers.cloudflare.com/d1/changelog/ move? I am having trouble fi...

W

wasauce•3/9/24, 11:46 PM

Note - Links in documentation point to this old URL

Wwasauce Note - Links in documentation point to this old URL

M

Max (@rozenmd)OP•3/10/24, 8:48 AM

cheers for that, will add a redirect on Monday (if someone else doesn't PR it first)

Wwasauce Is the thought that the 2GB limit will remain or that that also might increase?

M

Max (@rozenmd)OP•3/10/24, 8:49 AM

I was specifically talking about the number of DBs, we're working towards a 10GB limit

MMax (@rozenmd)I was specifically talking about the number of DBs, we're working towards a 10GB...

W

wasauce•3/10/24, 8:54 AM

Thank you! Looking forward to needing 10GB.

J

Jonathan Williams•3/10/24, 8:54 AM

J

Jonathan Williams•3/10/24, 8:55 AM

#d1-beta

E

Enduriel•3/10/24, 11:28 AM

Have there been any comments with regards to timeline or expectations for JS (or maybe wasm/rust???) procedures? Using workers-rs and my 2 options seem to be leaving a bunch of race conditions around or developing a system to handle the JsValue errors d1 returns from batch transactions so I can figure out why transactions failed

M

mnik01•3/10/24, 2:36 PM

I was trying to create Table with ENUM
but there was error about syntax

with string value table was created successfully

so d1 not supporting ENUM?

Mmnik01 I was trying to create Table with ENUM but there was error about syntax with st...

M

Max (@rozenmd)OP•3/10/24, 4:18 PM

D1 is SQLite under the hood

M

Max (@rozenmd)OP•3/10/24, 4:19 PM

ENUM looks like a MySQL feature

N

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

Is there a reason i cannot see the local D1 data with VSCode? I am using the SQLite Viewer extension (which I confirmed works with Django), but for some reason its not showing the data from the

Customers

Customers

table, as you can see with the wrangler query, there is data locally

NNicolas Is there a reason i cannot see the local D1 data with VSCode? I am using the SQL...

C

Cyb3r-Jak3•3/11/24, 12:47 AM

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

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-Jak3•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),

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)OP•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)OP•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)OP•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?

there's no limit, we just put one so people would believe us

???

Similar Threads

???