Cotton

GitHub release (latest by date)

SQL Database Toolkit for Deno.

Well-tested
Type-safe
Supports MySQL, SQLite, and PostgreSQL
Semantic versioning

Documentation

How to use

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.table("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 of 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";

  @Field()
  email!: string;

  @Field()
  age!: number;

  @Field()
  created_at!: Date;
}

Keep in mind that you need to override the default TypeScript configration in order to use this decorator feature.

// tsconfig.json

{
  "compilerOptions": {
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true
  }
}

$ deno run -c tsconfig.json main.ts

To do CRUD operations to our model, we can use the provided method in our model:

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

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

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

To save the current model to the database, use the save method.

const user = new User();
user.email = "a@b.com";
user.age = 16;
user.created_at = new Date("1 June, 2020");
await user.save();

You also can use the insert method to create the model instance and save it to the database at the same time.

const user = await User.insert({
  email: "a@b.com",
  age: 16,
  created_at: new Date("1 June, 2020"),
});

To insert multiple records, you can simply pass an array as the parameter.

const user = await User.insert([
  { email: "a@b.com", age: 16, created_at: new Date("1 June, 2020") },
  { email: "b@c.com", age: 17, created_at: new Date("2 June, 2020") },
]);

Query Builder

Basic query

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

orWhere and notWhere

await db.table("users").notWhere("name", "kevin").execute();
// SELECT * FROM users WHERE NOT name = 'kevin';

await db
  .table("users")
  .where("name", "kevin")
  .orWhere("name", "john")
  .execute();
// SELECT * FROM users WHERE name = 'kevin' OR name = 'john';

Select columns

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

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

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

Pagination

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

Insert data

await db
  .table("users")
  .insert({
    email: "a@b.com",
    age: 16,
    created_at: new Date("5 June, 2020"),
  })
  .execute();
// INSERT INTO users (email, age, created_at) VALUES ('a@b.com', 16, '2020-06-05 00:00:00');

await db
  .table("users")
  .insert([{ email: "a@b.com" }, { email: "b@c.com" }])
  .execute();
// INSERT INTO users (email) VALUES ('a@b.com'), ('a@b.com');

Replace data

await db
  .table("users")
  .replace({
    email: "a@b.com",
    age: 16,
    created_at: new Date("5 June, 2020"),
  })
  .execute();
// REPLACE INTO users (email, age, created_at) VALUES ('a@b.com', 16, '2020-06-05 00:00:00');

Delete data

await db.table("users").where("email", "a@b.com").delete().execute();
// DELETE FROM users WHERE email = 'a@b.com';

Update data

await db
  .table("users")
  .where("email", "a@b.com")
  .update({ name: "John" })
  .execute();
// UPDATE users SET name = 'John' WHERE email = 'a@b.com';