Create Databases and Tables in the Panel: One Designer, Six Engines

Intro

0:00 MongoDB, MySQL, MariaDB, PostgreSQL, SQL Server and Oracle : their databases and tables, made right in the panel. Designed column by column, the statements written for your engine and shown before they run, and the schemas of API Maker kept in step.

Tables, the usual way

0:20 Making a table usually means a SQL client for each engine, and six dialects of CREATE TABLE, ALTER statements written by hand, where one typo loses a column of data, and APIs that learn of the change when a call fails. With API Maker, one designer for six engines, the statements written for your engine and shown before they run, and the schema of every table kept in step with it.

0:46 In this video : a new database, a table designed column by column, one design on six engines, scripts of your own, a table edited, a table and its schema back in sync, collections of MongoDB, removing, safely, and renaming, with the schemas along.

A new database

1:03 Calm Home starts a loyalty programme, Calm Rewards, and it needs a database. Instance API, the PostgreSQL server of the instance postgres, and Add New Database. Its name : loyalty. PostgreSQL takes an owner, a template to copy, an encoding and a locale : empty, they are the server's own. And below, what runs on the server, written as you type. Save Database. loyalty is there, with no table yet.

1:33 Each engine asks for what its databases take. On MySQL, a new database has a character set, a collation, and runs only when there is none of its name. On Oracle, a database is a user : until it has a password, the dialog says so. The password is sent once, kept nowhere, and shown as stars ; then its tablespace, and what the user may do, as grants. And MongoDB, keeps a database only once it holds a collection : a first one is made with it.

A table, column by column

2:03 Now its first table. Design a new table : the designer reads what PostgreSQL offers, and starts with a key, id, a bigint the database numbers. Its footer always says where the design stands : here, the table needs a name. members, and a comment. Ready : the statements are written as you design. A column : customer_id, the customer of the CRM. Its type comes from the list of PostgreSQL : fifty four types in groups, each with what it holds.

2:37 bigint. Chosen. Every member has one, and no customer joins twice. Then, the points : an integer, never empty, with a default value, zero. Under More, a check : points never go below zero. The usual columns are one click : Created and updated, two dates the database fills. Indexes : one, on points, the largest first, for the leaderboard ; its name is made for you. The SQL tab : three statements, written by the server for PostgreSQL.

3:15 The table, with its key, its unique customer and its check, the index, and the comment. Nothing has run yet.

One design, six engines

3:23 The same design on every engine : a key, an email, an amount, a yes or no and two dates, all quick columns. MySQL numbers the key with AUTO_INCREMENT, sets updated_at on every change, and names its engine and character set. PostgreSQL : an identity, timestamps with their time zone, and now. SQL Server : IDENTITY one one, a named constraint for each default, and a batch that ends with GO.

3:50 Oracle : NUMBER nineteen, an identity on null, and a check that keeps a yes or no to one and zero. MongoDB : a collection which checks every document against a JSON schema, and a unique index. And MariaDB writes what MySQL writes. One design ; each database gets what it understands.

Made, with its schema

4:12 Create table : made. And API Maker asks : make its schema now? The APIs with a schema check every write against it. Its code is written from the table : the key numbered by the database, the required columns, their types, and you may change it before it is saved. Make the schema. public.members, its schema, and its twenty eight APIs, live, without a line of code.

How it works

4:39 How it works. The panel sends the design, never SQL. The Rust core of API Maker checks it against what the engine allows, and writes the statements for that engine. You see them first. They run on your database, in one transaction where the engine has them. Then the schema follows the table, and the APIs are ready.

Scripts of your own

5:00 Some tables are quicker to write than to click. Run a SQL script. Two tables and an index : rewards, and redemptions, which refers to members and to rewards. On the right, the three statements as PostgreSQL will get them, each with its line. All or nothing : PostgreSQL takes back what a failed script made. Run. The second statement fails : relation public.member does not exist, with its code and its line.

