Hi! I've spent quite a few hours looking up this specific use-case but could not find much help on

Jjoostschuur Let me see if I get this right: If I wanted to trigger a sync process between KV...

V

vassbenceOP•6/20/23, 11:26 AM

There are Unbound Workers which have no execution time limit.

Vvassbence Hi! I've spent quite a few hours looking up this specific use-case but could n...

V

vassbenceOP•6/20/23, 11:32 AM

As for my usecase I guess I could run shell scripts before testing to clean up and migrate the database but it would be nice if the tests somehow had their own instance/database file and they would not interfere with the local development file.

Any insight?

V

vassbenceOP•6/20/23, 11:37 AM

persistTo to a temporary file and execute migrations in the before function?

V

vassbenceOP•6/20/23, 11:39 AM

AFAIK wrangler d1 migrations appy just runs the migration files one after another with execute()

V

vassbenceOP•6/20/23, 11:39 AM

and so I can do the same without using child_process or similar node apis to keep everything progamatical in the test

V

vassbenceOP•6/20/23, 11:44 AM

oh yeah my bad you can not access the actual D1 APIs from the test, so I either have to create an endpoint inside the Worker to run the migrations or fall back to child_process.exec to run wrangler d1 migrations apply

V

vassbenceOP•6/20/23, 11:47 AM

Guess I just wanted to have one less dependency.. So you run better-sqlite3 on the db file which gets generated in a known location (since we set it in

persistTo

persistTo

persistTo

persistTo) then read the migrations files and execute them via better-sliqte3?

V

vassbenceOP•6/20/23, 11:56 AM

Wrangler usees Miniflare as per the codebase but then I guess miniflare 3 is using better-sqlite3?

M

Max (@rozenmd)•6/20/23, 12:03 PM

IIRC it might even be in the codebase twice

V

vassbenceOP•6/20/23, 12:07 PM

I guess this is not high on the roadmap but do you think this developer experience around testing D1 will improve later on?

M

Max (@rozenmd)•6/20/23, 12:09 PM

d1's dev experience in general has a long way to go, yeah

V

vassbenceOP•6/20/23, 12:11 PM

but hey, the product is already super awesome

i've been waiting years for a truly serverless database. it's even better that it's still running good old SQL and there is not yet another NoSQL API to learn

V

vassbenceOP•6/20/23, 12:15 PM

Later down the road I'd love to learn more about the limitations of the DB. To given an example DynamoDB is very limited in some ways but they are very exact about the limitations and also clearly state that why these limitations are in place (e.g.: no joins to have predicatable response times even with TBs of data).

Regular SQL databases inherently have a lot of complexity and are much more of a black-box in this sense but it would be still nice to learn about the limits of the platform and also maybe about any changes you guys made and more importantly why those changes were made (eg.: disable transactions because of X to get better Y metric)

Jjoostschuur Let me see if I get this right: If I wanted to trigger a sync process between KV...

M

Matt Silverlock•6/20/23, 12:16 PM

Ask in #kv - but to correct some things:

The execution time is CPU time. Writing / reading from data storage isn’t counted.
Unbound is part of the paid tier. Not a beta!
KV also has a REST API you can bulk write to. See the KV docs.

V

vassbenceOP•6/20/23, 12:19 PM

Also pardon me if it's not appropriate to ask but I'd love to know what the vision is for D1. Will it be truly a fully fledged SQL database that supports all and every use case that we can currently use a regular run of the mill sql database for? Or it will just focus on being good for cloud use cases and because of this maybe cut back in some other parts and not support some usecases?

Vvassbence Also pardon me if it's not appropriate to ask but I'd love to know what the visi...

M

Matt Silverlock•6/20/23, 12:37 PM

https://blog.cloudflare.com/d1-turning-it-up-to-11/ (and older blogs). I don’t think “cut back” and “fully fledged” make sense - every database has inherent limitations. AWS RDS can scale up, but you have to plan ahead, or provision capacity units, and it’s strictly regional. Is that fully fledged or also limited?

The Cloudflare Blog

D1: We turned it up to 11

We've been heads down iterating on D1, and we've just shipped a major new version that's substantially faster, more reliable, and introduces Time Travel: the ability to restore a D1 database to any point in time.

I

itsmatteomanf•6/20/23, 12:46 PM

One question I have regarding optimizing the actual code.
If you do a

env.DB.prepare()

env.DB.prepare()

at the start of the file for 10 will it incur a performance penalty/go to the actual DB?
I doubt it, but I also expect it might be slighty slower that doing it just when needed, if that statement is maybe used only once?

V

vassbenceOP•6/20/23, 1:03 PM

env.DB.prepare() does not send any type of request IIRC, you have to explicitly add .all() or .first() to send it to the DB

K

kian•6/20/23, 1:04 PM

