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); // trueconst 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 = "[email protected]";
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: "[email protected]",
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: "[email protected]", age: 16, created_at: new Date("1 June, 2020") },
{ email: "[email protected]", age: 17, created_at: new Date("2 June, 2020") },
]);await db
.table("users")
.where("email", "[email protected]")
.where("name", "john")
.execute();
// SELECT * FROM users WHERE email = '[email protected]' AND name = 'john';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';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(5).execute(); // Skip 5 row and take 5
// SELECT * FROM users LIMIT 5 OFFSET 5;await db
.table("users")
.insert({
email: "[email protected]",
age: 16,
created_at: new Date("5 June, 2020"),
})
.execute();
// INSERT INTO users (email, age, created_at) VALUES ('[email protected]', 16, '2020-06-05 00:00:00');
await db
.table("users")
.insert([{ email: "[email protected]" }, { email: "[email protected]" }])
.execute();
// INSERT INTO users (email) VALUES ('[email protected]'), ('[email protected]');await db
.table("users")
.replace({
email: "[email protected]",
age: 16,
created_at: new Date("5 June, 2020"),
})
.execute();
// REPLACE INTO users (email, age, created_at) VALUES ('[email protected]', 16, '2020-06-05 00:00:00');await db.table("users").where("email", "[email protected]").delete().execute();
// DELETE FROM users WHERE email = '[email protected]';await db
.table("users")
.where("email", "[email protected]")
.update({ name: "John" })
.execute();
// UPDATE users SET name = 'John' WHERE email = '[email protected]';
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent