EASYCRUD

Appearance

Return to top
On this page

JSON Table Schema

JSON Table Schema is a JSON-based format for describing database table structure. You can check out the full types definition here.

Properties

tableName

  • Type: string

The name of the table.

alias

  • Type: string | undefined

The alias of a table. If provided, it will be used for describing the data object in the code.

columns

  • Type: ColumnDefinition[]

columns is a core part of JSON Table Schema. It describes the columns of the table. Each item in columns is a ColumnDefinition object.

indexes

  • Type: IndexDefinition[] | undefined

indexes describes the indexes of the table. Each item in indexes is a IndexDefinition object.

options

  • Type: TableOptions | undefined

options describes the extra options of the table. It is a TableOptions object.
It is designed for configuring some special features within different packages. For example, dropIfExists is used for configuring the create table SQL statement generated by @easycrud/toolkits. It is intended to provide flexiblity and extensibility, allowing for future development and third-party packages integration.

pk

  • Type: string[]

pk is a getter property. Users don't need to provide it, it will be generated automatically after standardize(). It returns the primary key(s) of the table. If the table has no primary key, it returns an empty array. Actualy, we do require a primary key for a table, it is important for the CRUD operations.

ColumnDefinition

name

  • Type: string

The name of the column.

alias

  • Type: string | undefined

The alias of a column. If provided, it will be used for describing the data object in the code.

type

  • Type: string

The type of the column. The available types are the same as the types of the database you are using. For example, if you are using MySQL, the available types are int, varchar, text, etc.
A type includes the length or range of the column is accepted, like varchar(255), int(11), decimal(10,2).

NOTE

We do consider about defining a set of basic types, but we plan to support multiple databases, currently including MySQL/MariaDB, PostgreSQL. Each database has its own types, we don't want to limit the types to a subset of the database types in this period. We may change the idea if it make things weird in practice. In nutshell, we still think about how to make it easier to use and keep the flexibility. If you have some good ideas, please let us know.

length

  • Type: number | undefined

The length of the column. It is used for the types that don't include the length in the type property, like varchar, decimal.

primary

  • Type: boolean

Whether the column is a primary key. Default is false.

autoIncrement

  • Type: boolean

Whether the column is set to auto increment. Default is false.

nullable

  • Type: boolean

Whether the column is nullable. Default is false.

default

  • Type: string | number

The default value of the column. Can be CURRENT_TIMESTAMP.

comment

  • Type: string | undefined

The comment of the column.

onUpdate

  • Type: string | undefined

The ON UPDATE clause of the column. Usually be CURRENT_TIMESTAMP.

hide

  • Type: boolean

Don't let the column used in APIs or displayed in front end. Default is false.

IndexDefinition

columns

  • Type: string[]

The columns to be indexed. Provide multiple columns for composite index.

column

  • Type: string | undefined

The column to be indexed. It will be ignored if columns is provided.

unique

  • Type: boolean

Whether the index is unique. Default is false.

primary

  • Type: boolean

Whether the index is primary. Default is false. If primary key is set here, it will override the primary property of the column.

TableOptions

database

  • Type: string | undefined

The database that the table belongs to. If users want to connect to multiple databases on server side, this property is required and should be the same as the database name in the connection string. See also dbConfig.

columnFormatter

  • Type: snake | camel | kebab | none | (col: string) => string

The method used for formatting the column name. Default is camel. It is used for generating the code. For example, if the column name is create_time, the default formatter will convert it to createTime. If you want to keep the original name, set it to none. However, alias of the column will be used first if it is provided.

Options within @easycrud/toolkits

  • For schemaToSQL: convert the JSON Table Schema to SQL statements.

Options within @easycrud/server

See more details in Server Options.

Example

json
// for PostgreSQL
{
  "columns": [
    {
      "name": "id",
      "type": "serial",
      "primary": true
    },
    {
      "name": "title",
      "type": "character varying",
      "length": 512,
      "nullable": false,
      "default": "",
      "comment": "标题"
    },
    {
      "name": "description",
      "type": "character varying",
      "nullable": false,
      "default": "",
      "comment": "描述"
    },
    {
      "name": "content",
      "type": "character varying",
      "nullable": false,
      "default": "",
      "comment": "内容"
    },
    {
      "name": "author",
      "type": "character varying",
      "length": 256,
      "nullable": false,
      "default": "system",
      "comment": "作者"
    },
    {
      "name": "create_time",
      "type": "timestamp without time zone",
      "default": "current_timestamp",
      "comment": "创建时间"
    },
    {
      "name": "update_time",
      "type": "timestamp without time zone",
      "default": "current_timestamp",
      "comment": "更新时间"
    }
  ],
  "indexes": {
    "idx_create_time": {
      "column": "create_time"
    },
    "idx_update_time": {
      "column": "update_time"
    }
  },
  "options": {
    "sql": {
      "dropIfExists": true
    }
  }
}

Unstrict Format

For convinient usage, several unstrict formats are accepeted. They will be standardized to the strict format while using within the pacakges.

ColumnDefinition[]

The most simpliest format is an array of ColumnDefinition. The file name will be used as the table name.

json
[
  {
    "name": "id",
    "type": "serial",
    "primary": true
  },
  {
    "name": "title",
    "type": "character varying",
    "length": 512,
    "nullable": false,
    "default": "",
    "comment": "标题"
  }
]

UnstrictTableObject

All properties are optional except columns. The standardize process will determine whether the object is a valid JSON Table Schema. If tableName property is not provided, the file name will be used as the table name.

json
{
  "columns": [
    {
      "name": "id",
      "type": "serial",
      "primary": true
    },
    {
      "name": "title",
      "type": "character varying",
      "length": 512,
      "nullable": false,
      "default": "",
      "comment": "标题"
    }
  ],
  "indexes": {
    "idx_create_time": {
      "column": "create_time"
    },
    "idx_update_time": {
      "column": "update_time"
    }
  }
}

JSON string

A JSON string is also accepted. The standardize process will parse and determine whether the string is a valid JSON Table Schema.

Released under the MIT License.

Copyright © 2023 QIA YANG