No those tiers are separate

UUnsmart No those tiers are separate

J

JSON•6/19/23, 3:48 AM

Thank you. I had no conscience lol

Ggeelen Our underlying infrastructure is quite different to PlanetScale, which makes cer...

S

Steved•6/19/23, 8:59 AM

Do we know yet how multiple databases will work yet? At the moment the limitations of the binding make having multiple DBs awkward. I'm hoping to use a separate DB per SaaS tenant. I can't currently see how that would fit the current bindings. Other systems, e.g. Azure SQL have a logical 'Server' that contains individual databases. That would work (assuming you don't call it 'Server').

SSteved Do we know yet how multiple databases will work yet? At the moment the limitatio...

M

Matt Silverlock•6/19/23, 12:47 PM

We have some ideas here for the “thousands to tens of thousands of tenants” - that will isolate each DB you create. Can’t share more yet and won’t be “next month”.

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

M

Matt Silverlock•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
```
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 Silverlock•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

) 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 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

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 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()

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

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

to like

test

test

will place the db into

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

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>

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

D1Database

D1Database

allows you to call the regular

.prepare

.prepare

,

.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

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

Yeah, if you can run

runMigration

runMigration

and it mirrors

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

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

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

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 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.

Similar Threads

Similar Threads

Similar Threads