DOCSEARCHbyAlgolia
Submit Docs

db0 Lightweight SQL Connector

repository·main·Indexed 43 days ago

https://github.com/unjs/db0

A lightweight, flexible SQL connector providing a unified query API across multiple databases including SQLite, PostgreSQL, MySQL, LibSQL, Cloudflare D1, Neon, and PlanetScale. Version 0.4.1 features a tagged template literal for SQL queries, support for custom connectors, and an opt-in query tracing system using Node.js diagnostics_channel.

Tokens
19.8K
Snippets
75
Records
100
Agent score
56%

What's inside db0

  1. What is DB0?

    main
    DB0 is a lightweight SQL connector designed to provide a simple and elegant query API. It is built to be highly flexible, supporting multiple SQL connectors and allowing for easy integration with ORMs or embedding into various web frameworks.

  2. Integrate DB0 with an ORM or framework

    main

    DB0 is designed to be integrated into your preferred ORM or framework. Currently, there are official integrations available for:

    • Drizzle: For users wanting to use Drizzle ORM with DB0.
    • Kysely: For users wanting to use Kysely with DB0.

    Refer to the specific integration guides for setup instructions and configuration details.

  4. Why use the Neon connector instead of Postgres?

    main
    The Neon connector is optimized for serverless environments. Unlike the standard Postgres connector which uses node-postgres and requires a raw TCP connection, the Neon connector uses the @neondatabase/serverless driver. This driver communicates via WebSockets, which is preferred for serverless runtimes that may not support opening raw TCP sockets.

  5. Avoid column name collisions in DB0 queries

    main

    Because DB0 connectors return rows as objects keyed by column name, selecting multiple columns with the same name (e.g., joining two tables that both have an id column) will cause a collision. DB0 will raise an error if a collision is detected.

    To resolve this, you must use unique aliases for the colliding columns or use the Drizzle relational query builder, which automatically handles unique aliasing.

    const rows = await db
  .select({
    userId: sql<number>`${users.id}`.as("user_id"),
    postId: sql<number>`${posts.id}`.as("post_id",
  })
  .from(users)
  .leftJoin(posts, eq(posts.userId, users.id));

  6. How transactions work with Prisma and DB0

    main

    Prisma transactions ($transaction) are supported only on DB0 connectors that maintain an open session.

    • Supported: Connectors that keep a session open.
    • Unsupported: Connectors that open a new connection per query (e.g., Cloudflare D1, PlanetScale, or libsql over HTTP). These connectors report transactions: false and will throw an error if a transaction is attempted.

    Concurrency Behavior: Because a DB0 database wraps a single connection, transactions are serialized. If a second transaction is started while one is already in progress, the second transaction will wait for the first to either commit or roll back.

  7. Use static parameters for identifiers like table names

    main

    DB0 supports Static Parameters, which allow you to inject string literals into parts of a SQL query where prepared statements are not supported, such as table names or column names.

    WARNING: SECURITY RISK Static parameters are NOT SANITIZED. Never use static parameters with untrusted input (e.g., data from a request body), as this can lead to SQL injection vulnerabilities.

    // Using static parameters for a table name
const tableName = "users";
const userId = "1001";
const { rows } = await db.sql`SELECT * FROM {${tableName}} WHERE id = ${userId}`;
console.log(rows);

  8. How PostgreSQL query parameter rewriting works

    main

    PostgreSQL natively uses numbered placeholders ($1, $2, ...), but DB0 uses ?. The connector automatically rewrites ? to the appropriate $n format before sending the query to the database.

    Important Rules:

    • Only a ? in the SQL code is treated as a placeholder.
    • A ? inside a string literal, a quoted identifier, a comment, or a dollar-quoted body is treated as data and is left untouched.
    • Do not mix styles: You cannot combine hand-written $n parameters with ? placeholders in the same query. This will cause a collision and throw an error.
    // DB0 rewrites this to: SELECT * FROM users WHERE id = $1
await db.sql`SELECT * FROM users WHERE id = ${userId}`;

// Question marks in strings are untouched
await db.sql`INSERT INTO posts (body) VALUES ('what? really?')`;

// This will throw an error:
await db.prepare("SELECT * FROM users WHERE id = $1 AND name = ?").all(id, name);

  9. Create a custom connector for a SQL database

    main
    If a built-in connector for your specific SQL database integration does not exist in db0, you can implement a custom one. To understand the required implementation details, you should examine the existing built-in connectors in the src/connectors directory of the repository to see how they interface with the db0 core.

  10. Use the Cloudflare D1 connector

    main

    Connect DB0 to Cloudflare D1 within Cloudflare Workers. This connector requires D1 to be enabled in your worker environment. The database binding is read from the env object exported by the cloudflare:workers runtime.

    import { createDatabase } from "db0";
import cloudflareD1 from "db0/connectors/cloudflare-d1";

const db = createDatabase(
  cloudflareD1({
    bindingName: "DB",
  }),
);

  12. Connect DB0 to MySQL using mysql2

    main

    To use MySQL with DB0, you must install the mysql2 package as a dependency. You can then initialize the database by passing the mysql connector from db0/connectors/mysql2 to the createDatabase function.

    import { createDatabase } from "db0";
import mysql from "db0/connectors/mysql2";

const db = createDatabase(
  mysql({
    /* options */
  }),
);