Cotton

SQL Database Toolkit for Deno.

Features

Database Adapters
- ✅ SQLite3 (via sqlite)
- ✅ MySQL (via deno_mysql)
- 🚧 MariaDB (wait for deno_mysql to support it)
- ✅ PostgresQL (via postgres)
🚧 Query Builder
🚧 Object-Relational Mapper
- 🚧 Model Manager
- ❌ Relationship
- ❌ Data Validators
- ❌ Hooks
❌ Migrations

Connect to database

Currently, Cotton supports SQLite3, MySQL, and PostgreSQL. To create a connection, use connect and pass the connection configurations.

import { connect } from "https://deno.land/x/cotton/mod.ts";

const db = await connect({
  type: "sqlite", // available type: 'mysql', 'postgres', and 'sqlite'
  database: "db.sqlite",
  // other...
});

You can run an SQL statement using the execute method.

await db.execute(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email VARCHAR(255),
  );
`);

Cotton provides an easy-to-use query builder which allows you to perform queries without writing raw SQL.

// Execute "SELECT * FROM users;"
const users = await db.queryBuilder("users").execute();

for (const user in users) {
  console.log(user.email);
}

However, you can still use raw SQL via query method.

const users = await db.query("SELECT * FROM users;");

for (const user in users) {
  console.log(user.email);
}

Once, you’ve finished using the database, disconnect it.

await db.disconnect();

Model

A model is nothing more than a class that extends Model.

import { Model } from "https://deno.land/x/cotton/mod.ts";

class User extends Model {
  static tableName = "users";
  static primaryKey = "id"; // optional

  email: string;
  // Other fields here...
}

To do CRUD operations to our model, we can use the database manager provided by connection. Here are some basic examples:

const user = await db.manager.findOne(User, 1); // find user by id
console.log(user instanceof User); // true

const users = await db.manager.find(User); // find all users

for (const user in users) {
  console.log(user.email);
}

Query Builder

Basic query

await db
  .queryBuilder("users")
  .where("email", "a@b.com")
  .where("name", "john")
  .execute();
// SELECT * FROM users WHERE email = 'a@b.com' AND name = 'john';

Select columns

await db.queryBuilder("users").select("email").execute();
// SELECT (email) FROM users;

await db.queryBuilder("users").select("id", "email").execute();
// SELECT (id, email) FROM users;

await db.queryBuilder("users").select("id").select("email").execute();
// SELECT (id, email) FROM users;

Pagination

await db.queryBuilder("users").limit(5).offset(5).execute(); // Skip 5 row and take 5
// SELECT * FROM users LIMIT 5 OFFSET 5;