Convex SQL

[blissful wing]

Then to actually make use of the SQL features that it would provide you would use a custom mutation that gets auto generated by a CLI command, which will generate code in your convex app next to the _generated folder in _sql

then we can create custom mutations and queries with the generated code:

import { withConstraints } from './_sql/db'
import { query, mutation } from './_generated/server'
import { v } from 'convex/values'

const { query: queryWithConstraints, mutation: mutationWithConstraints } =
  withConstraints(mutation, query)

Then the usage would be almost identical to normal mutations, only API difference is when deleting a document you do have to specify the Table.

Everything is still typesafe

export const createDocument = mutationWithConstraints({
  args: {
    name: v.string(),
    userId: v.id('users'),
    content: v.string(),
  },
  handler: async (ctx, args) => {
    const document = await ctx.db.insert('documents', {
      name: args.name,
      userId: args.userId,
      content: '18923712',
    })
    return document
  },
})

export const deleteDocument = mutationWithConstraints({
  args: {
    id: v.id('documents'),
  },
  handler: async (ctx, args) => {
    const result = await ctx.db.delete('documents', args.id)
    return result
  },
})

export const createUser = mutationWithConstraints({
  args: {
    firstName: v.string(),
    lastName: v.string(),
    username: v.string(),
    email: v.string(),
  },
  handler: async (ctx, args) => {
    const user = await ctx.db.insert('users', args)
    return user
  },
})

export const deleteUser = mutationWithConstraints({
  args: {
    id: v.id('users'),
  },
  handler: async (ctx, args) => {
    const result = await ctx.db.delete('users', args.id)
    return result
  },
})

Now we can create a user and a document that relates to that user.

If we try to delete the user now we will get a response like this:

We can make it a little more complicated now and instead of relating documents by their system ids we can relate a document by a targetField

so if we add a documentId field into the documents table (normally you'd add a unique constraint to the id field but we wont here to demo that the relations will generate indexes properly between parent and child tables)

Then we can add defaults to the field as well to generate a default value either with a caller or a static value. This will set the value on insert and will let you insert to that table without specifying a value for documentId, since it has a default it will default to that. (I havent actually implemented this 😢, but it exists in my parser and code gen i just have not made the convex stuff for it)

const Documents = Table('documents', {
  userId: v.id('users'),
  name: v.string(),
  documentId: v.string(),
  content: v.string(),
}).constraints((c) => [
  c.relation('userId', Users, {
    onDelete: 'restrict',
  }),
  c.default('documentId', () => crypto.randomUUID()),
])

const Attachments = Table('attachments', {
  attachmentId: v.string(),
  name: v.string(),
  url: v.string(),
  docId: v.string(),
}).constraints((c) => [
  c.unique('attachmentId'),
  c.relation('docId', Documents, {
    targetField: 'documentId',
    onDelete: 'restrict',
  }),
  c.default('attachmentId', () => crypto.randomUUID()),
])
export default defineSchema(
  {
    users: Users.toConvexTable(),
    documents: Documents.toConvexTable(),
    attachments: Attachments.toConvexTable(),
  },
  {
    schemaValidation: true,
  }
)

Now when inserting an attachment if we enter a documentId that doesnt exist in our DB it will not insert, but if we enter a valid documentId it will work.

That is all ive got so far. But i hope you could see that this could be something cool. Ill attach the repo. Its currently not a package, since I actually have not clue what im doing, ive never made a npm package and actually uploaded it. Im also very new to convex (so if this is really dumb lmk) and making packages in general. So any advice would be nice. Also I dont think ill build off this much more, but, if this is something that is interesting let me know i might actually put more effort into it and make a version of this that isnt so shit lol

https://github.com/tristendillon/convex-sql/

If you want to mess around with it (its probably very buggy) ill leave a way for you to clone and link the package up to a convex app in the github repo

[whole dove]

This is fire 🔥 🔥🔥🔥🔥

[soft zodiac]

the idea actually pretty dope and would solve some pain points. I hope it gets picked up into the officially supported convex-helpers

[blissful wing]

Im glad yall found it cool. I think that in larger production models query limits and bandwidth would become a problem so im not too sure about it atm

[blissful wing]

I think if i go anywhere with it, ill probably see something about convex helpers triggers or something idk im really busy with work for the next while so im not worrying about it too much

[stuck wyvern]

Would be very cool to have a similar translation https://stack.convex.dev/translate-sql-into-convex-queries to a better, scalable API 🙂

[blissful wing]

Yeah for sure, Ill probably re vist the actual project sometime in the near future and look for more ways to do an sql like feel to convex and implement automatic joins, unions, etc with it, all while monitoring bandwidth usage and query limits to make sure that it can scale properly as well. I havent actually played around with convex in a large dataset before now (im doing it right now for a project for work) so im learning it as i go and hopefully can translate into making a cool little tool!

[blissful wing]

Im going to make a completely different thread for this, since ive completely changed this around and made something very similar but works MUCH better

Ill paste the link to it once ive made the thread if yall want to go see the 'new' version