# Schema

Schema is a query interface which allows you to do anything with your database schema. You often see this in your migrations code, however, you can still use it anywhere in your app as long as you connected to the database. Heavily inspired by Laravel (opens new window).

Let's take a look at this example.

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

export async function up(schema: Schema) {
  await schema.createTable("users", (table) => {
    table.id();
    table.varchar("name");
  });
}

export async function down(schema: Schema) {
  await schema.dropTable("users");
}

This is an example of a migration file. Here, we're trying to create a basic table called user. This table contains two column, an auto incremental primary key called id and classic varchar column called title.

With Schema, you can do the following things:

  • Create a table or schema
  • Alter tables and columns
  • Drop tables and columns
  • Manage constraints and indexes

# Create a table

You can use createTable method to create a new table. This two parameters, the first one is the table name and the second is a callback function. This callback function gets a TableBuilder from the parameter, which contains series of method that helps you to add columns to your table.

await schema.createTable("users", (table) => {
  table.id();
  table.varchar("email");
  table.integer("age");
  table.boolean("is_active");
  table.custom("fullname VARCHAR(255)");
  table.timestamps();
});

You can see all available columns here.

# Drop a table

You can easily drop a table by calling dropTable and pass the table name.

await schema.dropTable("users");

To drop multiple tables, use dropTables and pass an array as the parameter.

await schema.dropTables(["users"]);

# Rename a table

Use renameTable to rename an existing table.

await schema.renameTable("users", "accounts");

# Check if a table exists

Use hasTable check if a table is exists in the database.

if (await schema.hasTable("users")) {
  // `users` table exists
}

# Add column

Adding a new column to a table can be done by using the addColumn method.

const nameColumn = new ColumnBuilder("name", "varchar", 255)
  .notNull()
  .default("John");
await schema.addColumn("users", nameColumn);

# Check if a table has a column

To check if a table has a column in it, you can use hasColumn and pass the table name and the expected column.

if (await schema.hasTable("users", "name")) {
  // `users` table containts `name` column
}

# Column types

These are the available column types you can use to build your tables.

Syntax Description
id() Shortcut for bigIncrements('id').
increments('id') Auto incremental column (SERIAL in PostgreSQL).
bigIncrements('id') Big auto incremental column.
smallIncrements('id') Small auto incremental column.
varchar('name', 100) VARCHAR (text) column with a length (default 255).
text('content') A large TEXT column (LONGTEXT in MySQL).
boolean('is_active') A boolean compatible column. Tiny int in MySQL & SQLite and BOOLEAN in PostgreSQL.
integer('votes') Basic INTEGER (number) column.
bigInteger('price') BIGINT equivalent column.
smallInteger('votes') SMALLINT equivalent column.
datetime('created_at') Date and time column (TIMESTAMP in PostgreSQL).
date('created_at') DATE equvalient column.
timestamps() Creates two datetime columns called created_at and updated_at.
custom('age INTEGER') Add column by yoursel using SQL query.
foreignId('user_id', 'users') Add a foreign key to other table's primary key.