DuckSQL

DuckSQL is a TypeScript-first database toolkit built around a small, declarative schema language. You write a .duck file, DuckSQL builds the SQLite database, generates a typed client, and gives you a simple runtime API for queries, inserts, updates, deletes, transactions, hooks, and migrations.

If you are new to the project, start here:

1. Read docs/guia-iniciante.md.
2. Open examples/api-simples/README.md for a tiny API project.
3. Skim the examples below to understand the moving parts.

.duck file  ──┬─►  SQLite (.sqlite)
              └─►  Typed TS client (.duck.ts)

O que o DuckSQL resolve

DuckSQL exists for people who want to:

• describe a database in a readable text file;
• generate a typed TypeScript client automatically;
• keep SQLite as the storage engine;
• avoid hand-written models, repetitive CRUD code, and ORM-heavy setup;
• grow from a simple prototype to a maintainable project.

Comece pelo básico

1. Escreva o schema

VERSION 1

CREATE TABLE usuarios (
  id INT PRIMARY KEY AUTO INCREMENT,
  nome STRING NOT NULL MAX(80),
  email STRING UNIQUE NOT NULL MAX(120),
  senha STRING NOT NULL MAX(255),
  criado_em TIMESTAMP DEFAULT now()
)

2. Gere o banco e o client

npx duck build

That creates the SQLite database and a generated TypeScript client next to the .duck file.

3. Use o client

import { open } from "./schema.duck.js";

const db = open();

const usuario = db.usuarios.create({
  nome: "Ana",
  email: "[email protected]",
  senha: "123456",
});

const todos = db.usuarios.all();
const um = db.usuarios.findById(usuario.id);
const atualizados = db.usuarios.updateById(usuario.id, { nome: "Ana Paula" });
const removido = db.usuarios.deleteById(usuario.id);

db.close();

Como ler o projeto

The codebase is split into a few clear layers:

• src/lang/ parses the .duck language.
• src/emit/ turns the IR into SQLite DDL and TypeScript client code.
• src/driver/ abstracts SQLite access.
• src/client/ exposes Connection, Table, QueryBuilder, validation, hooks, and the where DSL.
• src/cli/ contains the duck command-line tool.

The contract between layers is the IR in src/lang/ir.ts. That means: parse once, and the rest of the system can reuse the same schema information.

Documentação principal

• Guia de iniciante
• Exemplo de API simples
• Arquitetura do projeto
• Glossário DuckSQL

Instalação via npm

Se você quiser usar o DuckSQL como biblioteca no seu projeto, instale pelo npm:

npm install ducksql

Depois disso, você pode importar o runtime ou usar o comando duck no terminal.

Publicação no npm

Para publicar uma nova versão, o fluxo recomendado é:

npm run check
npm run build
npm publish

O pacote já está configurado para ser publicado como público em publishConfig.access.

CLI

duck build [file.duck]      # default: scan cwd recursively
duck watch [file.duck]      # rebuild on change
duck migrate [file.duck]    # apply IR diff to an existing .sqlite
duck inspect <db.sqlite>    # print a .duck description of an existing DB
duck repl <db.sqlite>       # interactive SQL REPL

Runtime API

The generated .duck.ts is the friendly entry point, but the runtime can also be used directly when you want to build schemas dynamically or write lower-level tooling.

import {
  parseDuck, lowerToIR, emitSQLite, openConnection, sqliteDriver,
} from "ducksql";

const source = await fs.readFile("schema.duck", "utf8");
const schema = lowerToIR(parseDuck(source), { source });

const driver = sqliteDriver.open("db.sqlite");
for (const stmt of emitSQLite(schema)) driver.exec(stmt);
driver.close();

const db = openConnection("db.sqlite", schema);
db.table("usuarios").create({ nome: "ana", email: "[email protected]", senha: "123" });

Exemplos rápidos

Buscar com filtros

const clientes = db.usuarios.where({ papel: "cliente" }).orderBy("id", "desc").all();

Contar registros

const total = db.usuarios.count();

Relações

const pedidosDoUsuario = db.usuarios.pedidos(1);

Transações

db.transaction((tx) => {
  const usuario = tx.usuarios.create({ nome: "Carlos", email: "[email protected]", senha: "abc" });
  tx.pedidos.create({ usuarios_id: usuario.id, total: 49.9, status: "aberto" });
});

Boas práticas

• Comece com poucas tabelas e poucos campos.
• Use NOT NULL, UNIQUE, CHECK e DEFAULT para proteger dados desde o schema.
• Prefira findById, updateById e deleteById quando o fluxo for por chave primária.
• Use transaction quando uma operação criar ou alterar mais de uma tabela.
• Não use SQL cru para tudo; deixe o client gerar as queries simples.
• Crie índices nos campos usados com frequência em busca e filtro.

Exemplo de aplicação

The api/ folder contains a Fastify REST API scaffold using DuckSQL directly. It shows how to:

• create users;
• list users with pagination;
• update and delete users;
• create orders tied to a user;
• expose metadata and health routes.

License

MIT.