MySql Database - Cloudflare Developers

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_JagguOP•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_JagguOP•4/6/24, 3:03 PM

I will try it

Bbun prisma db push

A

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

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

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

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

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

adjlsncm•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_JagguOP•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

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

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

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

SSki.oh wait.. does local dev only hit local db?

B

Beny•4/7/24, 4:49 AM

Yes if you're using

wrangler dev

wrangler dev

it will use a local database.
You can find the local sqlite file under

/.wrangler/state/v3/d1/miniflare-D1DatabaseObject/

/.wrangler/state/v3/d1/miniflare-D1DatabaseObject/

/.wrangler/state/v3/d1/miniflare-D1DatabaseObject/

/.wrangler/state/v3/d1/miniflare-D1DatabaseObject/ folder too if you want to inspect it

S

snacks•4/7/24, 11:39 AM

Hellloo, does anyone have any recommendations for managing migrations between drizzle/prisma and the built in migrations tool? Should I just go with what my ORM provides?

Original message was deleted

S

snacks•4/7/24, 11:56 AM

Thanks! Keen to give D1 a try for this side project, will go with drizzle for this one.

V

villainy•4/7/24, 3:13 PM

https://twitter.com/TablePlus/status/1776611556509253687

TablePlus (@TablePlus) on X

TablePlus will support Cloudflare D1 in the next update!

Twitter

•

4/6/24, 2:02 PM

E

EvanPiro•4/7/24, 3:55 PM

Is there a typescript library out there for building queries? My code has a ton of massive queries as strings and it's getting a bit unweildy.

J

jensen•4/7/24, 3:59 PM

should check https://kysely.dev/ and https://orm.drizzle.team/

I use drizzle

E

EvanPiro•4/7/24, 4:02 PM

how does this compare to knex?

J

jensen•4/7/24, 4:02 PM

it supports TS

J

jensen•4/7/24, 4:02 PM

I haven't used knex i about 5 years though.

E

EvanPiro•4/7/24, 4:04 PM

Does it play well with wrangler migrations? I have a ton.

Screen_Shot_2024-04-07_at_12.04.19_PM.png

E

EvanPiro•4/7/24, 4:17 PM

The most basic thing I'm trying to do is convert this code to drizzle:

      const org: any = await db
        .prepare(
          `
              SELECT o.id, o.name, o.logo_r2_object_id
              FROM account a
              JOIN member m ON a.id = m.account_id
              JOIN organization o ON m.organization_id = o.id
              WHERE a.id = ?
            `
        )
        .bind(user.id) 
        .first();

      const org: any = await db
        .prepare(
          `
              SELECT o.id, o.name, o.logo_r2_object_id
              FROM account a
              JOIN member m ON a.id = m.account_id
              JOIN organization o ON m.organization_id = o.id
              WHERE a.id = ?
            `
        )
        .bind(user.id) 
        .first();

      const org: any = await db
        .prepare(
          `
              SELECT o.id, o.name, o.logo_r2_object_id
              FROM account a
              JOIN member m ON a.id = m.account_id
              JOIN organization o ON m.organization_id = o.id
              WHERE a.id = ?
            `
        )
        .bind(user.id) 
        .first();

      const org: any = await db
        .prepare(
          `
              SELECT o.id, o.name, o.logo_r2_object_id
              FROM account a
              JOIN member m ON a.id = m.account_id
              JOIN organization o ON m.organization_id = o.id
              WHERE a.id = ?
            `
        )
        .bind(user.id) 
        .first();

L

Larry•4/7/24, 6:22 PM

How does drizzle compare to prisma?

K

kindaraffy•4/7/24, 6:26 PM

Drizzle is known to be more performant than prisma in the way it translates queries over. Drizzle seems to be more adaptive to products (has pretty good support for D1 in particular).

K

kindaraffy•4/7/24, 6:28 PM

Where can I find all the cloudflare locations that D1 has support in. All I can find is this in the docs: https://developers.cloudflare.com/d1/configuration/data-location/#available-hints. And I saw some hints to D1 coming to Australia this week. Is there a place where I can see all currently supported locations?

Cloudflare Docs

Data location · Cloudflare D1 docs

Learn how the location of data stored in D1 is determined, including where the leader is placed and how you optimize that location based on your …