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.
TEXTdata types generally cannot be indexed
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
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;
id, but to include the table name as a prefix. e.g. using
idin the user's table
_fkfor foreign keys as a convention when referring to an primary key from another table. e.g. using
user_groupstable 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.
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.
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
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
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!