SQL Database Toolkit for Deno.
- Well-tested
- Type-safe
- Supports MySQL, SQLite, and PostgreSQL
- Semantic versioning
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.query(`
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();
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") },
]);
Cotton offers a simple, powerful, and database agnostic query builder. It allows you to construct SQL queries with ease. The values are replaced with a placeholder in the query string and being handled by the database to prevent SQL injection.
await db
.table("users")
.where("email", "a@b.com")
.where("name", "john")
.execute();
// query : SELECT * FROM `users` WHERE `email` = ? AND `name` = ?;
// params : ['a@b.com', 'john']
Once you connected to the database, you can access the query builder via table
method. The table
method requires you to pass your table name which you want to fetch. You can chain all the methods you need in order to add more constraints or statements to your query.
await db.table("users").not("name", "kevin").execute();
// query : SELECT * FROM `users` WHERE NOT `name` = ?;
// params : ['kevin']
await db.table("users").where("name", "kevin").or("name", "john").execute();
// query : SELECT * FROM `users` WHERE `name` = ? OR `name` = ?;
// params : ['kevin', 'john']
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`;
await db.table("users").limit(5).offset(10).execute(); // Skip 5 row and take 10
// query : SELECT * FROM `users` LIMIT ? OFFSET ?;
// params : [5, 10]
await db
.table("users")
.insert({
email: "a@b.com",
age: 16,
created_at: new Date("5 June, 2020"),
})
.execute();
// Insert multiple
await db
.table("users")
.insert([{ email: "a@b.com" }, { email: "b@c.com" }])
.execute();
await db
.table("users")
.replace({
email: "a@b.com",
age: 16,
created_at: new Date("5 June, 2020"),
})
.execute();
await db.table("users").where("email", "a@b.com").delete().execute();
await db
.table("users")
.where("email", "a@b.com")
.update({ name: "John" })
.execute();