# Overview
Source: https://razor-press.web-templates.io/overview

## What is litdb?

litdb is a suite of simple and lightweight database agnostic abstractions and SQL query builders for TypeScript and JavaScript. 
It is designed to leverage TypeScript's powerful type system to provide a simple and intuitive type-safe wrapper around
constructing and executing typed SQL queries with a focus on type safety, best practices and portability.

### litdb library

The core `litdb` library provides a set of composable query builders and SQL fragments that can be used to generate
SQL that can be executed on SQLite, MySQL and PostgreSQL.

### SQL Expression

The `$` tagged template function is used to create parameterized SQL Fragments that's split into `sql` and `params`:

```ts
type Fragment = { sql:string, params:Record<string,any> }
```

<live-preview>
JSON.stringify($`id = ${1} OR name = ${'John'}`)
</live-preview>

### SQL Builder

SQL Builders are just objects containing a `build()` function which returns an SQL `Fragment`:

```ts
interface SqlBuilder {
    build(): Fragment
}
```

Their simplicity and loose coupling allows them to be used in any ORM or driver that can execute parameterized SQL.

### litdb drivers

The litdb Drivers provide a unified interface for executing custom parameterized SQL, SQL Builders and SQL Fragments 
for their respective RDBMS. The SQLite drivers support both the Sync and Async DbConnection whilst
remote databases like PostgreSQL and MySQL only support the Async DbConnection.

```ts
interface SyncDbConnection {
    driver:Driver
    $:Function
    schema:Schema
    quote(symbol:string): string
    insert<T extends ClassInstance>(row:T, options?:InsertOptions): Changes
    insertAll<T extends ClassInstance>(rows:T[], options?:InsertOptions): Changes
    update<T extends ClassInstance>(row:T, options?:UpdateOptions): Changes
    delete<T extends ClassInstance>(row:T, options?:DeleteOptions): Changes
    listTables(): string[]
    dropTable<Table extends ClassParam>(table:Table): void
    createTable<Table extends ClassParam>(table:Table): void
    all<RetType>(strings: TemplateStringsArray | SqlBuilder | Fragment | IntoFragment<RetType>, ...params: any[]): RetType[]
    one<RetType>(strings: TemplateStringsArray | SqlBuilder | Fragment | IntoFragment<RetType>, ...params: any[]): RetType
    column<ReturnValue>(strings: TemplateStringsArray | SqlBuilder | Fragment, ...params: any[]): RetType[]
    value<ReturnValue>(strings: TemplateStringsArray | SqlBuilder | Fragment, ...params: any[]): ReturnValue
    arrays(strings: TemplateStringsArray | SqlBuilder | Fragment, ...params: any[]): any[][]
    array(strings: TemplateStringsArray | SqlBuilder | Fragment, ...params: any[]): any[]
    exec(strings:TemplateStringsArray | SqlBuilder | Fragment, ...params:any[]): Changes
    run(strings:TemplateStringsArray | SqlBuilder | Fragment, ...params:any[]): void
    prepareSync<T>(str: TemplateStringsArray | SqlBuilder | Fragment, ...params: any[]): Statement    
    close()
}

type Changes = { changes: number; lastInsertRowid: number | bigint }
```

## Data Models

litdb is more a lightweight data mapper than a full-fledged ORM, but many of its APIs are designed to work with 
[data models](/models) which are simple TypeScript classes that represent and map 1:1 to tables in a database.

## Safe by default

All SQL Queries and SQL Fragments require using a tagged template function or SQL Builder which parameterizes 
all values to prevent SQL Injection attacks, as such accidentally using a `string` will result in an error, e.g:

<live-preview>
const bobbyTables = "Robert'); DROP TABLE Students;--"
db.one(`SELECT * FROM Contact WHERE name = '${bobbyTables}'`)
</live-preview>

Driver APIs, SQL Builders and Expressions instead accept templated strings which auto parameterizes SQL queries:

<live-preview>
const bobbyTables = "Robert'); DROP TABLE Students;--"
db.one($.from(Contact).where(c => $`${c.name} = ${bobbyTables}`))
db.one($.from(Contact).where`name = ${bobbyTables}`)
db.one`SELECT * FROM Contact WHERE name = ${bobbyTables}`
db.one($`SELECT * FROM Contact WHERE name = ${bobbyTables}`)
db.one($.sql('SELECT * FROM Contact WHERE name = $bobbyTables', { bobbyTables }))
db.one({ sql:'SELECT * FROM Contact WHERE name = $bobbyTables', params:{bobbyTables} })
</live-preview>

## Portable

litdb is designed to be portable where in most cases query builders, expressions and driver APIs that stick to ANSI SQL
syntax can be used across different databases and drivers. This preserves investments and knowledge reuse allowing
your App's logic to bind to RDBMS-agnostic abstractions that can be reused across different databases or allow your 
App to easily migrate to run on different databases.

## Expressive

At the same time litdb SQL Builders and Fragments doesn't restrict you to a subset of SQL, instead of forcing the use of
a more restrictive query language that abstracts away the full power of SQL, litdb's SQL Builders are designed for creating
type-safe parameterized SQL that can be executed on any RDBMS, but when needed you can use the full feature-set of 
your RDBMS SQL dialect to make use of any RDBMS-specific features.


# Install
Source: https://razor-press.web-templates.io/install

To use litdb with your favorite ORM, no driver is required. Just use the `litdb` package directly:

:::sh
npm install litdb
:::

`litdb` is also available as a module, where it can be used directly in the browser:

```html
<script type="module">
import { sqlite as $ } from "https://unpkg.com/litdb/dist/index.min.js"
const { sql, params } = $.from(Contact).select(c => $`${c.name}`).build()
</script>
```

To get the most out of `litdb` we recommend using text editors that supports TypeScript definitions (e.g. VS Code)

# LitDB Drivers

Lightweight drivers with first-class support for litdb query builders are also available for the popular databases below:

### SQLite (Bun)

