Database

Raw Drizzle

Escape hatch to the underlying Drizzle ORM instance for complex queries.

Raw Drizzle

The generated Model API covers most use cases. When you need complex joins, custom SQL, or Drizzle features the Model abstraction doesn't expose, use $db directly.

Drizzle Imports

When writing custom queries, you will often need Drizzle utilities like eq, and, sql, or desc.

Omni Svelte provides built-in barrel exports for these so you do not need to install drizzle-orm in your project. This guarantees you are using the exact same version of Drizzle that Omni Svelte uses internally, preventing frustrating type mismatches.

Import them directly from the Omni Svelte package based on your database type:

// For PostgreSQL
import { eq, and, sql, desc } from 'omni-svelte/drizzle/pg';
 
// For MySQL
import { eq, and, sql, desc } from 'omni-svelte/drizzle/mysql';
 
// For SQLite
import { eq, and, sql, desc } from 'omni-svelte/drizzle/sqlite';

Importing Database Instance

import { db }    from '$db';          // Drizzle database instance
import { posts, users, comments } from '$schema'; // all Drizzle tables

Complex joins

import { db } from '$db';
import { posts, users, comments } from '$schema';
import { eq, and, count, sql, desc, gt } from 'omni-svelte/drizzle/pg';
 
const results = await db
  .select({
    postId:       posts.id,
    title:        posts.title,
    authorName:   users.name,
    commentCount: count(comments.id).as('comment_count')
  })
  .from(posts)
  .innerJoin(users, eq(posts.userId, users.id))
  .leftJoin(comments, eq(comments.postId, posts.id))
  .where(eq(posts.published, true))
  .groupBy(posts.id, users.name)
  .orderBy(desc(sql`comment_count`))
  .limit(10);

Transactions

import { db } from '$db';
import { posts, tags, postTags } from '$schema';
 
const result = await db.transaction(async (tx) => {
  const [post] = await tx
    .insert(posts)
    .values({ title, content, userId })
    .returning();
 
  if (tagIds.length) {
    await tx.insert(postTags).values(
      tagIds.map(tagId => ({ postId: post.id, tagId }))
    );
  }
 
  return post;
});

Batch inserts

await db.insert(users).values([
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob',   email: 'bob@example.com' },
  { name: 'Carol', email: 'carol@example.com' }
]);

Upsert (insert or update)

await db
  .insert(posts)
  .values({ slug, title, content, userId })
  .onConflictDoUpdate({
    target: posts.slug,
    set:    { title, content, updatedAt: new Date() }
  });

Raw SQL

import { sql } from 'omni-svelte/drizzle/pg';
 
const result = await db.execute(
  sql`SELECT id, title, ts_rank(search_vector, plainto_tsquery(${query})) AS rank
      FROM posts
      WHERE search_vector @@ plainto_tsquery(${query})
      ORDER BY rank DESC
      LIMIT 10`
);

Accessing from Model

You can also call Posts.drizzle() to get the same db instance scoped to the model's table:

const db = Posts.drizzle();
// db is the same Drizzle instance from $db

Previous Migrations Next Introduction

Edit this page on GitHub