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
// 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.
[
{
"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.
{
"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.