website·Indexed 30 days ago
https://durcno.devA type-safe SQL query builder and schema definition tool for PostgreSQL. Durcno provides a fluent API for CRUD operations, relational queries, and schema management, including support for PostgreSQL-specific features like ENUMs, indexes, PostGIS, and pgvector. It features end-to-end TypeScript type safety, Zod validation schema generation, and a migration system.
$-prefixed utility functions that act as convenient shortcuts wrapping standard query operations. All these functions accept an optional where clause as the last argument, return a QueryPromise (which can be awaited or used with .then()), and are fully type-safe with inferred return types.| Function | Purpose | Returns |
|---|---|---|
$count | Count rows | number |
$exists | Check if rows exist | boolean |
$first | Get first matching row | T | null |
$distinct | Get unique column values | T[] |
$sum | Sum numeric column | number | null |
$avg | Average of column | number | null |
$min | Minimum value | number | null |
$max | Maximum value | number | null |
$-prefixed utility functions to simplify common query patterns. All these functions accept an optional where clause as the last argument, return a QueryPromise that can be awaited, and are fully type-safe with inferred return types.| Function | Purpose | Returns |
|---|---|---|
$count | Count rows | number |
$exists | Check if rows exist | boolean |
$first | Get first matching row | T | null |
$distinct | Get unique column values | T[] |
$sum | Sum numeric column | number | null |
$avg | Average of column | number | null |
$min | Minimum value | number | null |
$max | Maximum value | number | null |
pg, postgres, bun, and pglite, and includes first-class support for PostGIS geographic column types.Durcno is optimized for the PostgreSQL ecosystem and is compatible with the following runtimes and databases:
import { createInsertSchema, createUpdateSchema } from "durcno/validators/zod";
import { Users } from "./schema.ts";
// Generate schemas from your table definition
const insertUserSchema = createInsertSchema(Users);
const updateUserSchema = createUpdateSchema(Users);Durcno automatically converts TypeScript camelCase identifiers to snake_case when generating SQL statements and migration files. This applies to schemas, table names, column names, enum names, and sequence names. Consecutive uppercase sequences (acronyms) are handled correctly (e.g., XMLParser becomes xml_parser).
Note: Enum values (e.g., ["admin", "user"]) are NOT converted and are stored exactly as written.
// TypeScript definition
export const BlogPosts = table("public", "blogPosts", {
id: pk(),
authorId: bigint({ notNull }),
publishedAt: timestamp({ notNull }),
});
// Resulting generated SQL
CREATE TABLE "public"."blog_posts" (
"id" bigserial PRIMARY KEY,
"author_id" bigint NOT NULL,
"published_at" timestamp NOT NULL
);import {
bigint,
enumtype,
index,
many,
notNull,
now,
one,
pk,
relations,
table,
timestamptz,
unique,
uniqueIndex,
varchar,
} from "durcno";
// Define an enum
export const UserRole = enumtype("public", "userRole", ["admin", "user"]);
// Define tables
export const Users = table(
"public",
"users",
{
id: pk(),
username: varchar({ length: 50, unique, notNull }),
email: varchar({ length: 255, unique, notNull }),
role: UserRole.enumed({ notNull }),
createdAt: timestamptz({ notNull }).default(now()),
},
{
indexes: (t) => [index([t.email]), index([t.createdAt])],
},
);
export const UsersRelations = relations(Users, () => ({
posts: many(Posts, Posts.userId),
}));
export const Posts = table(
"public",
"posts",
{
id: pk(),
userId: bigint({ notNull }).references(() => Users.id),
title: varchar({ length: 255, notNull }),
createdAt: timestamptz({ notNull }).default(now()),
},
{
indexes: (t) => [index([t.userId]), index([t.userId, t.createdAt])],
},
);
export const PostsRelations = relations(Posts, () => ({
author: one(Users, Users.id),
}));createInsertSchema for INSERT operations and createUpdateSchema for UPDATE operations.import { createInsertSchema, createUpdateSchema } from "durcno/validators/zod";
import { Users } from "./schema.ts";
// Generate schemas from your table definition
const insertUserSchema = createInsertSchema(Users);
const updateUserSchema = createUpdateSchema(Users);