Yeah it just constructs a

D1PreparedStatement

D1PreparedStatement

D1PreparedStatement

D1PreparedStatement object

V

vassbenceOP•6/20/23, 1:25 PM

@skye_31 persistTo only sets the folder in which D1 will be stored but somehow I still need to figure out the uuid of the database, how do you do that?

V

vassbenceOP•6/20/23, 1:26 PM

settings

persistTo

persistTo

persistTo

persistTo to like

test

test

will place the db into

test/v3/d1/{uuid}

test/v3/d1/{uuid}

test/v3/d1/{uuid}

test/v3/d1/{uuid} where uuid is the id of the database in the .toml file. Not sure how should I access that in the test

V

vassbenceOP•6/20/23, 1:27 PM

in like a non-hacky way

Vvassbence env.DB.prepare() does not send any type of request IIRC, you have to explicitly ...

I

itsmatteomanf•6/20/23, 1:46 PM

Yeah, Iwas sure about no queries, but maybe...

M

Matt Silverlock•6/20/23, 2:04 PM

Both @rozenmd and I were chatting about the state of testing (our excuse: we're in alpha). We're working through a design but I think the right approach is to be able to call:

createTestDatabase(): Promise<D1Database>

createTestDatabase(): Promise<D1Database>

createTestDatabase(): Promise<D1Database>

createTestDatabase(): Promise<D1Database>

runMigration(db: D1Database, name: string, dir?: string): Promise<void>

runMigration(db: D1Database, name: string, dir?: string): Promise<void>

runMigration(db: D1Database, name: string, dir?: string): Promise<void>

runMigration(db: D1Database, name: string, dir?: string): Promise<void>

deleteTestDatabase(db: D1Database): Promise<void>

deleteTestDatabase(db: D1Database): Promise<void>

deleteTestDatabase(db: D1Database): Promise<void>

deleteTestDatabase(db: D1Database): Promise<void>

... as helper methods that spawn a local DB for testing against. The

D1Database

D1Database

D1Database

D1Database allows you to call the regular

.prepare

.prepare

.prepare

.prepare,

.exec

.exec

.exec

.exec, etc methods on top.

What else are folks wanting here? We want avoid local testing drifting from production APIs, and you shouldn't have to think about where

.state

.state

.state

.state is stored.

V

vassbenceOP•6/20/23, 2:05 PM

I think if we are able to do migrations / cleanup on the database outside of the unstable_dev() then most of the use-cases should be covered

V

vassbenceOP•6/20/23, 2:07 PM

As for my problem I think I figured it out, by setting a preview_database_id="local" in the .toml file now I know the exact path of the db.sqlite file and thus able to execute migrations on the db before tests start running

Vvassbence I think if we are able to do migrations / cleanup on the database outside of the...

M

Matt Silverlock•6/20/23, 2:07 PM

Yeah, if you can run

runMigration

runMigration

runMigration

runMigration and it mirrors

wrangler d1 migrations apply <name>

wrangler d1 migrations apply <name>

wrangler d1 migrations apply <name>

wrangler d1 migrations apply <name> -> that gives you the tools you need to run a series of migrations from within CI

V

vassbenceOP•6/20/23, 2:07 PM

that would be awesome yes

V

vassbenceOP•6/20/23, 2:08 PM

wrangler d1 migrations apply

wrangler d1 migrations apply

wrangler d1 migrations apply

wrangler d1 migrations apply run in CI's without like logging into cloudflare? because i might just run it as a shell command as of now to make my test setup less brittle and later swithc to the programatic

runMigration

runMigration

runMigration

runMigration API once its out

Vvassbence `wrangler d1 migrations apply` run in CI's without like logging into cloudflare?...

M

Matt Silverlock•6/20/23, 2:16 PM

migrations apply

migrations apply

migrations apply

migrations apply defaults to remote.

Options:
      --local                 Execute commands/files against a local DB for use with wrangler dev --local  [boolean]
      --preview               Execute commands/files against a preview D1 DB  [boolean] [default: false]
      --persist-to            Specify directory to use for local persistence (you must use --local with this flag)  [string]
      --experimental-backend  Use new experimental DB backend  [boolean] [default: false]
      --batch-size            Number of queries to send in a single batch  [number] [default: 10000]

Options:
      --local                 Execute commands/files against a local DB for use with wrangler dev --local  [boolean]
      --preview               Execute commands/files against a preview D1 DB  [boolean] [default: false]
      --persist-to            Specify directory to use for local persistence (you must use --local with this flag)  [string]
      --experimental-backend  Use new experimental DB backend  [boolean] [default: false]
      --batch-size            Number of queries to send in a single batch  [number] [default: 10000]

Options:
      --local                 Execute commands/files against a local DB for use with wrangler dev --local  [boolean]
      --preview               Execute commands/files against a preview D1 DB  [boolean] [default: false]
      --persist-to            Specify directory to use for local persistence (you must use --local with this flag)  [string]
      --experimental-backend  Use new experimental DB backend  [boolean] [default: false]
      --batch-size            Number of queries to send in a single batch  [number] [default: 10000]

Options:
      --local                 Execute commands/files against a local DB for use with wrangler dev --local  [boolean]
      --preview               Execute commands/files against a preview D1 DB  [boolean] [default: false]
      --persist-to            Specify directory to use for local persistence (you must use --local with this flag)  [string]
      --experimental-backend  Use new experimental DB backend  [boolean] [default: false]
      --batch-size            Number of queries to send in a single batch  [number] [default: 10000]

V

vassbenceOP•6/20/23, 2:17 PM

describe("backend json rest api", () => {
  const testId = randomUUID();
  let worker;

  before(async () => {
    // running migrations on db
    execSync(
      `export NO_D1_WARNING=true && wrangler d1 migrations apply db --local --persist-to=temp/${testId}`
    );

    // setting up worker
    worker = await unstable_dev("src/index.ts", {
      experimental: {
        disableExperimentalWarning: true,
      },
      persistTo: `temp/${testId}`,
    });
  });

  after(async () => {
    await worker.stop();
    execSync("rm -rf temp");
  });

  // ... tests here
});

describe("backend json rest api", () => {
  const testId = randomUUID();
  let worker;

  before(async () => {
    // running migrations on db
    execSync(
      `export NO_D1_WARNING=true && wrangler d1 migrations apply db --local --persist-to=temp/${testId}`
    );

    // setting up worker
    worker = await unstable_dev("src/index.ts", {
      experimental: {
        disableExperimentalWarning: true,
      },
      persistTo: `temp/${testId}`,
    });
  });

  after(async () => {
    await worker.stop();
    execSync("rm -rf temp");
  });

  // ... tests here
});

describe("backend json rest api", () => {
  const testId = randomUUID();
  let worker;

  before(async () => {
    // running migrations on db
    execSync(
      `export NO_D1_WARNING=true && wrangler d1 migrations apply db --local --persist-to=temp/${testId}`
    );

    // setting up worker
    worker = await unstable_dev("src/index.ts", {
      experimental: {
        disableExperimentalWarning: true,
      },
      persistTo: `temp/${testId}`,
    });
  });

  after(async () => {
    await worker.stop();
    execSync("rm -rf temp");
  });

  // ... tests here
});

describe("backend json rest api", () => {
  const testId = randomUUID();
  let worker;

  before(async () => {
    // running migrations on db
    execSync(
      `export NO_D1_WARNING=true && wrangler d1 migrations apply db --local --persist-to=temp/${testId}`
    );

    // setting up worker
    worker = await unstable_dev("src/index.ts", {
      experimental: {
        disableExperimentalWarning: true,
      },
      persistTo: `temp/${testId}`,
    });
  });

  after(async () => {
    await worker.stop();
    execSync("rm -rf temp");
  });

  // ... tests here
});

V

vassbenceOP•6/20/23, 2:17 PM

here is my current code, looks to be working allright

V

vassbenceOP•6/20/23, 2:18 PM

let me know if you see anything wrong

Vvassbence ``` describe("backend json rest api", () => { const testId = randomUUID(); l...

M

Matt Silverlock•6/20/23, 2:23 PM

Looks pretty good to me.

M

Matt Silverlock•6/20/23, 2:23 PM

I've stubbed out some basic docs here - https://github.com/cloudflare/cloudflare-docs/pull/9471

but need to improve with a full example.

V

vassbenceOP•6/20/23, 2:25 PM

with the code above i do not need to use

preview_database_id

preview_database_id

preview_database_id

preview_database_id

V

vassbenceOP•6/20/23, 2:26 PM

as simply setting the

persistTo

persistTo

persistTo

persistTo to a different temporary path works. In the docs above if an user would run tests with

preview_database_id

preview_database_id

preview_database_id

preview_database_id set but not a custom

persistTo

persistTo

persistTo

persistTo they would mess up their development database

Vvassbence with the code above i do not need to use `preview_database_id`

M

Matt Silverlock•6/20/23, 2:26 PM

Yeah, that makes it a bit easier in that regard. I think we need to think through how we want

preview_database_id

preview_database_id

preview_database_id

preview_database_id to interact w/ programmatic testing here.

M

Matt Silverlock•6/20/23, 2:26 PM

(Sometimes you might want that to happen)

V

vassbenceOP•6/20/23, 2:27 PM

honestly what would be the perfect API for testing is to be able to have unstable_dev run D1 as in-memory only and have a function to run migrations on this in-memory sqlite db

Vvassbence honestly what would be the perfect API for testing is to be able to have unstabl...

M

Matt Silverlock•6/20/23, 2:30 PM

and/or we should just generate a

/tmp/<some random ID>

/tmp/<some random ID>

/tmp/<some random ID>

/tmp/<some random ID> file that gets thrown away by your OS. That way, if you have a larger DB, we don't eat all of your CI containers' memory

V

vassbenceOP•6/20/23, 2:31 PM

sure, that would be just as good

A

amorfati•6/20/23, 8:06 PM

how can I seed local

D1

D1

D1

D1 sqlite? I was using

bun

bun

bun

bun sqlite where I create a local

db.sqlite

db.sqlite

file and interact with it using the imported client. In the case of Cloudflare & wrangler, I'm a little confused as to how I can create and seed a local sqlite

Aamorfati how can I seed local `D1` sqlite? I was using `bun` sqlite where I create a loca...

A

amorfati•6/20/23, 8:07 PM

my

dev

dev

command

NO_D1_WARNING=true ./node_modules/.bin/wrangler dev --persist-to=./tokens.sqlite --experimental-json-config

NO_D1_WARNING=true ./node_modules/.bin/wrangler dev --persist-to=./tokens.sqlite --experimental-json-config

NO_D1_WARNING=true ./node_modules/.bin/wrangler dev --persist-to=./tokens.sqlite --experimental-json-config

NO_D1_WARNING=true ./node_modules/.bin/wrangler dev --persist-to=./tokens.sqlite --experimental-json-config

wrangler.json

wrangler.json

wrangler.json

wrangler.json

{
  "$schema": "https://gist.githubusercontent.com/o-az/d298a1776165407ba63ee763d32f7a89/raw/7f87871dd9a5efb9d3cc147fac0174ebdc9858fa/wrangler.json",
  "name": "tokens",
  "workers_dev": true,
  "main": "./src/index.ts",
  "compatibility_date": "2023-05-18",
  "compatibility_flags": [ "nodejs_compat" ],
  "placement": { "mode": "smart" },
  "d1_databases": [
    {
      "binding": "database",
      "database_name": "tokens",
      "preview_database_id": "local-tokens-sqlite"
    }
  ]
}

{
  "$schema": "https://gist.githubusercontent.com/o-az/d298a1776165407ba63ee763d32f7a89/raw/7f87871dd9a5efb9d3cc147fac0174ebdc9858fa/wrangler.json",
  "name": "tokens",
  "workers_dev": true,
  "main": "./src/index.ts",
  "compatibility_date": "2023-05-18",
  "compatibility_flags": [ "nodejs_compat" ],
  "placement": { "mode": "smart" },
  "d1_databases": [
    {
      "binding": "database",
      "database_name": "tokens",
      "preview_database_id": "local-tokens-sqlite"
    }
  ]
}

{
  "$schema": "https://gist.githubusercontent.com/o-az/d298a1776165407ba63ee763d32f7a89/raw/7f87871dd9a5efb9d3cc147fac0174ebdc9858fa/wrangler.json",
  "name": "tokens",
  "workers_dev": true,
  "main": "./src/index.ts",
  "compatibility_date": "2023-05-18",
  "compatibility_flags": [ "nodejs_compat" ],
  "placement": { "mode": "smart" },
  "d1_databases": [
    {
      "binding": "database",
      "database_name": "tokens",
      "preview_database_id": "local-tokens-sqlite"
    }
  ]
}

{
  "$schema": "https://gist.githubusercontent.com/o-az/d298a1776165407ba63ee763d32f7a89/raw/7f87871dd9a5efb9d3cc147fac0174ebdc9858fa/wrangler.json",
  "name": "tokens",
  "workers_dev": true,
  "main": "./src/index.ts",
  "compatibility_date": "2023-05-18",
  "compatibility_flags": [ "nodejs_compat" ],
  "placement": { "mode": "smart" },
  "d1_databases": [
    {
      "binding": "database",
      "database_name": "tokens",
      "preview_database_id": "local-tokens-sqlite"
    }
  ]
}

Aamorfati my `dev` command ```sh NO_D1_WARNING=true ./node_modules/.bin/wrangler dev --per...

C

Cyb3r-Jak3•6/20/23, 8:15 PM

https://developers.cloudflare.com/d1/learning/local-development/ maybe

CCyb3r-Jak3 https://developers.cloudflare.com/d1/learning/local-development/ maybe

A

amorfati•6/20/23, 8:17 PM

I've tried this. It says to use

--persist-to=/path/to/file

--persist-to=/path/to/file

--persist-to=/path/to/file

--persist-to=/path/to/file to persist data to a specific location but no luck and on seeding I couldn't find any relevant info.
seeding a local sqlite

Hi! I've spent quite a few hours looking up this specific use-case but could not find much help on

Similar Threads