Migrate to an SQL Database

[webstudio-is]

Migrate to an SQL Database

[kof]

Found an interesting alternative to D1 on top of sqlite https://dqlite.io/, shout out to @freeekanayaka for creating it

[SaraVieira]

I would at least for now go with Postgres for a couple of reasons

• Support in Prisma is slightly better
• there's a lot of options to have these databases
• when using it with supabase we could also use their storage and auth and that would be a super huge deal to get into beta and allow people to upload fonts and images
• i am not sure about workers but codesandbox used Postgres with live and we didn't have any issues whatsoever with it
• SQlite is nice but it has to be deployed with docker has you need write accessto the file system and vercel/heroku do not give you that

[kof]

What just dropped https://blog.cloudflare.com/introducing-d1/ 🔥

[kof]

Given d1 is based on sqlite, maybe we should start with migrating to sqlite using any type of hosting for now and then start using d1 once available

[SaraVieira]

On it! Will start the migation across this repo and the SDK one

[SaraVieira]

Can you assign it to me?

[SaraVieira]

This is taking longer than expected for two reasons:

• No other database supports JSON as a tye in the database so I am at least for now stringifying everything
• Types in mongo had to be transformed to new models and so small calls like these:

await prisma.breakpoints.create({ data });

Have to be transformed into a massive call like so:

  const all = breakpoints.map(
    async (breakpoint: Breakpoint) =>
      await prisma.breakpoints.update({
        where: { treeId },
        data: {
          values: {
            create: breakpoint,
          },
        },
      })
  );

Since sqlite also does not support createMany

[kof]

Oh we should migrate to a schema, that JSON type was a workaround because prisma didn't support nested documents types

[kof]

Since sqlite also does not support createMany

Yeah, we should create a helper utility that does the same thing as createMany by running multiple queries in parallel. D1 has a batching function that does this faster by sending all queries to the database at once so we can extend our function later to use D1's batching.

My guess is that sqlite never needed batching because it runs close to the server that sends the requests, which is the case if we run it in a container next to the server as well, so that won't be a perf problem in this case

[SaraVieira]

ThG would be perfect in the future, for now we have will have to have a map

Will love to know if we actually have the format of that JSON

Will push both branches today so you can take a look

[kof]

that map looks like it won't be running those in parallel

[SaraVieira]

Oh, this is just a test before actually making a decent MR

I was gonna use something like: https://www.npmjs.com/package/p-all that we use at remote

[SaraVieira]

This actually looks better
https://github.com/sindresorhus/p-map

[kof]

Do we need to control the concurrency? I would have thought you want to run them in parallel always, so there is no need for an abstraction, its just javascript tm:

  await Promise.all([
    prisma.breakpoints.update(...)
  ])

Note how there is no await before prisma.breakpoints.update

But actually we should use Prisma transaction I think it is the exactly right API for this, I don know why I even used Promise.all for this

[kof]

• Btw. lets also create a document for contributors on how to work with the db, basics with links to full docs.

[kof]

Another sqlite based alternative https://github.com/rqlite/rqlite, shout out to @otoolep

[kof]

Streaming replication for SQLIte https://litestream.io/

[SaraVieira]

Check out the PR's pls

[kof]

Btw where should we host temporarily as long as the D1 from cloudflare is not available? I would have picked fly.io

[SaraVieira]

Fly sounds perfect!

[kof]

Also should we migrate all the sites from mongo, the once created from the landing by clicking "try" ? If its too much work we should only migrate the landing. If that's too much work too, we can keep the landing the way it is in alpha and freeze it until beta