We have some ideas here for the “thousands to tens of thousands of tenants” - that will isolate each

Jjoostschuur Can I access a binding to use D1 from a local Node script (i.e. not a worker whe...

M

Matt SilverlockOP•6/20/23, 10:51 AM

Your current options:

Shell out to
```
wrangler
```
wrangler
```
wrangler
```
wrangler
and have it run commands/import files for you
Put your own HTTP API in front of D1
Use
```
wrangler dev
```
wrangler dev
```
wrangler dev
```
wrangler dev
in remote mode against your live DB

Jjoostschuur To be honest, I am kind of tempted to just bundle all my data right into the wor...

M

Matt SilverlockOP•6/20/23, 11:15 AM

Why not KV? If it’s read heavy. You can bundle into your script but will eventually hit script size limits.

V

vassbence•6/20/23, 11:22 AM

Hi!

I've spent quite a few hours looking up this specific use-case but could not find much help online so I'd like to ask:

How can I work with D1 in the context of integration/e2e tests?
Let's say I have a JSON REST API written in a Worker.

As for the Worker itself I can use wranglers

unstable_dev()

unstable_dev()

unstable_dev()

unstable_dev() API to set up everything for testing.

My code currently looks like this:

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

  before(async () => {
    worker = await unstable_dev("src/index.ts", {
      experimental: { disableExperimentalWarning: true },
    });
  });

  after(async () => {
    await worker.stop();
  });

  // ... actual tests here
});

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

  before(async () => {
    worker = await unstable_dev("src/index.ts", {
      experimental: { disableExperimentalWarning: true },
    });
  });

  after(async () => {
    await worker.stop();
  });

  // ... actual tests here
});

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

  before(async () => {
    worker = await unstable_dev("src/index.ts", {
      experimental: { disableExperimentalWarning: true },
    });
  });

  after(async () => {
    await worker.stop();
  });

  // ... actual tests here
});

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

  before(async () => {
    worker = await unstable_dev("src/index.ts", {
      experimental: { disableExperimentalWarning: true },
    });
  });

  after(async () => {
    await worker.stop();
  });

  // ... actual tests here
});

At the very least it would be nice to have a fresh database ready at the start of the test. So like create a new SQLite file and run all the migrations. Currently the worker in the code above simply accesses my local D1 file.

How could I accomplish this in a way that would later fit into CI/CD etc?

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

V

vassbence•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

vassbence•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

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

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

V

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

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

V

vassbence•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

vassbence•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

vassbence•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

vassbence•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

vassbence•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

vassbence•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

vassbence•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 SilverlockOP•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

vassbence•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 SilverlockOP•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()

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

vassbence•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

vassbence•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

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

settings

persistTo

persistTo

persistTo

persistTo to like

test

test

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

vassbence•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 SilverlockOP•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

vassbence•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

vassbence•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 SilverlockOP•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

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

that would be awesome yes

V

vassbence•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 SilverlockOP•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

vassbence•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

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

here is my current code, looks to be working allright

V

vassbence•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 SilverlockOP•6/20/23, 2:23 PM

Looks pretty good to me.

M

Matt SilverlockOP•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

vassbence•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

vassbence•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 SilverlockOP•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 SilverlockOP•6/20/23, 2:26 PM

(Sometimes you might want that to happen)

We have some ideas here for the “thousands to tens of thousands of tenants” - that will isolate each

Similar Threads