app-icon

Duskfare

Duskfare

Duskfare

Generate simple create table statements in MySQL in JSDev


Writing SQL statements for application developers can be a chore, especially when there are implementation details that you do not wish to deal with while prototyping. One of the reasons that NoSQL databases have become popular is that you can immediately start writing to them without defining a schema. The problem is that you immediately lose all the benefits of an SQL database, simply because of the time that it takes to write queries and describe the database models.

In this article, we explore how to use JSDev to write documentation for our models, and generate the SQL queries from this documentation, based on recommended presets that are automatically configured for you.

A typical MySQL create table statement has a number of components to it.

  • Table name: The name of the table that you would like to create
  • Primary Key: The value of a column that uniquely identifies a row
  • Fields: Each field has a name, data type, and also specifies whether the field is nullable.
  • Constraints: Restrictions on the data entered into fields
  • Indexes: Adding an index to a column may decrease the speed at which a record is inserted, but will greatly improve the speed when finding the record.

Warning


An example of a JSDev configuration to generate a user table. Click here to open the tool.

table_name: user
user_id: primary, index, VARCHAR(50)
first_name: index, VARCHAR(255), NULL
last_name: index, VARCHAR(255), NULL
email: index, VARCHAR(255), NULL
password: LONGTEXT, NULL
Language: JSDev

The sample output looks something like this

CREATE TABLE `user` (
`user_id` VARCHAR(50) NOT NULL DEFAULT '',
`first_name` VARCHAR(255) DEFAULT NULL,
`last_name` VARCHAR(255) DEFAULT NULL,
`email` VARCHAR(255) DEFAULT NULL,
`password` LONGTEXT DEFAULT NULL,
`created_by` VARCHAR(50) DEFAULT NULL,
`created_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_by` VARCHAR(50) DEFAULT NULL,
`updated_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`user_id`),
INDEX `IX_USE_use_id` (`user_id`),
INDEX `IX_USE_fir_nam` (`first_name`),
INDEX `IX_USE_las_nam` (`last_name`),
INDEX `IX_USE_ema` (`email`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Language: SQL

Keywords

  • primary: Adds a primary key constraint. This can be seen as a key that is unique and indexed.
  • index: Adds an index to the column to allow for a faster lookup. Good for fields such as emails or usernames.
  • unique: Adds a constraint that ensures no duplicates. Good for fields such emails or usernames.
  • NULL: States that the default value of the data should be null.


Recommendations

  • It is recommended that primary keys not labeled generically as id, but to include the table name as a prefix. e.g. using user_id instead of id in the user's table
  • Adding a suffix _fk for foreign keys as a convention when referring to an primary key from another table. e.g. using user_fk in a user_groups table to refer to the id of a user. This makes joins much easier since you will not have to manually deconflict the source of the id column, as they are already prefixed.

Explanation of design

Primary Key

In contrast to auto-increment keys, GUID inserts can be slow and take up more resources. However, they do bring some benefits that help with the maintability and scalability of the system.

  • GUIDs are unique globally (use a generation method such as UUID v4)
  • Generate the ID anywhere, and still guarantee uniqueness
  • GUIDs are not sequential, that makes the next item in the sequence difficult to guess.

Constraints

The naming convention of the constraints IX, UK, FK as the prefixes are fairly intuitive. The capitalization of the table name is meant to try to guarantee uniqueness and avoid ambguity as to what column and what key the key is generated for. The truncation of each field segment to 3 characters maximum helps to mitigate issues encountered when long table names occur within the system

Foreign Keys

By adding a suffix _fk to a column name, a foreign key is automatically generated to the table based on the prefix, and linked to the column name of format <table_name>_id. For example, adding a user_fk column to a user_groups table, would create a foreign key constraint automatically for the user table and look up the user_id column there.


This wraps up the example on how to create MySQL queries using the JSDev editor. Happy coding!

Created at: Apr 12 - 04:13 PM by Conrad Koh