// Server-side actions
import { fetchData, insertData, updateData, deleteData } from "@/lib/db/drizzle-service";
// Direct database access
import { db } from "@/src/db";
import * as schema from "@/src/schema";
// Query building
import { eq, and, or, inArray, sql } from "drizzle-orm";
```typescript
## Common Patterns
### 1. Fetch Data
```typescript
// Simple fetch all
const users = await fetchData("users", {});
// Fetch with conditions
const users = await fetchData("users", {
conditions: [
{ eq_column: "company_id", eq_value: "123" },
{ eq_column: "active", eq_value: true }
],
limit: 10
});
// Fetch single record
const user = await fetchData("users", {
conditions: [{ eq_column: "user_id", eq_value: "abc" }],
single: true
});
// With tenant scoping (automatic company_id filtering)
const invoices = await fetchData(
"invoices",
{
scopes: { company_id: "company" }, // Automatically adds company_id filter
limit: 50
},
{ company_id: userData.company_id, user_id: userData.user_id }
);
```typescript
### 2. Insert Data
```typescript
// Insert single record
const newUser = await insertData("users", {
body: {
name: "John Doe",
email: "john@example.com",
company_id: "123"
}
});
// Insert multiple records
const newUsers = await insertData("users", {
body: [
{ name: "User 1", email: "user1@example.com" },
{ name: "User 2", email: "user2@example.com" }
]
});
```typescript
### 3. Update Data
```typescript
// Update by ID
const updated = await updateData("users", {
idColumn: "user_id",
id: "abc123",
body: {
name: "Updated Name",
updated_at: new Date()
}
});
```typescript
### 4. Delete Data
```typescript
// Delete single record
const deleted = await deleteData("users", {
idColumn: "user_id",
id: "abc123",
body: {} // Required but unused
});
```typescript
### 5. Complex Queries with Direct DB Access
```typescript
import { db } from "@/src/db";
import * as schema from "@/src/schema";
import { eq, and, inArray } from "drizzle-orm";
// Complex query with multiple conditions
const results = await db
.select()
.from(schema.invoices)
.where(
and(
eq(schema.invoices.companyId, "123"),
eq(schema.invoices.status, "draft")
)
)
.limit(10);
// Query with IN clause
const users = await db
.select()
.from(schema.users)
.where(inArray(schema.users.userId, ["id1", "id2", "id3"]));
// Join queries
const invoicesWithCustomers = await db
.select()
.from(schema.invoices)
.leftJoin(schema.customers, eq(schema.invoices.customerId, schema.customers.customerId))
.where(eq(schema.invoices.companyId, "123"));
```typescript
### 6. Raw SQL (When Needed)
```typescript
import { executeRawSQL } from "@/lib/db/drizzle-service";
// Execute raw SQL
const results = await executeRawSQL<any>(
"SELECT * FROM users WHERE company_id = $1",
["123"]
);
```typescript
## Migration from Supabase Client
### Before (Supabase)
```typescript
const { data, error } = await supabase
.from("users")
.select("*")
.eq("company_id", "123")
.single();
```typescript
### After (Drizzle)
```typescript
const user = await fetchData("users", {
conditions: [{ eq_column: "company_id", eq_value: "123" }],
single: true
});
```typescript
### Before (Supabase Insert)
```typescript
const { data, error } = await supabase
.from("users")
.insert({ name: "John", email: "john@example.com" })
.single();
```typescript
### After (Drizzle Insert)
```typescript
const user = await insertData("users", {
body: { name: "John", email: "john@example.com" }
});
```typescript
### Before (Supabase Update)
```typescript
const { data, error } = await supabase
.from("users")
.update({ name: "Updated" })
.eq("user_id", "123")
.single();
```typescript
### After (Drizzle Update)
```typescript
const user = await updateData("users", {
idColumn: "user_id",
id: "123",
body: { name: "Updated" }
});
```typescript
## Schema Access
```typescript
// Import schema
import * as schema from "@/src/schema";
// Access tables
schema.users
schema.invoices
schema.customers
// ... etc
// Access enums
schema.roles // ['Customer', 'Admin', 'Register Accountant', 'Developer']
schema.countryCode // ['NL', 'GB', 'DE', ...]
```typescript
## Error Handling
```typescript
try {
const data = await fetchData("users", {
conditions: [{ eq_column: "user_id", eq_value: userId }]
});
if (!data) {
throw new Error("User not found");
}
return { success: true, data };
} catch (error) {
console.error("Error fetching user:", error);
return {
success: false,
error: error instanceof Error ? error.message : "Unknown error"
};
}
```typescript
## Type Safety
```typescript
// Define types based on schema
type User = typeof schema.users.$inferSelect;
type NewUser = typeof schema.users.$inferInsert;
// Use in functions
async function createUser(userData: NewUser): Promise<User> {
return await insertData<User>("users", { body: userData });
}
```typescript
## Performance Tips
1. **Use specific columns**: Instead of `select("*")`, specify columns you need
2. **Add indexes**: Use Drizzle Kit to add indexes to frequently queried columns
3. **Batch operations**: Use array inserts for multiple records
4. **Connection pooling**: Already configured with max 10 connections
5. **Avoid N+1 queries**: Use joins instead of multiple queries
## Debugging
```typescript
// Enable query logging
import { db } from "@/src/db";
// Drizzle will log SQL queries in development mode
// Check console for generated SQL statements
```typescript
## Common Gotchas
1. **Table Name Conversion**: Table names are converted to camelCase in schema
- Database: `user_settings` -> Schema: `userSettings`
2. **Column Names**: Use snake_case when querying (matches database)
- `{ eq_column: "user_id", eq_value: "123" }`
3. **Returning Values**: All mutations return the modified record(s)
4. **Null vs Undefined**: Be explicit with null values in conditions
5. **Type Inference**: TypeScript will infer types based on table schema
## Need Help?
- [Drizzle ORM Documentation](https://orm.drizzle.team/)
- [Drizzle Kit Documentation](https://orm.drizzle.team/kit-docs/overview)
- Check `lib/db/drizzle-service.ts` for helper function implementations
- Review `src/schema.ts` for available tables and columns