5:29 So the first one was taken back, and the third never ran : nothing changed. The missing s, and Run again : three statements, committed. Both tables are listed. Tables made by a script get their schemas from Bulk Schema Creation, as video two shows.

A table edited

5:47 The programme grows : tiers, and birthday rewards. public.members, Edit the table. The designer reads it back from PostgreSQL, every field filled : the columns, the key, the index, the check, the comment. No change yet. points becomes a bigint, marked edited. A new column, tier, of sixteen characters, never empty, with silver by default, so the members there already get one. And then, birthday, a date. updated_at removed by mistake?

6:21 It waits below with what it was, and the footer warns that a change can lose data. Put back, and it never left. The SQL tab lists the three changes and their ALTER statements, run all or none. Apply changes. Now the schema follows the table : as it is on the left, as it will be on the right, tier and birthday added, your validations and comments kept. Update the schema.

Table and schema in sync

6:47 Tables change outside API Maker too : a migration, a colleague with a SQL client. Someone added referral_code to members, and dropped birthday. The row of members carries a mark : the table and its schema differ, and the tooltip says how. A click lists it, field by field : referral_code, not in the schema ; birthday, not in the table. Each can be settled either way. referral_code first : the schema follows the table.

7:16 Only what differs changes, shown before it is saved. Saved. birthday the other way : the table follows the schema. The designer opens with birthday filled in from the schema, to review : the schema only says a date, and for a birthday a day is enough. Apply changes. The mark is gone : the table and its schema say the same again.

Collections of MongoDB

7:39 MongoDB works the same way. On catalog, Design a new collection. gift_cards. A field, code : every document has it, never twice, and also, a pattern of ten capitals or digits. Quick add, Amount, named balance, never below zero, and the two dates. In the options, only the fields above get in. Its script of the shell : createCollection with a JSON schema validator, and the unique index. Create collection, and its schema.

Removing, safely

8:13 And removing, safely. public.redemptions, Permanently Delete : the dialog says what goes, rows, indexes and keys, shows the statement, and takes the API Maker schema along. The name is typed to confirm. Removed. The database loyalty_test, where a colleague tried things : DROP DATABASE, with its connections closed first, on any version of PostgreSQL, its name, and it is gone, with its schema.

Renaming, schemas along

8:42 Names change too : at the launch, the rewards are called perks, and the database takes the name of the programme. public.rewards, Edit the table, and its new name : perks. One statement : ALTER TABLE, RENAME TO. Apply changes : the table is renamed, and its schema goes with it. public.perks has its twenty eight APIs, under the new name. The database : Edit, calm_rewards. The schemas of its tables follow it, and PostgreSQL renames a database nobody uses : the connections to it are closed first.

9:17 Update Database : renamed, two schemas moved with it. calm_rewards, with members and perks.

Good to know

9:25 Good to know. A table not made yet is kept in the browser : open the designer again, and Restore brings it back. Before it applies, the designer reads the table again : if it changed meanwhile, it asks. PostgreSQL and SQL Server take back a failed script ; MySQL, MariaDB and Oracle make each statement by itself, and the designer says so. Scripts are split as each client splits them : GO, the slash after a PL/SQL block, DELIMITER.

9:54 And with Remember my choice, schemas follow their tables without asking.

Built on standards

9:59 Built on standards. Nothing runs before you see it, and a change that can lose data is named. Schemas stay in step with their tables, so the APIs check what the tables really hold. A script written by an AI assistant runs statement by statement, each with its result, its line and its error. And removing always shows the statement and asks for the name.

Recap

10:24 To sum up. Databases made from the panel, on six engines. Tables designed column by column, the statements written for your engine. Scripts of your own, all or nothing. Tables edited, the ALTER statements shown first. Schemas in step with their tables, both ways, and renamed with them. Next : schemas in depth. API Maker. Build faster, spend smarter, deliver better.