i made some changes to my prisma schema, how do i migrate/sync the local D1 DB with these changes?

Aadjlsncm i made some changes to my prisma schema, how do i migrate/sync the local D1 DB w...

B

bun•4/6/24, 2:55 AM

prisma db push

B

bun•4/6/24, 2:55 AM

Or make migrations

B

bun•4/6/24, 2:56 AM

And then wrangler d1 migrations apply

V

Voids•4/6/24, 9:00 AM

how would I have my worker communicate wit my d1 database Im so confused

VVoids how would I have my worker communicate wit my d1 database Im so confused

M

Max (@rozenmd)•4/6/24, 9:05 AM

I'd recommend going through this guide end-to-end https://developers.cloudflare.com/d1/get-started/

Cloudflare Docs

Get started · Cloudflare D1 docs

This guide will instruct you through:

MMax (@rozenmd)I'd recommend going through this guide end-to-end https://developers.cloudflare....

V

Voids•4/6/24, 9:07 AM

ive tried, genuinly but Its so hard for me to understand

M

Max (@rozenmd)•4/6/24, 9:07 AM

that's alright - what part are you stuck on?

MMax (@rozenmd)that's alright - what part are you stuck on?

V

Voids•4/6/24, 9:08 AM

ok so I think this might actually be a worker issue, when I try to submit from my site I get a cors error

V

Voids•4/6/24, 9:09 AM

index.html:1 Access to XMLHttpRequest at 'https://messaging.logodzip18.workers.dev/' from origin 'http://127.0.0.1:3002' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.

VVoids index.html:1 Access to XMLHttpRequest at 'https://messaging.logodzip18.workers.d...

M

Max (@rozenmd)•4/6/24, 9:14 AM

ah welcome to CORS: https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS

in short, the browser rejects requests from domain A to domain B, if domain B doesn't send headers authorising those requests

MDN Web Docs

Cross-Origin Resource Sharing (CORS) - HTTP | MDN

Cross-Origin Resource Sharing (CORS) is an HTTP-header based mechanism that allows a server to indicate any origins (domain, scheme, or port) other than its own from which a browser should permit loading resources. CORS also relies on a mechanism by which browsers make a "preflight" request to the server hosting the cross-origin resource, in ord...

M

Max (@rozenmd)•4/6/24, 9:16 AM

you can modify the example here to get your Worker to send cors headers: https://developers.cloudflare.com/workers/examples/cors-header-proxy/

Cloudflare Docs

CORS header proxy · Cloudflare Workers docs

Documentation for Cloudflare Workers, a serverless execution environment that allows you to create entirely new applications or augment existing ones …

V

Voids•4/6/24, 9:17 AM

I did this tho

V

Voids•4/6/24, 9:18 AM

addEventListener('fetch', event => {
  event.respondWith(handleRequest(event.request));
});

async function handleRequest(request) {
  if (request.method === 'POST') {
    // Parse form data from the request
    const formData = await request.formData();
    const user = formData.get('User');
    const quote = formData.get('Quote');
    const date = parseInt(formData.get('date'));
    const visibility = parseInt(formData.get('visibility'));

    // Send data to D1 database
    const response = await fetch('https://quote.logozip18.workers.dev/', {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
      
        'Access-Control-Allow-Origin': '*', 
        'Access-Control-Allow-Methods': 'POST',
        'Access-Control-Allow-Headers': 'Content-Type',
      },
      body: JSON.stringify({ user, quote, date, visibility }),
    });

    if (response.ok) {
      return new Response('Data submitted successfully!', { status: 200 });
    } else {
      return new Response('Error submitting data to database', { status: 500 });
    }
  } else {
    return new Response('Method not allowed', { status: 405 });
  }
}

addEventListener('fetch', event => {
  event.respondWith(handleRequest(event.request));
});

async function handleRequest(request) {
  if (request.method === 'POST') {
    // Parse form data from the request
    const formData = await request.formData();
    const user = formData.get('User');
    const quote = formData.get('Quote');
    const date = parseInt(formData.get('date'));
    const visibility = parseInt(formData.get('visibility'));

    // Send data to D1 database
    const response = await fetch('https://quote.logozip18.workers.dev/', {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
      
        'Access-Control-Allow-Origin': '*', 
        'Access-Control-Allow-Methods': 'POST',
        'Access-Control-Allow-Headers': 'Content-Type',
      },
      body: JSON.stringify({ user, quote, date, visibility }),
    });

    if (response.ok) {
      return new Response('Data submitted successfully!', { status: 200 });
    } else {
      return new Response('Error submitting data to database', { status: 500 });
    }
  } else {
    return new Response('Method not allowed', { status: 405 });
  }
}

