# sql-gen

> A SQL query builder, inspired by SQLAlchemy

Latest version **0.1.1** (published 2016-10-16) · BSD-2-Clause license · 0 weekly downloads

## Install

```sh
npm install sql-gen
pnpm add sql-gen
yarn add sql-gen
bun add sql-gen
```

## Health

**Score 15/100 (F)** — status: abandoned.

Positive: no vulnerabilities.

Warnings: low downloads; no types; no esm support; pre 1.0.

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 0.1.1 |
| Published | 2016-10-16 |
| First published | 2016-10-12 |
| Weekly downloads | 0 |
| License | BSD-2-Clause |
| TypeScript types | none |
| Module format | CommonJS |
| Dependencies | 1 |
| Known vulnerabilities | 0 |
| Install scripts | no |
| GitHub stars | 6 |
| Author | Michael Williamson |
| Maintainers | mwilliamson |
| Keywords | sql, query, sqlalchemy |

## Links

- npm: https://www.npmjs.com/package/sql-gen
- Repository: https://github.com/mwilliamson/node-sql-gen
- Issues: https://github.com/mwilliamson/node-sql-gen/issues
- npm.io page: https://npm.io/package/sql-gen

## Dependencies (1)

- [lodash](https://npm.io/package/lodash.md) ^4.15.0

## Alternatives

- [gamedig](https://npm.io/package/gamedig.md) — 29.3K weekly downloads
- [join-monster](https://npm.io/package/join-monster.md) — 12.8K weekly downloads
- [masked](https://npm.io/package/masked.md) — 5.5K weekly downloads
- [@comunica/actor-query-process-explain-logical](https://npm.io/package/@comunica/actor-query-process-explain-logical.md) — 4.7K weekly downloads
- [@veracity/vui](https://npm.io/package/@veracity/vui.md) — 4.6K weekly downloads

## Recent versions

- 0.1.1 (latest) — 2016-10-16
- 0.1.0 — 2016-10-12

## README

# node-sql-gen

A SQL query builder, inspired by [SQLAlchemy](http://www.sqlalchemy.org/).

## Installation

    npm install sql-gen

## Example

We can define tables using `sql.table()` and `sql.column()`:

```javascript
import sql from "sql-gen";

const Author = sql.table("author", {
    id: sql.column({name: "id", type: sql.types.int, primaryKey: true}),
    name: sql.column({name: "name", type: sql.types.string})
});
const Book = sql.table("book", {
    id: sql.column({name: "id", type: sql.types.int, primaryKey: true}),
    authorId: sql.column({name: "author_id", type: sql.types.int}),
    title: sql.column({name: "title", type: sql.types.string}),
    genre: sql.column({name: "genre", type: sql.types.string})
});
```

We can then define a query:

```javascript
const query = sql.from(Book)
    .join(Author, sql.eq(Book.c.authorId, Author.c.id))
    .where(sql.eq(Book.c.genre, "comedy"))
    .select(Author.c.name, Book.c.title);
```

We can use `compile()` to turn a query into a string and parameters,
ready to pass into a database connection:

```javascript
sql.compile(query)
//  {
//      "text": "SELECT author.name, book.title FROM book JOIN author ON book.author_id = author.id WHERE book.genre = ?",
//      "params": ["comedy"]
//  }
```

If using [node-sqlite3](https://github.com/mapbox/node-sqlite3):

```javascript
const {text, params} = sql.compile(query);
const database = new sqlite3.Database("path/to/database");
database.all(text, ...params, (error, rows) => {
    if (error) {
        console.error(error);
    } else {
        console.log(rows);
    }
});
```

## API

### `table(name, columns)`

Represent a table in a database. Takes the following arguments:

* `name`: the name of the table in the database.

* `columns`: the columns in the table.
  The keys should be the name by which you want to refer to the columns.
  The values should be the result of calling `column()`.

Returns an instance of `Table`, which has the following properties:

* `c`: the columns of the table, which can then be used in generating queries.
  For instance:
  
  ```javascript
  const Author = table("author", {
      id: sql.column({name: "id", type: sql.types.int, primaryKey: true}),
      name: sql.column({name: "name", type: sql.types.string})
  });
  sql.from(Author).select(Author.c.name)
  ```

* `as(alias)`: create a from clause for this table with an alternate name,
  as specified by `alias`. For instance:
  
  ```javascript
  const Author = table("author", {
      id: sql.column({name: "id", type: sql.types.int, primaryKey: true}),
      name: sql.column({name: "name", type: sql.types.string})
  });
  const authorAlias = Author.as("favourite_author");
  sql.from(authorAlias).select(authorAlias.c.name).where(sql.eq(authorAlias.c.id, 42))
  ```

* `primaryKey`:
  if the table has no columns defined as a primary key, this is `null`.
  Otherwise, this is an object with a `columns` property,
  which is an array of all of the columns in the primary key.

### `column(options)`

Represent a column in a table.

Options should be an object with the following properties:

* `name`: the name of the column in the database.

* `type`: the type of the column. Use a value from `types`.

* `primaryKey` (optional):
  set to `true` to mark this column as part of the table's primary key.
  Defaults to false.

* `nullable` (optional): 
  set to `false` to mark this column as `NOT NULL`.
  Defaults to true.

### `types`

* `types.int`: SQL integer type.
* `types.string`: SQL string type.

### `from(selectable)`

Create an instance of `Query` using `selectable` as the primary from clause.

### `Query`

`Query` is used to generate SQL queries.
It has the following properties:

* `join(selectable, condition)`:
  creates a `JOIN` clause onto the given selectable.

* `select(...columns)`:
  specify the columns to select.

* `distinct()`:
  add a `DISTINCT` qualifier to this query.

* `where(condition)`:
  add a `WHERE` clause.
  If there's already a `WHERE` clause, `condition` is added using `AND`.
  `condition` should be a SQL expression.

* `subquery()`:
  turn this query into a subquery that can then be selected from,
  similarly to a table.

### `createTable(table)`

Represents a `CREATE TABLE` statement.
Use `compile()` to compile it.

### `compile(query)`

Turn a query or statement into a query that can be executed.
Returns an object with two properties:

* `text`: the text of the query
* `params`: any parameters that have been generated during compilation of the query

---
_Source: https://npm.io/package/sql-gen · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._