# SQL Databases
URL: /docs/integrations/sql-databases
LLM index: /llms.txt
Description: How safe SQL generation and the direct SQL runtime fit PostgreSQL, MySQL, and SQLite workflows.

# SQL Databases

Farming Labs ORM supports SQL-backed teams in 2 different ways:

- safe SQL generation through `@farming-labs/orm-cli`
- live direct SQL runtime through `@farming-labs/orm-sql`

That means teams can either generate SQL artifacts, run the unified runtime
against a real SQL client, or do both.

This is the integration path where method translation is already live today.
The same typed calls such as `findUnique`, `findMany`, `create`, `update`,
`upsert`, and `deleteMany` are compiled by the SQL driver into the target
dialect at runtime.

## Supported SQL dialects

- `postgres`
- `mysql`
- `sqlite`

## Basic config

```ts
import { defineConfig } from "@farming-labs/orm-cli";
import { authSchema } from "./src/schema";

export default defineConfig({
  schemas: [authSchema],
  targets: {
    sql: {
      out: "./generated/sql/0001_init.sql",
      dialect: "postgres",
    },
  },
});
```

## Runtime package

```ts
import { createOrm } from "@farming-labs/orm";
import { createMysqlDriver, createPgPoolDriver, createSqliteDriver } from "@farming-labs/orm-sql";
import { Pool } from "pg";
import { authSchema } from "./src/schema";
```

### PostgreSQL with `pg.Pool`

```ts
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

const orm = createOrm({
  schema: authSchema,
  driver: createPgPoolDriver(pool),
});
```

### MySQL

```ts
const orm = createOrm({
  schema: authSchema,
  driver: createMysqlDriver(mysqlPool),
});
```

### SQLite

```ts
const orm = createOrm({
  schema: authSchema,
  driver: createSqliteDriver(sqliteDatabase),
});
```

All 3 drivers expose the same unified query and mutation API:

```ts
await orm.user.findUnique({ where: { email: "ada@farminglabs.dev" } });
await orm.session.upsert({
  where: { token: "session-token" },
  create: { userId: "user_1", token: "session-token", expiresAt: new Date() },
  update: { expiresAt: new Date() },
});
```

The direct SQL runtime also verifies:

- `integer()` comparison filters
- `json()` equality filters
- compound-unique lookups and upserts
- JSON update and reload behavior
- collision-safe native projection aliases, so selected scalar fields stay intact
  even if a model also has a field name used by the native loader internally

For relation loading, the SQL runtime now has a native single-query path for
joinable singular branches such as:

- `session.user`
- `session.user.profile`
- `profile.user`

It also covers simple collection branches when the relation branch does not add
its own `where`, `orderBy`, `take`, or `skip`, including:

- direct `hasMany(...)` reads such as `user.sessions`
- explicit join-table `manyToMany(...)` reads such as `user.organizations`

That same native path is what the Drizzle and Kysely runtimes inherit, because
both packages route through `@farming-labs/orm-sql` under the hood.

Plural branches still work too, but they currently fall back to the shared
relation resolver when they need relation-level filters, paging, or ordering.

That fallback list should be read as the current implementation scope, not as a
limitation of SQL itself. PostgreSQL, MySQL, and SQLite can all support richer
native relation plans than the first singular path added here.

The native loader also keeps its internal presence markers on collision-safe
aliases. That means a real model field such as `present` can still be selected
normally without conflicting with native row-mapping metadata.

## Example output

For a simple user model, the SQL generator emits output like:

```sql
create table if not exists "users" (
  "id" text primary key not null,
  "email_address" text not null unique,
  "createdAt" timestamp not null
);
```

If you want that SQL output as a string instead of writing a file, use
`renderSafeSql(...)` from `@farming-labs/orm`. The CLI page includes the string
render example: [CLI generation](/docs/cli#render-as-a-string).

## How this fits into PostgreSQL, MySQL, and SQLite workflows

### PostgreSQL

Use:

```ts
sql: {
  out: "./generated/sql/0001_init.sql",
  dialect: "postgres",
}
```

This is a good fit when the team wants schema-first SQL generation but does not
need Prisma or Drizzle to be the consumer of that schema, or wants to pair the
generated SQL with `createPgPoolDriver(pool)` at runtime.

### MySQL

Use:

```ts
sql: {
  out: "./generated/sql/0001_init.sql",
  dialect: "mysql",
}
```

This works well when the database is MySQL but the team prefers a more direct
SQL workflow. It also pairs cleanly with `createMysqlDriver(mysqlPool)` if the
team wants the unified runtime API instead of handwritten query code.

### SQLite

Use:

```ts
sql: {
  out: "./generated/sql/0001_init.sql",
  dialect: "sqlite",
}
```

This is especially practical for lightweight apps, prototypes, demos, and local
development workflows, and it can run directly through
`createSqliteDriver(sqliteDatabase)`.

## When the SQL path is a good fit

- greenfield apps
- database-first teams
- simple deployment pipelines
- projects that want generated DDL without standardizing on one ORM
- libraries that want one runtime API and direct SQL backends

## Important limit

The SQL generator currently emits safe SQL output. It is not trying to replace a
full migration system with every advanced schema transform built in.

That is why the docs call it **safe SQL generation** rather than a complete
migration framework.

## Local verification

The repo verifies the SQL runtime locally against SQLite, PostgreSQL, and
MySQL.

Run it with:

```bash title="terminal"
pnpm test:local:sql
```

## Helpful references

<HoverLink
  href="https://node-postgres.com/features/pooling"
  title="node-postgres pooling"
  description="Learn how pg pools connections and transactions, which maps directly to the PostgreSQL path used by the Farming ORM SQL runtime."
  linkLabel="Open pg docs"
>
  PostgreSQL driver docs
</HoverLink>

<HoverLink
  href="https://sidorares.github.io/node-mysql2/docs"
  title="mysql2 documentation"
  description="Review mysql2 connection and pool behavior for the MySQL runtime path that powers Farming ORM's direct SQL driver."
  linkLabel="Open mysql2 docs"
>
  MySQL driver docs
</HoverLink>

<HoverLink
  href="https://nodejs.org/api/sqlite.html"
  title="Node.js SQLite API"
  description="See the built-in Node.js SQLite API used by the local SQLite test and runtime path in the direct SQL driver."
  linkLabel="Open SQLite docs"
>
  SQLite runtime docs
</HoverLink>