Use with [Bun's native SQLite3 driver](https://bun.sh/docs/api/sqlite) (requires Bun):

:::sh
bun install @litdb/bun-sqlite
:::

See [litdb Bun SQLite Docs](/bun-sqlite).

### SQLite (Node.js)

Use with Node [better-sqlite3](https://github.com/WiseLibs/better-sqlite3) (requires Node.js):

:::sh
npm install @litdb/better-sqlite
:::

See [litdb better-sqlite3 Docs](/better-sqlite).

### PostgreSQL

Use with the [postgres.js](https://github.com/porsager/postgres) client:

:::sh
npm install @litdb/postgres
:::

See [litdb postgres Docs](/postgres).

### MySQL

Use with the [mysql2](https://github.com/sidorares/node-mysql2) client:

:::sh
npm install @litdb/mysql2
:::

See [litdb mysql2 Docs](/mysql2).

### Request a Driver

If you'd like to see a driver for a specific client, please open or vote for a feature request on litdb's 
[GitHub Discussions](https://github.com/litdb/litdb/discussions/categories/ideas).

## Driver Usage

litdb drivers are lightweight data adapters providing convenience APIs for executing SQL and parameters. 
They can be used with or without litdb SQL Builders, but offer the most value when used together. 

The same APIs are available across all drivers, so you can easily switch between them. They include both **sync** APIs
recommended for SQLite libraries that use SQLite's native blocking APIs, whilst **async** APIs should be used for 
querying all other remote databases, e.g. PostgreSQL and MySQL.


# Models
Source: https://razor-press.web-templates.io/models

Model definitions are a representation of your RDBMS tables and columns in your App's code that's used to
configure how your App's classes and properties map to your database tables and columns.

They're used for creating table schemas and is able to influence the SQL that's generated by query builders 
and how results are mapped between your data models and RDBMS.

They can be defined using a Fluent API to configure existing classes or by using JavaScript decorators to declaratively 
annotate classes. In both cases litdb's TypeScript definitions provide intelli-sense to assist you in annotating your models. 

## Fluent API

```ts
import { Table } from 'litdb'

export class Contact {
    constructor(data) { Object.assign(this, data) }
    id = 0
    name = ''
    age = 0 || undefined
    email = ''
    city = '' || undefined
    createdAt = new Date(2025,1,1)
}
export class Order {
    constructor(data) { Object.assign(this, data) }
    id = 0
    contactId = 0
    total = 0.0
    createdAt = new Date()
}
export class OrderItem {
    constructor(data) { Object.assign(this, data) }
    id = 0
    orderId = 0
    sku = ''
    qty = 0
    total = 0.0
}
export class Product {
    constructor(data) { Object.assign(this, data) }
    sku = ''
    name = ''
    cost = 0.0
}

Table(Contact, {
    columns: {
        id:        { type:"INTEGER",  autoIncrement:true },
        name:      { type:"TEXT",     required:true },
        age:       { type:"INTEGER" },
        email:     { type:"TEXT",     required:true, index:true, unique:true },
        city:      { type:"TEXT" },
        createdAt: { type:"DATETIME", defaultValue:"CURRENT_TIMESTAMP" },
    }
})
Table(Order, {
    columns: {
        id:        { type:"INTEGER",  autoIncrement:true },
        contactId: { type:"INTEGER",  required:true, references:{ table:Contact, on:["DELETE","CASCADE"] } },
        total:     { type:"MONEY",    required:true },
        createdAt: { type:"DATETIME", defaultValue:"CURRENT_TIMESTAMP" },
    }
})
Table(OrderItem, {
    columns: {
        id:      { type:"INTEGER", autoIncrement:true },
        orderId: { type:"INTEGER", required:true, references:{ table:Order,   on:["DELETE","RESTRICT"] } },
        sku:     { type:"TEXT",    required:true, references:{ table:Product, on:["DELETE","RESTRICT"] } },
        qty:     { type:"INTEGER", required:true },
        total:   { type:"MONEY",   required:true }
    }
})
Table(Product, {
    columns: {
        sku:  { type:"TEXT",  primaryKey:true },
        name: { type:"TEXT",  required:true, index:true, unique:true },
        cost: { type:"MONEY", required:true },
    }
})
```

## Declarative Annotations

TypeScript or JS build systems that support [TC39 decorators](https://github.com/tc39/proposal-decorators) can use the 
`@table` and `@column` decorators to define their data models, e.g: 

```ts
import { table, column, DefaultValues } from 'litdb'

@table()
export class Contact {
    constructor(data?: Partial<Contact>) { Object.assign(this, data) }

    @column("INTEGER", { autoIncrement: true })
    id = 0
    
    @column("TEXT", { required: true })
    name = ''
    
    @column("INTEGER")
    age?: number

    @column("TEXT", { required:true, index:true, unique:true })
    email = ''

    @column("TEXT")
    city = ''
    
    @column("DATETIME", { defaultValue:'CURRENT_TIMESTAMP' })
    createdAt = new Date()
}

@table()
export class Order {
    constructor(data?: Partial<Order>) { Object.assign(this, data) }

    @column("INTEGER", { autoIncrement:true })
    id: number = 0

    @column("INTEGER", { required:true, references:{ table:Contact, on:["DELETE","CASCADE"] } })
    contactId: number = 0

    @column("MONEY", { required:true})
    total: number = 0

    @column("DATETIME", { defaultValue:DefaultValues.NOW })
    createdAt = new Date()
}

@table()
export class OrderItem {
    @column("INTEGER", { autoIncrement:true })
    id: number = 0

    @column("INTEGER", { required:true, references:{ table:Order, on:["DELETE","RESTRICT"] } })
    orderId: number = 0

    @column("TEXT", { required:true, references:{ table:Product, on:["DELETE","RESTRICT"] } })
    sku: string = ''

    @column("INTEGER", { required:true })
    qty: number = 0

    @column("MONEY", { required:true })
    total: number = 0
}

@table()
export class Product {
    @column("TEXT", { primaryKey:true })
    sku = ''
    @column("TEXT", { required:true, index:true, unique:true })
    name = ''
    @column("MONEY", { required:true })
    cost = 0.0
}
```

### Custom Data Types

When needed, a `Symbol` can be used to define custom data types, e.g:

```ts
class Address {
    @column(Symbol("POINT"))
    location
}
```


# Customize
Source: https://razor-press.web-templates.io/customize

## Custom Naming Strategy

App's can configure litdb to use a custom naming strategy for tables and columns by configuring the dialect's strategy. 

```ts
import { SnakeCaseStrategy } from "litdb"
import { connect } from "@litdb/postgres"

export const connection = connect({hostname, database, user, password})
export const { $, async: db, native:sql } = connection
connection.dialect.strategy = new SnakeCaseStrategy()
```

Where SnakeCaseStrategy is defined as returning table and column names in snake_case:

```ts
class SnakeCaseStrategy {
    tableName(table:string) : string { return snakeCase(table) }
    columnName(column:string) : string { return snakeCase(column) }
}
```

## Type Converters

litdb uses type converters to convert between JavaScript Objects and RDBMS data types. App's can configure litdb to use 
custom type converters by registering them with the driver's schema.

A Type Converter is an object with `toDb` and `fromDb` methods that convert between JavaScript and RDBMS data types.

```ts
interface TypeConverter {
    toDb(value: any): any;
    fromDb(value: any): any;
}
```

For example, this `DateTimeConverter` is used to convert `Date` objects to and from MySQL's `DATETIME` data type:

```ts
class DateTimeConverter implements TypeConverter
{
    toDb(value: any) {
        const d = toDate(value)
        return d ? dateISOString(d).replace('T',' ') : null
    }
    fromDb(value: any) {
        if (!value) return null
        return toDate(value)
    }
}
```

Custom Type Converters can be registered with the driver's schema for the data type it should apply to:

```ts
export const connection = connect({ host, database, user, password })
export const { $, async:db, native } = connection

connection.schema.converters['DATETIME'] = new DateTimeConverter()
```

## Register Converter for Mutliple Data Types

The `converterFor` utility function can be used to register a converter for multiple data types:

```ts
import { converterFor } from "litdb"

Object.assign(connection.schema.converters, 
    converterFor(new DateConverter(), "DATE", "DATETIME", "TIMESTAMP", "TIMESTAMPZ"))
```


# Creating a good Bug Report
Source: https://razor-press.web-templates.io/bug-report

### [View existing Issues](https://github.com/litdb/litdb/issues) or [Report a New Issue](https://github.com/litdb/litdb/issues/new?template=bug_report.yml)

> Issues must be reproducible with either a failing test, sample code, gist, link to a stand-alone project 
or otherwise clear steps to reproduce the issue. For details please see 
[How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve).


The more effective your bug report is, the better chance it will get fixed. So fixing a bug depends on how
well it's reported.

## 1) Reproducible:

If your bug is not reproducible it will never get fixed. You should clearly **provide the steps to reproduce the bug**.
Do not assume or skip any reproducing step. A Step-by-step description of the issue is easy to reproduce and fix.
E.g. **A failing test** with the issue (or in a gist) is the preferred way to report a reproducible error as it contains
all the assumptions and environment settings necessary for the error to occur.

## 2) Be Specific:

Do not write a essay about the problem. Be Specific and to the point. Try to summarize the problem in minimum
words yet in effective way. Do not combine multiple problems even they seem to be similar.
Write different reports for each problem.

## 3) Environment Details:

Ensure you're using the latest litdb packages, include the Operating System and the versions of the relevant
major components, e.g. JS Runtime (Node.js, Bun, Deno), etc. If you're using a browser, mention the browser and its version.


# litdb for Bun SQLite
Source: https://razor-press.web-templates.io/bun-sqlite

Use litdb with [Bun's native SQLite3 driver](https://bun.sh/docs/api/sqlite) (requires Bun):

:::sh
bun install @litdb/bun-sqlite
:::

## Configuration

**db.ts**

```ts
import { connect } from "@litdb/bun-sqlite"

export const connection = connect("app.db") // WAL enabled by default
export const { $, sync:db, async, native } = connection
```

:::tip
When needed use `native` to access underlying [bun:sqlite Database](https://bun.sh/docs/api/sqlite#database) 
:::

### Configuration Options

```ts
type ConnectionOptions = {
    // Creates a new database connection to the specified SQLite DB. 
    // If the database file does not exist, it is created.
    // default "app.db"
    fileName?:string
    // Whether to enable WAL
    // default true
    wal?:boolean
    // Open the database as read-only (no write operations, no create).
    readonly?: boolean
    // Allow creating a new database
    create?: boolean;
    // Open the database as read-write
    readwrite?: boolean;
    // When set to `true`, integers are returned as `bigint` types.
    // When set to `false`, integers are returned as `number` types and truncated to 52 bits.
    // default false
    safeIntegers?: boolean;
    // When set to `false` or `undefined`:
    // - Queries missing bound parameters will NOT throw an error
    // - Bound named parameters in JavaScript need to exactly match the SQL query.
    // default true
    strict?: boolean;
}
```

Example:

```ts
export const connection = connect({ fileName:'app.db' })
```

## Usage

Example of using `@litdb/bun-sqlite` sync APIs:

```ts
import { $, db } from "./db"
import { Contact } from "./models"

db.dropTable(Contact)
db.createTable(Contact)
db.insertAll([
    new Contact({ name:"John Doe", email:"john@mail.org" }),
    new Contact({ name:"Jane Doe", email:"jane@mail.org" }),
])

const janeEmail = 'jane@mail.org'
const jane = db.one<Contact>($.from(Contact).where(c => $`${c.email} = ${janeEmail}`))!

// Insert examples
const { lastInsertRowid: bobId } = db.insert(new Contact({ name:"Bob", email:"bob@mail.org" }))
const { lastInsertRowid } = db.exec`INSERT INTO Contact(name,email) VALUES ('Jo','jo@doe.org')`
const name = 'Alice', email = 'alice@mail.org'
db.exec`INSERT INTO Contact(name,email) VALUES (${name}, ${email})`

// Typed SQL fragment with named param example
const hasId = <Table extends { id:number }>(id:number|bigint) =>
    (x:Table) => $.sql($`${x.id} = $id`, { id })

const contacts = db.all($.from(Contact).into(Contact))                // => Contact[]
const bob = db.one($.from(Contact).where(hasId(bobId)).into(Contact)) // => Contact    
const contactsCount = db.value($.from(Contact).select`COUNT(*)`)      // => number
const emails = db.column($.from(Contact).select(c => $`${c.email}`))  // => string[]
const contactsArray = db.arrays($.from(Contact))                      // => any[][]
const bobArray = db.array($.from(Contact).where(hasId(bobId)))        // => any[]

// Update examples
jane.email = 'jane@doe.org'
db.update(jane)                           // Update all properties
db.update(jane, { onlyProps:['email'] })  // Update only email
db.exec($.update(Contact).set({ email:jane.email }).where(hasId(jane.id))) // query builder

// Delete examples
db.delete(jane)
db.exec($.deleteFrom(Contact).where(hasId(jane.id))) // query builder
```


# litdb for Node.js better-sqlite3
Source: https://razor-press.web-templates.io/better-sqlite

Use litdb with the [better-sqlite3 driver](https://github.com/WiseLibs/better-sqlite3) (requires Node.js):

:::sh
npm install @litdb/better-sqlite
:::

## Configuration

**db.ts**

```ts
import { connect } from "@litdb/better-sqlite"

export const connection = connect("app.db") // WAL enabled by default
export const { $, sync:db, async, native } = connection
```

:::tip
When needed use `native` to access the native [better-sqlite3 Database](https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#class-database)
:::

### Configuration Options

```ts
type ConnectionOptions = {
    // Creates a new database connection to the specified SQLite DB. 
    // If the database file does not exist, it is created.
    // default: app.db
    fileName?:string
    // Whether to enable WAL
    // default: true
    wal?:boolean
    // Open the database as read-only (no write operations, no create).
    // default: false
    readonly?: boolean
    // If the database does not exist, an Error will be thrown instead of creating a new file
    fileMustExist?: boolean | undefined;
    // The number of milliseconds to wait when executing queries on a locked database, 
    // before throwing a SQLITE_BUSY error
    // default: 5000
    timeout?: number | undefined;
    // Provide a function that gets called with every SQL string executed by the db connection
    verbose?: ((message?: unknown, ...additionalArgs: unknown[]) => void) | undefined;
    // If you're using a build system that moves, transforms, or concatenates your JS files,
    // you can solve it by using this option to provide the file path of better_sqlite3.node 
    // (relative to the current working directory).
    nativeBinding?: string | undefined;
}
```

Example:

```ts
export const connection = connect({ fileName:'app.db' })
```

## Usage

Example of using `@litdb/better-sqlite` sync APIs:

```ts
import { $, db } from "./db"
import { Contact } from "./models"

db.dropTable(Contact)
db.createTable(Contact)
db.insertAll([
    new Contact({ name:"John Doe", email:"john@mail.org" }),
    new Contact({ name:"Jane Doe", email:"jane@mail.org" }),
])

const janeEmail = 'jane@mail.org'
const jane = db.one<Contact>($.from(Contact).where(c => $`${c.email} = ${janeEmail}`))!

// Insert examples
const { lastInsertRowid: bobId } = db.insert(new Contact({ name:"Bob", email:"bob@mail.org" }))
const { lastInsertRowid } = db.exec`INSERT INTO Contact(name,email) VALUES ('Jo','jo@doe.org')`
const name = 'Alice', email = 'alice@mail.org'
db.exec`INSERT INTO Contact(name,email) VALUES (${name}, ${email})`

// Typed SQL fragment with named param example
const hasId = <Table extends { id:number }>(id:number|bigint) =>
    (x:Table) => $.sql($`${x.id} = $id`, { id })

const contacts = db.all($.from(Contact).into(Contact))                // => Contact[]
const bob = db.one($.from(Contact).where(hasId(bobId)).into(Contact)) // => Contact    
const contactsCount = db.value($.from(Contact).select`COUNT(*)`)      // => number
const emails = db.column($.from(Contact).select(c => $`${c.email}`))  // => string[]
const contactsArray = db.arrays($.from(Contact))                      // => any[][]
const bobArray = db.array($.from(Contact).where(hasId(bobId)))        // => any[]

// Update examples
jane.email = 'jane@doe.org'
db.update(jane)                           // Update all properties
db.update(jane, { onlyProps:['email'] })  // Update only email
db.exec($.update(Contact).set({ email:jane.email }).where(hasId(jane.id))) // query builder

// Delete examples
db.delete(jane)
db.exec($.deleteFrom(Contact).where(hasId(jane.id))) // query builder
```


# litdb for postgres.js
Source: https://razor-press.web-templates.io/postgres

Use litdb with [postgres.js](https://github.com/porsager/postgres) driver:

:::sh
npm install @litdb/postgres
:::

## Configuration

**db.ts**

```ts
import { connect } from "@litdb/postgres"

export const connection = connect({ hostname, database, user, password })
export const { $, async:db, native:sql } = connection
```

:::tip
When needed use `sql` to access the native [postgres.js sql function](https://github.com/porsager/postgres#usage)
:::

### Configuration Options

```ts
type ConnectionOptions = {
    /** Postgres ip address[s] or domain name[s] */
    host?: string | undefined;
    /** Postgres server[s] port[s] */
    port?: number | undefined;
    /** unix socket path (usually '/tmp') */
    path?: string | undefined;
    /** Password of database user (an alias for `password`) */
    pass?: Options<T>['password'] | undefined;
    /**
     * Password of database user
     * @default process.env['PGPASSWORD']
     */
    password?: string | (() => string | Promise<string>) | undefined;
    /** Name of database to connect to (an alias for `database`) */
    db?: Options<T>['database'] | undefined;
    /** Username of database user (an alias for `user`) */
    username?: Options<T>['user'] | undefined;
    /** Postgres ip address or domain name (an alias for `host`) */
    hostname?: Options<T>['host'] | undefined;
    /**
     * Disable prepared mode
     * @deprecated use "prepare" option instead
     */
    no_prepare?: boolean | undefined;
    /**
     * Idle connection timeout in seconds
     * @deprecated use "idle_timeout" option instead
     */
    timeout?: Options<T>['idle_timeout'] | undefined;
}
```

See [postgres.js Connection Options](https://github.com/porsager/postgres?tab=readme-ov-file#connection-details) for more.

Example:

```ts
connection = connect({ hostname, database, user, password })
connection = connect(connectionString, options)
```

## Usage

Example of using `@litdb/postgres` async APIs:

```ts
import { $, db } from "./db"
import { Contact } from "./models"

await db.dropTable(Contact)
await db.createTable(Contact)
await db.insertAll([
    new Contact({ name:"John Doe", email:"john@mail.org" }),
    new Contact({ name:"Jane Doe", email:"jane@mail.org" }),
])

const janeEmail = 'jane@mail.org'
const jane = await db.one<Contact>($.from(Contact).where(c => $`${c.email}=${janeEmail}`))

// Insert examples
const { lastInsertRowid:bobId } = await db.insert(
    new Contact({ name:"Bob", email:"bob@mail.org"}))

const { lastInsertRowid } = await db.exec
    `INSERT INTO Contact(name,email) VALUES('Jo','jo@doe.org')`

const name = 'Alice', email = 'alice@mail.org'
await db.exec`INSERT INTO Contact(name,email) VALUES (${name}, ${email})`

// Typed SQL fragment with named param example
const hasId = <Table extends { id:number }>(id:number|bigint) =>
    (x:Table) => $.sql($`${x.id} = $id`, { id })

const contacts = await db.all($.from(Contact).into(Contact))                // => Contact[]
const bob = await db.one($.from(Contact).where(hasId(bobId)).into(Contact)) // => Contact
const contactsCount = await db.value($.from(Contact).rowCount())            // => number
const emails = await db.column($.from(Contact).select(c => $`${c.email}`))  // => string[]
const contactsArray = await db.arrays($.from(Contact))                      // => any[][]
const bobArray = await db.array($.from(Contact).where(hasId(bobId)))        // => any[]

// Update examples
jane.email = 'jane@doe.org'
await db.update(jane)                           // Update all properties
await db.update(jane, { onlyProps:['email'] })  // Update only email
// query builder
await db.exec($.update(Contact).set({ email:jane.email }).where(hasId(jane.id)))

// Delete examples
await db.delete(jane)
await db.exec($.deleteFrom(Contact).where(hasId(jane.id))) // query builder
```


# litdb for mysql2
Source: https://razor-press.web-templates.io/mysql2

Use litdb with [node-mysql2](https://github.com/sidorares/node-mysql2) driver:

:::sh
npm install @litdb/mysql2
:::

## Configuration

Example of using the [node-mysql2](https://github.com/sidorares/node-mysql2) driver:

**db.ts**

```ts
import { connect } from "@litdb/mysql2"

export const connection = connect({ hostname, database, user, password })
export const { $, async:db, native:sql } = connection
```

:::tip
When needed use `native` to access [mysql2 Connection Pool](https://sidorares.github.io/node-mysql2/docs#using-connection-pools)
:::

### Configuration Options

```ts
type ConnectionOptions = {
    // DECIMAL and NEWDECIMAL types will be returned as numbers if this option is set to `true`
    // default: false
    decimalNumbers?: boolean;
    // The MySQL user to authenticate as
    user?: string;
    // The password of that MySQL user
    password?: string;
    // Name of the database to use for this connection
    database?: string;
    // The charset for the connection.
    // default: UTF8_GENERAL_CI
    charset?: string;
    // The hostname of the database you are connecting to
    // default: localhost
    host?: string;
    // The port number to connect to
    // default: 3306
    port?: number;
    // The source IP address to use for TCP connection
    localAddress?: string;
    // The path to a unix domain socket to connect to. When used host and port are ignored
    socketPath?: string;
    // The timezone used to store local dates
    // default: local
    timezone?: string | 'local';
}
```

See [node-mysql Connection Pool Options](https://sidorares.github.io/node-mysql2/docs#using-connection-pools) for more.

Example:

```ts
connection = connect({ host, database, user, password })
connection = connect(connectionString)
```

## Usage

Example of using `@litdb/mysql2` async APIs:

```ts
import { $, db } from "./db"
import { Contact } from "./models"

await db.dropTable(Contact)
await db.createTable(Contact)
await db.insertAll([
    new Contact({ name:"John Doe", email:"john@mail.org" }),
    new Contact({ name:"Jane Doe", email:"jane@mail.org" }),
])

const janeEmail = 'jane@mail.org'
const jane = await db.one<Contact>($.from(Contact).where(c => $`${c.email}=${janeEmail}`))

// Insert examples
const { lastInsertRowid:bobId } = await db.insert(
    new Contact({ name:"Bob", email:"bob@mail.org"}))

const { lastInsertRowid } = await db.exec
    `INSERT INTO Contact(name,email) VALUES('Jo','jo@doe.org')`

const name = 'Alice', email = 'alice@mail.org'
await db.exec`INSERT INTO Contact(name,email) VALUES (${name}, ${email})`

// Typed SQL fragment with named param example
const hasId = <Table extends { id:number }>(id:number|bigint) =>
    (x:Table) => $.sql($`${x.id} = $id`, { id })

const contacts = await db.all($.from(Contact).into(Contact))                // => Contact[]
const bob = await db.one($.from(Contact).where(hasId(bobId)).into(Contact)) // => Contact
const contactsCount = await db.value($.from(Contact).rowCount())            // => number
const emails = await db.column($.from(Contact).select(c => $`${c.email}`))  // => string[]
const contactsArray = await db.arrays($.from(Contact))                      // => any[][]
const bobArray = await db.array($.from(Contact).where(hasId(bobId)))        // => any[]

// Update examples
jane.email = 'jane@doe.org'
await db.update(jane)                           // Update all properties
await db.update(jane, { onlyProps:['email'] })  // Update only email
// query builder
await db.exec($.update(Contact).set({ email:jane.email }).where(hasId(jane.id)))

// Delete examples
await db.delete(jane)
await db.exec($.deleteFrom(Contact).where(hasId(jane.id))) // query builder
```


# Schema APIs
Source: https://razor-press.web-templates.io/schema

:::info
All live examples use the data models defined in [/models](/models).
:::

## Create Table

<live-preview>
class Freight {
  id = 0
  name = ''
  cost = 0.0
}
Table(Freight, {
  columns: {
    id: { type:'INTEGER', autoIncrement:true },
    name: { type:'TEXT', required:true, unique:true, index:true },
    cost: { type:'MONEY', required:true }
  }
})
db.createTable(Freight)
db.createTable(Contact)
db.createTable(Order)
db.createTable(OrderItem)
db.createTable(Product)
</live-preview>

## Drop Table

<live-preview>
db.dropTable(Contact)
</live-preview>


# SELECT Examples
Source: https://razor-press.web-templates.io/select

## Simple Queries

Simple queries can be executed directly on the litdb driver APIs, the different APIs available based on whether the
query is expected to return multiple rows, a single row, a single value, a single column, etc.

<live-preview>
const id = 1
db.all`SELECT * FROM Contact`                     // => Contact[]
db.one`SELECT * FROM Contact WHERE id = ${id}`    // => Contact
db.value`SELECT COUNT(*) FROM Contact`            // => number
db.column`SELECT name FROM Contact`               // => string[]
db.arrays`SELECT * FROM Contact`                  // => any[][]
db.array`SELECT * FROM Contact WHERE id = ${id}`  // => any[]
</live-preview>

## SELECT Query Builder

`$.from(Table)` is used to create a SELECT query builder which by default selects all known columns of the data model.

<live-preview>
$.from(Contact)
</live-preview>

## Typed Selects

When a custom select is needed you can use select function to specify the columns to select. All tables and columns
using typed references are automatically quoted.

<live-preview>
$.from(Contact).select(c => $`${c.id}, ${c.name}, ${c.email}`)
</live-preview>

## Aliases

A table alias can be specified in `$.from()`, with the `.as()` method or by using `$.ref()` to 
create a table reference.

<live-preview>
db.all($.from(Contact,'c').select(c => $`${c.id}, ${c.name}, ${c.email}`))
db.all($.from(Contact).as('c').select(c => $`${c.id}, ${c.name}, ${c.age}`))
const c = $.ref(Contact,'c')
db.all($.from(c).select(c => $`${c.id}, ${c.name}, ${c.createdAt}`))
</live-preview>

## Select a list of Properties or Columns

The `props` option can be used to select a list of properties from the data model where any aliases would be used if defined, 
whilst the `columns` option can be used to select a list of RDBMS columns from the table.

<live-preview>
db.all($.from(Contact).select({ props:['id', 'name', 'age'] }))
db.all($.from(Contact).select({ columns:['id', 'name', 'email'] }))
</live-preview>


# JOIN Examples
Source: https://razor-press.web-templates.io/joins

## Simple Join

Use `join` to create a new query builder with an additional table join. The `on` option is used to specify the join condition.

<live-preview>
$.from(Contact).join(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` }).select('*')
</live-preview>

## Custom Join Types

Use `leftJoin`, `rightJoin`, `fullJoin`, `crossJoin` to create a new query builder with a specific join type.

<live-preview>
db.all($.from(Contact).leftJoin(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` }).select('*'))
db.all($.from(Contact).rightJoin(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` }).select('*'))
db.all($.from(Contact).fullJoin(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` }).select('*'))
db.all($.from(Contact).crossJoin(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` }).select('*'))
</live-preview>

## Multiple Joins

Multiple joins can be chained together to create complex queries. A new query builder is created for each join 
that's added containing references for all tables in the query in the order they were added. 

These references can be used in `where`, `select`, `groupBy`, `orderBy` methods to reference columns from each table.

The `*join` APIs are instead passed a reference to the **previous** joined table and the **current** table, they also include
a reference to the **primary** table as the last reference (e.g. OrderItem `on:(_,i,o)`).

<live-preview>
$.from(Order)
  .leftJoin(Contact, { on:(o,c) => $`${o.contactId} = ${c.id}` })
  .join(OrderItem,   { on:(_,i,o) => $`${i.orderId} = ${o.id}` })
  .leftJoin(Product, { on:(i,p) => $`${i.sku} = ${p.sku}` })
  .where((o,c,i,p) => $`${c.id} = ${1} AND ${p.cost} > ${100}`)
  .select((o,c,i,p) => $`${o.id}, ${c.name}, ${i.qty}, ${p.name}`)
</live-preview>

## Aliases

Each joined table can be assigned an alias using the `as` option. This alias is then used to reference the table in the query.

<live-preview>
$.from(Order,'o')
  .leftJoin(Contact, { as:'c', on:(o,c) => $`${o.contactId} = ${c.id}` })
  .join(OrderItem,   { as:'i', on:(_,i,o) => $`${i.orderId} = ${o.id}` })
  .leftJoin(Product, { as:'p', on:(i,p) => $`${i.sku} = ${p.sku}` })
  .where((o,c,i,p) => $`${c.id} = ${1} AND ${p.cost} > ${100}`)
  .select((o,c,i,p) => $`${o.id}, ${c.name}, ${i.qty}, ${p.name}`)
</live-preview>

## External References

Queries can be joined on external references which can be used across multiple query builders that can be composed together 
to create complex queries that reference other queries.

<live-preview>
const [ o, c, i, p ] = [ 
  $.ref(Order,'o'), $.ref(Contact,'c'), $.ref(OrderItem,'i'), $.ref(Product,'p') ]
const recentOrder = $.from(Order,'o2')
  .where(o2 => $`${o2.contactId} = ${c.id}`)
  .select(o2 => $`MAX(${o2.createdAt})`)
db.all($.from(o)
  .leftJoin(c, $`${o.contactId} = ${c.id}`)
  .join(i, $`${i.orderId} = ${o.id}`)
  .leftJoin(p, $`${i.sku} = ${p.sku}`)
  .where`${o.createdAt} = (${recentOrder})`
  .select`${o.id}, ${c.name}, ${i.qty}, ${p.name}`)
</live-preview>

## JOIN query builder

When more flexibility is needed you can create a JOIN query builder with `$.join()` that can be added to other SELECT
query builders to create complex queries.

<live-preview>
$.from(Contact,'c')
    .join(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` })
    .join(
       $.join(OrderItem,Order,Product).as('i').leftJoin((i, o, p) => 
         $`${o.id} = ${i.orderId} LEFT JOIN ${p} ON ${i.sku} = ${p.sku}`)
    )
    .select('*')
</live-preview>

## Cache complex JOIN queries

For improved performance and to simplify complex queries, complex joins can be reused and memoized by returning isolated
cloned query builders with `clone()`.

<live-preview>
 const contactOrderItems = (() => {
    const q = $.from(Contact,'c')
        .join(Order,     { as:'o', on:(c,o) => $`${c.id} = ${o.contactId}` })
        .join(OrderItem, { as:'i', on:(o,i) => $`${o.id} = ${i.orderId}` })
    return () => q.clone()
})()
const [q1, q2, q3] = [...Array(3)].map(contactOrderItems)
const [ c, o, i ] = q1.refs
db.all(q1.where`${c.id} = ${10}`)
db.all(q2.where`${o.contactId} = ${20}`)
db.all(q3.where`${i.orderId} = ${100}`)
</live-preview>


# WHERE Examples
Source: https://razor-press.web-templates.io/where

## Simple WHERE Clauses

The `where` method is used to add a WHERE clause to the query, it's an alias for `and` which can be called multiple times 
to add multiple **AND** conditions to the WHERE clause, whilst `or` can be used to add an **OR** condition.

<live-preview>
$.from(Order)
  .leftJoin(Contact, { on:(o,c) => $`${o.contactId} = ${c.id}` })
  .where((o,c) => $`${c.id} = ${1} AND ${c.age} > ${18}`)
  .or(o => $`${o.total} > ${100}`)
  .select('*')
</live-preview>

## Array Expansion

Arrays embedded in SQL Fragments are expanded into a list of parameters, this can be used to create IN clauses.

<live-preview>
$.from(Contact).where`id IN (${[10,20,30]})`
</live-preview>

## WHERE with Subqueries

Fragments can embed other fragments where their SQL and parameters are merged.

<live-preview>
const hasPurchasesOver = (c,total) => $`EXISTS (
       SELECT 1 FROM Order WHERE o.contactId = ${c.id} AND total >= ${total})`
const inCity = (...cities) => c => $`${c.city} IN (${cities})`
const createdAfter = after => $.sql('createdAt >= $after', { after })
const olderThan = age => ({ sql:'age >= $age', params: { age } })
const q = $.from(Contact,'c')
    .where(c => hasPurchasesOver(c,1000))
    .and(inCity('Austin','Chicago'))
    .and(createdAfter(new Date('2024-01-01')))
    .and(olderThan(18))
    .and({ contains: { name:'John' } })
db.all(q)
</live-preview>

### Subqueries with Query Builders

Similarly, Query Builders and SQL Fragments can be embedded in other Query Builders to create complex subqueries.

<live-preview src="/mjs/subselect.mjs"></live-preview>

## WHERE convenience options

The `where` method can also be called with an object containing a number of convenience options to simplify creating
common queries with an object query. If needed `op` can be used to create options for a custom SQL operator.

<live-preview>
const search = {
    name: 'John',
    age: 27,
    city: 'Austin',
}
db.all($.from(Contact).where({ equals: search }))
db.all($.from(Contact).where({ notEquals: search }))
db.all($.from(Contact).where({ like: { name:'John', city:'Austin' } }))
db.all($.from(Contact).where({ notLike: { name:'John', city:'Austin' } }))
db.all($.from(Contact).where({ op: ['>=', { id:10, age:18 }] }))
</live-preview>

### LIKE convenience options

The `startsWith`, `endsWith` and `contains` options can be used to create **LIKE** conditions that match the start, 
end or any part of a string.

<live-preview>
$.from(Contact).where({ 
    startsWith: { city:'A' }, 
    contains: { email:'@gmail.' }, 
    endsWith: { name:'J' }, 
})
</live-preview>

### NULL check convenience options

Whilst the `isNull` and `notNull` convenience options can be used to create **IS NULL** and **IS NOT NULL** conditions.

<live-preview>
$.from(Contact).where({ 
    isNull: ['city', 'age'], 
    notNull: ['email'], 
})
</live-preview>

## Reset WHERE

Calling `where` with no arguments will reset the WHERE clause:

<live-preview>
$.from(Contact).where`name LIKE ${'John%'}`.where().and`id = ${1}`
</live-preview>


# GROUP BY Examples
Source: https://razor-press.web-templates.io/group-by

## Simple GROUP BY

`groupBy` works like Query Builder methods where it's called with the query's table references in the order they were added: 

<live-preview>
$.from(Contact)
    .join(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` })
    .groupBy(c => $`${c.name}`)
    .select((c, o) => $`${c.name}, SUM(${o.total}) AS Total`)
</live-preview>

## Multiple GROUP BY

Multiple group by's can be added in one or multiple `groupBy` methods:

<live-preview>
const q = $.from(Contact,'c')
    .join(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` })
    .select((c, o) => $`${c.name}, ${c.city}, SUM(${o.total})`)
db.all(q.clone().groupBy(c => $`${c.name}, ${c.city}`))
db.all(q.clone().groupBy(c => $`${c.name}`).groupBy(c => $`${c.city}`))
</live-preview>

## GROUP BY Builder

When more flexibility is needed, `$.groupBy` can be used to create a HAVING builder which can be constructed independently
of the query:

<live-preview>
$.from(Contact,'c')
    .join(Order, { as:'o', on:(c,o) => $`${c.id} = ${o.contactId}` })
    .join(OrderItem, { as:'i', on:(o,i) => $`${o.id} = ${i.orderId}` })
    .groupBy(
        $.groupBy(Contact,OrderItem)
            .add(c => $`${c.name}`)
            .add((_,i) => $`${i.sku}`)
    )
    .select((c,o,i) => $`${c.name}, ${i.sku}, SUM(${o.total}) AS total`)
</live-preview>

## Reset GROUP BY

Calling `groupBy` with no arguments will reset the GROUP BY clause:

<live-preview>
$.from(Contact).groupBy`name`.groupBy().select`name`
</live-preview>


# HAVING Examples
Source: https://razor-press.web-templates.io/having

## Simple HAVING

The `having` method can be used to filter the results of a `groupBy` query:

<live-preview>
$.from(Contact)
    .join(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` })
    .groupBy(c => $`${c.city}`)
    .having(c => $`COUNT(${c.id}) > 5`)
    .select(c => $`${c.city}, COUNT(${c.id})`)
</live-preview>

## Multiple HAVING

Multiple having's can be added in one or multiple `having` methods:

<live-preview>
const q = $.from(Contact)
    .join(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` })
    .groupBy(c => $`${c.city}`)
    .select(c => $`${c.city}, COUNT(${c.id})`)
    
db.all(q.clone().having((c,o) => $`COUNT(${c.id}) > 5 AND SUM(${o.total}) < 1000`))
db.all(q.clone()
    .having(c => $`COUNT(${c.id}) > 5`).having((_,o) => $`SUM(${o.total}) < 1000`))
</live-preview>

## HAVING Builder

When more flexibility is needed, `$.having` can be used to create a HAVING builder which can be constructed independently
of the query:

<live-preview>
$.from(Contact)
.join(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` })
.groupBy(c => $`${c.city}`)
.having(
    $.having(Contact,Order)
        .add(c => $`COUNT(${c.id}) > 5`)
        .add((_,o) => $`SUM(${o.total}) < 1000`)
).select(c => $`${c.city}, COUNT(${c.id})`)
</live-preview>

## Reset HAVING

Calling `having` with no arguments will reset the ORDER BY clause:

<live-preview>
$.from(Contact).having`name`.having().select`name`
</live-preview>


# ORDER BY Examples
Source: https://razor-press.web-templates.io/order-by

## Simple ORDER BY

Like other Query Builder methods, `orderBy` is called with the query's table references in the order they were added:

<live-preview>
$.from(Contact)
    .join(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` })
    .select((c, o) => $`${c.name}, ${o.total}`)
    .orderBy(c => $`${c.name}`)
</live-preview>

## Multiple ORDER BY

Multiple order by's can be added in one or multiple `orderBy` methods:

<live-preview>
const q = $.from(Contact)
    .join(Order, { on:(c,o) => $`${c.id} = ${o.contactId}` })
    .select((c, o) => $`${c.name}, ${c.city}, ${o.total}`)
db.all(q.clone().orderBy(c => $`${c.name}, ${c.city}`))
db.all(q.clone().orderBy(c => $`${c.name}`).orderBy(c => $`${c.city}`))
</live-preview>

## ORDER BY Builder

When more flexibility is needed, `$.orderBy` can be used to create a ORDER BY builder which can be constructed independently
of the query:

<live-preview>
$.from(Contact,'c')
    .join(Order, { as:'o', on:(c,o) => $`${c.id} = ${o.contactId}` })
    .select((c, o) => $`${c.name}, ${o.total}`)
    .orderBy(
        $.orderBy(Contact,Order)
        .add(c => $`${c.name}`)
        .add((_,o) => $`${o.total} DESC`)
    )
</live-preview>

## Reset ORDER BY

Calling `orderBy` with no arguments will reset the ORDER BY clause:

<live-preview>
$.from(Contact).orderBy`name`.orderBy().select`name`
</live-preview>


# INSERT Examples
Source: https://razor-press.web-templates.io/insert

## Simple INSERT

Insert a new entity into a table using the `db.insert` driver method:

<live-preview>
db.insert(new Contact({ name:'John', email:'john@email.com', age:27 }))
db.insert(new Product({ sku:'WIDGET', name:'Acme Widget', cost:10 }))
</live-preview>

## INSERT Multiple Rows

Insert multiple entities into a table using the `db.insertAll` driver method:

<live-preview>
db.insertAll([
    new Contact({ name:'John', email:'john@email.com', age:27 }),
    new Contact({ name:'Jane', email:'jane@email.com', age:31 })
])
</live-preview>

## INSERT Expression

When the full flexibility of SQL is needed, you can execute a SQL fragment directly:

<live-preview>
const { name, age } = { name:'John', age:27 }
db.run`INSERT INTO Contact (name,age) VALUES (${name},${age})`
</live-preview>


# UPDATE Examples
Source: https://razor-press.web-templates.io/update

## Simple UPDATE

Simple updates can be executed directly on the litdb driver APIs which will update all properties of a data model:

<live-preview>
contact = new Contact({ id:1, name:'John', email:'john@mail.org' })
db.update(contact)
</live-preview>

## UPDATE Specific Properties

For updating specific properties of a data model, the `onlyProps` option can be used:

<live-preview>
db.update(new Contact({ id:1, email:'john@mail.org' }), { onlyProps:['email'] })
</live-preview>

## UPDATE Query Builder

When more flexibility is needed you can use `$.update()` to create an UPDATE query builder:

<live-preview>
db.run($.update(Contact).set({ age:41, city:'Austin' }).where($.idEquals(1)))
const { age, city, email } = { age:41, email:'john@mail.org' }
const q = $.update(Contact)
if (age) q.set({ age })
if (city) q.set({ city })
if (email) q.set({ email })
db.run(q.where($.idEquals(1)))
</live-preview>

## UPDATE Expression

When the full flexibility of SQL is needed, you can execute a SQL fragment directly:

<live-preview>
const { id, name, age } = { id:1, name:'John', age:27 }
db.run`UPDATE Contact SET name=${name}, age=${age} WHERE id=${id}`
</live-preview>


# DELETE Examples
Source: https://razor-press.web-templates.io/delete

## Simple DELETE

Delete an entity using the `db.delete` driver method:

<live-preview>
db.delete(new Contact({ id:1, name:'John', email:'john@email.com', age:27 }))
</live-preview>

## DELETE Query Builder
    
When a custom query is needed you can use `$.deleteFrom()` to create a DELETE query builder:

<live-preview>
const yearAgo = new Date(new Date().setFullYear(new Date().getFullYear() - 1))
db.run($.deleteFrom(Order).where(c => $`${c.createdAt} < ${yearAgo}`))
</live-preview>

## DELETE Expression

When the full flexibility of SQL is needed, you can execute a SQL fragment directly:

<live-preview>
const name = 'John'
db.run`DELETE FROM Contact WHERE name = ${name}`
</live-preview>