addEventListener('fetch', event => {
  event.respondWith(handleRequest(event.request));
});

async function handleRequest(request) {
  if (request.method === 'POST') {
    // Parse form data from the request
    const formData = await request.formData();
    const user = formData.get('User');
    const quote = formData.get('Quote');
    const date = parseInt(formData.get('date'));
    const visibility = parseInt(formData.get('visibility'));

    // Send data to D1 database
    const response = await fetch('https://quote.logozip18.workers.dev/', {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
      
        'Access-Control-Allow-Origin': '*', 
        'Access-Control-Allow-Methods': 'POST',
        'Access-Control-Allow-Headers': 'Content-Type',
      },
      body: JSON.stringify({ user, quote, date, visibility }),
    });

    if (response.ok) {
      return new Response('Data submitted successfully!', { status: 200 });
    } else {
      return new Response('Error submitting data to database', { status: 500 });
    }
  } else {
    return new Response('Method not allowed', { status: 405 });
  }
}

addEventListener('fetch', event => {
  event.respondWith(handleRequest(event.request));
});

async function handleRequest(request) {
  if (request.method === 'POST') {
    // Parse form data from the request
    const formData = await request.formData();
    const user = formData.get('User');
    const quote = formData.get('Quote');
    const date = parseInt(formData.get('date'));
    const visibility = parseInt(formData.get('visibility'));

    // Send data to D1 database
    const response = await fetch('https://quote.logozip18.workers.dev/', {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
      
        'Access-Control-Allow-Origin': '*', 
        'Access-Control-Allow-Methods': 'POST',
        'Access-Control-Allow-Headers': 'Content-Type',
      },
      body: JSON.stringify({ user, quote, date, visibility }),
    });

    if (response.ok) {
      return new Response('Data submitted successfully!', { status: 200 });
    } else {
      return new Response('Error submitting data to database', { status: 500 });
    }
  } else {
    return new Response('Method not allowed', { status: 405 });
  }
}

