This content originally appeared on DEV Community and was authored by Thomas Hansen
When creating an SQL database you'll have to mess around with complex CREATE TABLE and ALTER TABLE statements. This is time consuming, requires a lot of searching for the correct syntax, and not the optimal way to create a database. At Aista we decided to fix this by creating a visual database designer, allowing you to graphically design your database, and easily see the relationships between tables and fields. Below is a screenshot of parts of the Chinook database for you to see how this works.
The database designer works with SQLite, MySQL, SQL Server and PostgreSQL, and allows you to create and drop tables, fields, foreign keys, and everything needed to create complex and rich SQL database schemas. In the video below I am demonstrating the concept.
Automatic migration scripts
Another problem you're faced with when you've got a system that's already put into production is to create migrate scripts. This is a time consuming job, and also a source of bugs and errors. In our SQL database designer we've turned this literally into a checkbox, where you click the checkbox, and Magic does the rest.
If you turn on "Auto migrate" Magic will automatically create migrations scripts for you, that are executed sequentially as you install or update the module.
Automatically create the database
In addition to automatic migration scripts, Magic will give you the raw SQL DDL and automatically wrap it into a module for you, and ensure the database is created as the module is installed. This is useful if you're for instance creating your database in some sort of development environment, and you need to ensure the database is re-created the exact same way in production. This is illustrated below.
If you click the above "Export to module" button, Magic will automatically create a module folder for your database, with the same name as your database, create a "magic.startup" folder, and make sure your SQL DDL is executed as the module is installed. After installation, your migrate scripts will be executed sequentially.
Creating foreign keys
SQL Server, MySQL and PostgreSQL allows you to ALTER TABLE and add foreign key references after a field has been created. This is also possible in the SQL Designer in Magic. However, SQLite, which is the default database you're given as you signup for a cloudlet cannot do this. You therefor need to create the field and the foreign key at the same time. Below is a screenshot of how this is accomplished.
In general SQLite is a bit restrictive in regards to what DDL features it supports. Another example is that SQLite doesn't allow for deleting fields which are foreign keys. However, if you have an existing MySQL, SQL Server or PostgreSQL database somewhere, you can easily add a connection string to this database in your "Configuration" menu item. I go through this process in the above YouTube video if you're interested in the process. The most important thing to remember is to make sure you whitelist the IP address of your cloudlet. You can find this on your dashboard, illustrated below.
As you're done designing your database, you can of course automatically wrap your database into CRUD endpoints as illustrated in a previous article. If you want to signup for a free cloudlet and play around with the SQL designer, you can find a link below allowing you to register an account, and create a free cloudlet. We're still technically in BETA, and hence interested in feedback, but we intend to enter general availability in mid August 2022.
This content originally appeared on DEV Community and was authored by Thomas Hansen
Thomas Hansen | Sciencx (2022-07-20T11:31:59+00:00) Design your SQL database visually. Retrieved from https://www.scien.cx/2022/07/20/design-your-sql-database-visually/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.