# Query Builder

Cotton offers a simple, powerful, and database agnostic query builder. It allows you to construct SQL queries with ease.

Once you have established a connection, you can now use the query builder via table method.

const db = connect({
  type: "sqlite",
  // other options...
});

const users = await db.table("users").execute(); // SELECT * FROM `users`;

for (const user of users) {
  console.log(user); // { id: 1, email: 'a@b.com', ... }
}

The table methods takes only one argument, which is the name of the table you want to fetch. This method returns a QueryBuilder instance, which contains more methods to construct your query. You can chain all these methods you need in order to add more constraints or statements.

// SELECT * FROM `users` WHERE `id` = ?;
const users = db.table("users").where("id", 1).execute();

As you can see, the values are replaced with a placeholder in the query string and being handled by the database to prevent SQL injection.

If you just want to get the SQL query string and don't want run the it, you can end the statement with toSQL instead of execute.

const { text, values } = db.table("users").where("id", 1).toSQL();
console.log(text); // "SELECT * FROM `users` WHERE `id` = ?;"
console.log(values); // [1]

# WHERE

One of the most common thing people do in SQL query is using WHERE clause to filter the result.

Filtering records through WHERE clause is one of the most common thing people do with SQL query. In Cotton, you can easily add where clause of any kind by simply using where method.

db.table("users").where("id", 1);

The first parameter is the column name, and the second is the expected value. By default, it will use the = operator which will check whether the value is equal. You can customize the query operator by using Q helper to customize the SQL expressions.

db.table("users").where("id", Q.gt(1)); // SELECT * FROM `users` WHERE `id` > 1;
db.table("users").where("id", Q.in([1, 2, 3])); // SELECT * FROM `users` WHERE `id` IN (1, 2, 3);
db.table("users").where("id", Q.between([1, 5])); // SELECT * FROM `users` WHERE `id` BETWEEN 1 AND 5

These are the valid expressions you can access through Q utility.

Syntax	SQL Equivalent	Description
`Q.in([1, 2, 3])`	`IN`	The value is one of the given values
`Q.notIn([1, 2, 3])`	`NOT IN`	The value is not one of the given values
`Q.between(5, 10)`	`BETWEEN`	The value (number) is between two numbers
`Q.notBetween(5, 10)`	`NOT BETWEEN`	The value (number) is between two numbers
`Q.like('%john%')`	`LIKE`	LIKE operator
`Q.notLike('%john%')`	`NOT LIKE`	NOT LIKE operator
`Q.ilike('%john%')`	`ILIKE`	ILIKE (case-insensitive) operator
`Q.notIlike('%john%')`	`NOT ILIKE`	NOT ILIKE (case-insensitive) operator
`Q.eq('a@b.com')`	`=`	Is equal to
`Q.neq('a@b.com')`	`!=`	Is not equal to
`Q.gt(7)`	`>`	Greater than
`Q.gte(7)`	`>=`	Greater than equal
`Q.lt(7)`	`<`	Lower than
`Q.lte(7)`	`<=`	Lower than equal
`Q.null()`	`IS NULL`	Is the value null
`Q.notNull()`	`IS NOT NULL`	Is the value not null

Currently, the valid values are boolean, string, null, number, and Date.

query
  .table("users")
  .where("email", "a@b.com")
  .where("age", Q.gte(16))
  .where("is_active", true)
  .where("birthday", Q.lt(new Date("7 July, 2020")));

Sometimes you want to exclude or some records that match given conditions. For that, you can use not.

query.table("users").not("is_active", true);

Or, if you want to find records if one of the conditions are true, use or.

query.table("users").where("name", "John").or("name", "Jane");

# SELECT

By default, query builder will select every single columns in the table with *. However, you can choose which columsnt to select in a query by calling select.

db.table("users").select("email");

To select multiple columns, you can either pass multiple strings to the parameter or chain it multiple times.

db.table("users").select("email", "age", "is_active");

// Alternatively...
db.table("users").select("email").select("age").select("is_active");

If you want to select only unique values, you can enable distinct select by using distinct method.

db.table("users").select("email").distinct();

# GROUP BY & HAVING

Adding a GROUP BY query can be done by using groupBy.

db.table("users").groupBy("category");
db.table("users").groupBy("users.category"); // explicit table name

Most of the time, GROUP BY expressions are paired with HAVING to filter the records, and here is how you can do that:

db.table("users").groupBy("category").having("is_active", false);

# ORDER

You can sort a column using order.

db.table("users").order("age");

By default this will sort in ascending order. You can change this by passing the second parameter.

db.table("users").order("age", "DESC"); // or ASC

To sort multiple column, you can chain this method as many as you want.

db.table("users").order("age", "DESC").order("created_at");

# COUNT

You can count how many records that match given conditions by using the count method.

db.table("users").count("is_active");

Count multiple columns by passing an array as the argument.

db.table("users").count(["is_active", "is_banned"]);

To specify an alias for the count result, you can pass the second parameter.

db.table("users").count("is_active", "a");

Use countDistinct to add distinct expression inside your count statement.

db.table("users").countDistinct("is_active", "a");

# OFFSET & LIMIT

Typically, pagination can be done in SQL by using limit and offset. Limit is the maximum number of record to return, and the offset is the number of records to skip. Here is an example.

db.table("users").limit(5).offset(10);

# INSERT / REPLACE

To insert a new record, use insert.

db.table("users").insert({ email: "a@b.com", age: 16 });

To insert multiple records in a single query, you can pass an array instead.

db.table("users").insert([
  { email: "a@b.com", age: 16 },
  { email: "b@c.com", age: 17 },
  { email: "c@d.com", age: 18 },
]);

Another way to insert a record is by using replace. It will look for PRIMARY and UNIQUE constraints. If something matched, it gets removed from the table and creates a new row with the given values.

db.table("users").replace({ email: "a@b.com", age: 16 });

db.table("users").replace([
  { email: "a@b.com", age: 16 },
  // ...
]);

# UPDATE

To perform update, you need to chain update method and pass the values you want to update. The value parameter is a key-value pair which represents the column name and it's value. This method can be chained with other constraints such as where, not, or, limit, etc.

db.table("users").where("id", 1).update({ email: "a@b.com" });

# DELETE

The only thing you need to do to perform DELETE query is by adding delete method to the query builder.

db.table("users").where("id", 1).delete();

# RETURNING

Returning is a statement that typically used in INSERT or REPLACE query. Note that this feature only works in PostgreSQL. However, you can still build this query in MySQL or SQLite connection.

db.table("users").insert({ email: "a@b.com" }).returning("id", "email");

← Connection Transactions →