VVoids ```js addEventListener('fetch', event => { event.respondWith(handleRequest(eve...

M

Max (@rozenmd)•4/6/24, 9:23 AM

its not your D1 request thats failing - it's the request to this Worker

M

Mozzy•4/6/24, 2:12 PM

Just posting for visibility https://x.com/TablePlus/status/1776612840285286655

TablePlus (@TablePlus) on X

Hey @CloudflareDev @Cloudflare you're using the https protocol for the D1, however the result for rows is a key-value.

Which is not good, for example, D1 API failed to display this result:

select 1 as a, 2 as a;

Because key-value does not support two column with the same name.

Twitter

•

4/6/24, 2:08 PM

J

Jadugar_Jaggu•4/6/24, 2:56 PM

I am trying to import a sql db to D1 which I exported from cpanel ( phpmyadmin) . I am getting this error

near "SET": syntax error at offset 196 [code: 7500]

near "SET": syntax error at offset 196 [code: 7500]

I used command

wrangler d1 execute 10Database --file=database.sql

wrangler d1 execute 10Database --file=database.sql

JJadugar_Jaggu I am trying to import a sql db to D1 which I exported from cpanel ( phpmyadmin) ...

A

arch•4/6/24, 2:56 PM

What was the database you exported from? I'm assuming MySQL/Mariadb?

Aarch What was the database you exported from? I'm assuming MySQL/Mariadb?

J

Jadugar_Jaggu•4/6/24, 2:57 PM

MySql Database

A

arch•4/6/24, 2:58 PM

MySQL and Sqlite syntax varies a bit, so you might need to go through and edit the export to be compatible with SQLite This script seems like it would do the trick, but make a backup of the dump first before trying it.

A

arch•4/6/24, 3:02 PM

https://gist.github.com/esperlu/943776#file-mysql2sqlite-sh oops, I didn't link the script .-.

Gist

MySQL to Sqlite converter

MySQL to Sqlite converter. GitHub Gist: instantly share code, notes, and snippets.

Aarch MySQL and Sqlite syntax varies a bit, so you might need to go through and edit t...

J

Jadugar_Jaggu•4/6/24, 3:03 PM

I suggest adding support for cPanel exports because many PHP based websites and hostings on the internet uses cPanel and MySQL.

Aarch https://gist.github.com/esperlu/943776#file-mysql2sqlite-sh oops, I didn't link ...

J

Jadugar_Jaggu•4/6/24, 3:03 PM

I will try it

Bbun prisma db push

A

adjlsncmOP•4/6/24, 3:06 PM

thank you again

A

abel•4/6/24, 3:57 PM

what would be the “proper” approach for storing data for a consumer-facing app with an api built with workers?
i am considering d1, but the traditional approach of 1 database won’t work, and it is recommended to use multiple databases.
however, since i need some social features (private groups of friends), i cannot go with the per-user approach suggested for saas apis either.
having one table per database and sharding with uuidv7 or something like that seems like it could work, but loses the whole point of using a relational database if i only have a single table per database.
at this point, would it be better to try to model the data using durable objects or kv, or is there an approach that could work with d1?
i have experience with traditional sql databases, but not much with nosql

A

adjlsncmOP•4/6/24, 4:35 PM

so if this is the command to migrate from an empty database to the schema, what is it to migrate from an existing database to the updated schema? the default prisma migration commands dont seem to work

npx prisma migrate diff --script --from-empty --to-schema-datamodel ./prisma/schema.prisma >> migrations/0001_init.sql

npx prisma migrate diff --script --from-empty --to-schema-datamodel ./prisma/schema.prisma >> migrations/0001_init.sql

A

adjlsncmOP•4/6/24, 4:42 PM

npx prisma migrate diff --from-local-d1 --to-schema-datamodel="./prisma/schema.prisma" >> migrations/0002_init.sql

npx prisma migrate diff --from-local-d1 --to-schema-datamodel="./prisma/schema.prisma" >> migrations/0002_init.sql

i tried this but i get a weird error

A

adjlsncmOP•4/6/24, 4:43 PM

Screenshot_2024-04-06_at_12.43.14_PM.png

JJadugar_Jaggu I am trying to import a sql db to D1 which I exported from cpanel ( phpmyadmin) ...

J

Jadugar_Jaggu•4/6/24, 5:03 PM

cpanel phpmyadmin exported MySQL file to d1

Here's a workaround for this issue:

First, convert your exported plain SQL file to SQLite using the following command after installing SQLite from https://www.sqlite.org/download.html:

sqlite3 db.sqlite < db.sql

sqlite3 db.sqlite < db.sql

This command may display errors in the output. You'll need to remove any unnecessary lines from the SQL file.
Once you've resolved any errors, convert SQLite back to plain SQL. This time, your plain SQL file will be compatible with D1:

sqlite3.exe db.sqlite .dump > db2.sql

sqlite3.exe db.sqlite .dump > db2.sql

By following these steps, you can ensure compatibility with D1 after converting your MySQL file to SQLite and then back to plain SQL

J

johtso•4/6/24, 5:07 PM

When inserting a large number of rows, in your batch statement, is there any reason to have each insert statement insert say 20 rows (the kind of thing you might do in a transaction).. or is it better to just have every row as a separate insert statement?

J

johtso•4/6/24, 5:09 PM

so, 5000 insert statements in a batch vs 500 insert statements inserting 10 rows each

Jjohtso so, 5000 insert statements in a batch vs 500 insert statements inserting 10 rows...

J

Jadugar_Jaggu•4/6/24, 5:11 PM

it depends on what are you using for inserting row. worker OR wrangler OR cf rest api

J

Jadugar_Jaggu•4/6/24, 5:13 PM

you can insert 5000 rows using wrangler cli under 500ms

J

johtso•4/6/24, 5:13 PM

@Jadugar_Jaggu from a worker

J

johtso•4/6/24, 5:13 PM

using drizzle

J

johtso•4/6/24, 5:15 PM

export const updateMembers = async (db: dbType, membersObjs: z.infer<typeof insertMemberSchema>[]) => {
    const batchResponse = await db.batch([
        db.delete(members),
        ...membersObjs.map((member) => {
            return db.insert(members).values(member)
        })
    ])
    return batchResponse
}

export const updateMembers = async (db: dbType, membersObjs: z.infer<typeof insertMemberSchema>[]) => {
    const batchResponse = await db.batch([
        db.delete(members),
        ...membersObjs.map((member) => {
            return db.insert(members).values(member)
        })
    ])
    return batchResponse
}

export const updateMembers = async (db: dbType, membersObjs: z.infer<typeof insertMemberSchema>[]) => {
    const batchResponse = await db.batch([
        db.delete(members),
        ...membersObjs.map((member) => {
            return db.insert(members).values(member)
        })
    ])
    return batchResponse
}

export const updateMembers = async (db: dbType, membersObjs: z.infer<typeof insertMemberSchema>[]) => {
    const batchResponse = await db.batch([
        db.delete(members),
        ...membersObjs.map((member) => {
            return db.insert(members).values(member)
        })
    ])
    return batchResponse
}

J

johtso•4/6/24, 5:16 PM

or alternatively:

export const updateMembers = async (db: dbType, membersObjs: z.infer<typeof insertMemberSchema>[]) => {
  const BATCH_SIZE = 10; // Define an appropriate batch size
    const insertOps = chunkArray(membersObjs, BATCH_SIZE).map((batch) => {
        return db.insert(members).values(batch)
    })
    const batchResponse = await db.batch([
        db.delete(members),
        ...insertOps
    ])
    return batchResponse
}

export const updateMembers = async (db: dbType, membersObjs: z.infer<typeof insertMemberSchema>[]) => {
  const BATCH_SIZE = 10; // Define an appropriate batch size
    const insertOps = chunkArray(membersObjs, BATCH_SIZE).map((batch) => {
        return db.insert(members).values(batch)
    })
    const batchResponse = await db.batch([
        db.delete(members),
        ...insertOps
    ])
    return batchResponse
}

export const updateMembers = async (db: dbType, membersObjs: z.infer<typeof insertMemberSchema>[]) => {
  const BATCH_SIZE = 10; // Define an appropriate batch size
    const insertOps = chunkArray(membersObjs, BATCH_SIZE).map((batch) => {
        return db.insert(members).values(batch)
    })
    const batchResponse = await db.batch([
        db.delete(members),
        ...insertOps
    ])
    return batchResponse
}

export const updateMembers = async (db: dbType, membersObjs: z.infer<typeof insertMemberSchema>[]) => {
  const BATCH_SIZE = 10; // Define an appropriate batch size
    const insertOps = chunkArray(membersObjs, BATCH_SIZE).map((batch) => {
        return db.insert(members).values(batch)
    })
    const batchResponse = await db.batch([
        db.delete(members),
        ...insertOps
    ])
    return batchResponse
}

Jjohtso When inserting a large number of rows, in your batch statement, is there any rea...

I

irvyn•4/6/24, 5:53 PM

I think that just doing individual inserts works fine due to the way SQLite works, not entirely sure of your usecase but - https://twitter.com/fractaledmind/status/1774121491778441680

Stephen Margheim (@fractaledmind) on X

SQLite requires a different mindset for performance tuning than most dbs. For MySQL or PG the default advice is to prefer a smaller number of large queries, to minimize network latency. For SQLite, you should prefer a larger number of small queries.

Twitter

•

3/30/24, 5:08 PM

Iirvyn I think that just doing individual inserts works fine due to the way SQLite work...

J

johtso•4/6/24, 5:54 PM

interesting tweet, thanks!

I

irvyn•4/6/24, 5:56 PM

right if it is via CLI but if its from the worker it doesnt or does it?

I

irvyn•4/6/24, 5:57 PM

does d1 have network latency via a worker?

I

irvyn•4/6/24, 6:02 PM

U are right I had the wrong idea

I

irvyn•4/6/24, 6:02 PM

https://blog.cloudflare.com/building-d1-a-global-database

The Cloudflare Blog

Building D1: a Global Database

D1, Cloudflare’s SQL database, is now generally available. With new support for 10GB databases, data export, and enhanced query debugging, we empower developers to build production-ready applications with D1 to meet all their relational SQL needs

Original message was deleted

J

johtso•4/6/24, 6:21 PM

but if all your queries are in a batch they all get sent in one request

A

adjlsncmOP•4/6/24, 9:03 PM

in order to interact with the local d1 database are you limited to having to rebuild with cloudflare/next-on-pages and then running it with wrangler for every change or is there a way to npm run dev so you can have fast refresh

F

FalhadoNaMissao•4/6/24, 10:05 PM

Hey guys, I wonder if using the cloudflare D1 api to execute queries has the same performance than using the d1 binding, I ask that because my application has some logic to use dynamic created databases, having that api would be much appreciated since we don't have support to creating binding dynamically inside the worker

FFalhadoNaMissao Hey guys, I wonder if using the cloudflare D1 api to execute queries has the sam...

B

Beny•4/7/24, 1:17 AM

There is an API rate limit of 1,200 requests per 5 mins for each Cloudflare user (not per account).
If you are on Enterprise plan you can contact Cloudflare for higher limit, otherwise you can make more Cloudflare users to exceed the limit (invite members to your account and make new API keys on those).

FFalhadoNaMissao Hey guys, I wonder if using the cloudflare D1 api to execute queries has the sam...

C

Chaika•4/7/24, 1:20 AM

even besides the rate limit, currently each query via the cf api would have to back to core and then out to the DB, vs the workers bindings being on edge, so in some cases a lot more latency. It's something they're looking into though.
d1-database

B

Beny•4/7/24, 1:27 AM

It also might be impacted by outages such as the one last month when Dashboard + API was down.
They did say back in January that they are designing a way to dynamically access/bind databases.

i made some changes to my prisma schema, how do i migrate/sync the local D1 DB with these changes?

Similar Threads

Similar Threads

Similar Threads