AppSynergy Best Practices
This document highlights what we believe are best practices when developing applications with AppSynergy.
Consistent naming patterns make databases much easier to work with – especially as they become larger and more complex.
We recommend that you name your tables in the singular and in CamelCase
(i.e. Invoice rather than Invoices and InvoiceLine rather than InvoiceLines).
We recommend that you name your columns in CamelCase and sparingly use the underscore character
(i.e. Invoice_ID is recommended whereas InvoiceId is not, however InvoiceDate is recommended whereas Invoice_Date is not).
- Primary Key Columns: Should follow the pattern TableName_ID (e.g. Invoice_ID, PurchaseOrder_ID).
- Foreign Key Columns: Should have the same name as the Primary Key column they link to.
In advanced schemas where a table may have multiple foreign key references to the same table (e.g. TableX),
use a naming scheme like this: ShipTo_TableX_ID, ShipFrom_TableX_ID.
We recommend that you use the default AppSynergy naming scheme for your triggers
(e.g. Invoice_AFTER_UPDATE should be the name for an AFTER UPDATE trigger on the Invoice table).
Stored Procedure Names
When naming a stored procedure we recommend that you use a naming convention
like this: TableName_MyAction.
Stored Function Names
When naming a stored function we recommend that you use a naming convention
like this: TableName_GetSomething. The "Get" helps signify that the function will return a value.
We recommend that you use an AutoKey column for the primary key on your tables except in special circumstances
like the following:
- User table. Certain tables may have a natural primary key that is guaranteed to be unique. For example a table of users
could have its EmailAddress column as its primary key.
- State table. The natural primary key might be the state abbreviation.
We recommend that you never use Composite Primary Keys (these are Primary Keys composed of multiple columns).
If you are an advanced database developer, we suggest you instead use an AutoKey for the primary key and then add an
additional UNIQUE INDEX over the other column(s) that are required to be unique. Notwithstanding this rule, there are a handful of edge
cases where a composite primary key is best, but those examples are beyond the scope of this documentation.
You can add a unique index via Tools > SQL Console with a command like the following:
CREATE UNIQUE INDEX index_name ON table_name (col_name_1, col_name_2)
Generating AutoKey Values
AppSynergy automatically generates AutoKey values when records are added via the UI.
However, if you need to insert records via a database trigger or stored routine you will need to call the
parasql_next_val() function to generate the primary key:
INSERT INTO Customer (Customer_ID, Name) VALUES (parasql_next_val('Customer'), 'Bill Smith');
NOTE: You cannot simply leave an AutoKey column blank and have the system generate it (as you would with an AUTO_INCREMENT column).
Unlike an AUTO_INCREMENT column, an AutoKey column allows "reservations" of values for use within a modern web UI.
- Use Header Boxes. Most Record and Report objects benefit from the use of a header. Simply drag a Box object from
the palette and then drop objects (like text and buttons) inside this box. You will see this being done in various
- Use Modal Panels. A modal panel is a pop-up panel that forces the user to interact with it before they
can go back to using the rest of the application.
Modal panels are a great way of simplifying the user experience by not presenting too much information all at once.
See the Modal Panels Tutorial Video for more info.
- Delete Buttons. If you decide a Record needs a Delete button, place the Delete button inside the record –
not outside the record – so there is no ambiguity of what is being deleted.
Also, consider not having a Delete button at all, as it is often better
to design applications with something like an Inactive checkbox on the record and then use filtering to control
what is shown to the user.
Once your application is in use by other people, we strongly recommend turning Auto Publish OFF.
See File > App Settings for details.
- When Auto Publish is OFF, other users cannot see the changes you make to your application until you
click File > Publish App.
- With Auto Publish OFF you also have a longer Revert To history to undo unwanted changes.
The last 10 published versions of your application are kept, along with recent unpublished changes,
which makes undoing mistakes easier.
Be aware that if you modify a database table that an application uses, an AutoPublish will be forced automatically on that application.
For a variety of reasons listed below, we recommend that you do not modify tables that are in use
by a production application except to add a new column to the table or add an index to an existing column.
Any modification to a production database table should be carefully considered:
- Adding a new column to the end of an existing table is safe.
- Changing the Index attribute of a column is safe.
- Changing the Required attribute of a column is safe. If the column has blank values, you will need to specify a value
for the blank rows via Tools > Bulk Update before making the column required.
- Changing a table name or column name MIGHT cause problems.
When a table name or column name is changed, AppSynergy modifies every application that references that table name or column name.
However references to that table or column name in formulas, filters, triggers, stored routines, and scheduled events are not updated;
you will need to fix these references manually.
- Changing a Primary Key or Foreign Key column will LIKELY cause problems.
AppSynergy requires that you break any foreign key links, save those changes, rename, then recreate the foreign key(s).
Doing this will cause foreign key column formula references to break; you will need to fix these references manually.
- Changing the datatype of a column will LIKELY cause problems.
Most datatype changes require that AppSynergy remove any Field objects based on that column from your application(s);
you will need to add the Field object(s) back into your application(s) manually. The exceptions to this are 1) lengthening a
character column or 2) switching between numeric datatypes; both should be safe unless you have written SQL code that
expects a certain length or datatype.
- Timeouts can cause problems. If you make a change to a table that requires AppSynergy to modify a reference in an application,
and the table contains enough data that it cannot complete such change in less than 60 seconds, a timeout will occur.
In these cases the table might be modified but the application(s) references are not.
We suggest that you do not modify schema in such a way that it requires AppSynergy to modify application references
once your application is deployed into production.
In benchmarking MariaDB 10.6.11 in December 2022 using a table with 1 million rows and 50 columns we found the following:
- Adding a new column, deleting a column, renaming a column, making an existing character column longer (but not shorter),
and rearranging the order of columns is performed near instantly (1 or 2 seconds).
These operations do NOT require a complete table rebuild.
- Changing the Datatype of a column or changing its Required attribute took longer than 1 minute and the system timed out
(all operations must complete in less than 1 minute). Behind the scenes the change was completed (in a little over a minute) but no
app changes were made (so the apps that use that column could now be out of sync and generate errors).
- Adding an index to a character column took approximately 15 seconds and so therefore is safely under the 1 minute time limit.
Adding an index does NOT require a table rebuild but does require an index build.
Manual Schema Changes
If you create or modify database schema outside of the AppSynergy user interface, beware of the following:
- When AppSynergy creates or modifies tables it also maintains a variety of extended meta data that is critical to
the proper operation. You will see a set of meta data tables named parasql_* in your database.
These should never be modified except by AppSynergy or by special instructions provided by AppSynergy support.
- If you issue CREATE TABLE commands directly to the database (instead of via the AppSynergy UI) AppSynergy's extended
meta data will not be created; this limits AppSynergy functionality with these tables. You can, however, manually
add the extended meta data with special instructions provided by AppSynergy support if needed.
- If you issue ALTER TABLE commands directly to the database (instead of via the AppSynergy UI) AppSynergy's extended
meta data will not be updated. Further, your application's references to table names, column names, data types, etc.
will not be updated. This may result in applications with broken references. Issuing an ALTER TABLE command
only for the purpose of adding a column or a unique index is safe so long as no existing column definitions are changed.
- You should never change the password for any AppSynergy linked user account.
This includes root@localhost, root@%, u####@%, and db#####public@% user accounts.
Also, you should never change permissions on any AppSynergy linked user account except via the AppSynergy UI
(AppSynergy uses extended meta data to manage roles). Never create your own database user accounts directly; always
use the AppSynergy UI. If you need to create additional database user accounts for
other software to access the database directly, you should do so by creating an API Key via Tools > API Keys so
that the security permissions for that user can be managed via the AppSynergy UI.
NOTE: Do not make excess copies of your application(s). Each extra copy of an application must be opened and modified every time a table
in your database is modified. Extra copies of your application will slow down the table modification process,
potentially enough to cause it to timeout.
Security: Two-Factor Authentication
AppSynergy uses Google Accounts for authentication and SSO; we can also support SAML 2.0 if needed.
The Google Account can be a Gmail account but should be a
Google Account based on your existing business email address
(you can create one here).
In all cases the Google Account will support two-factor via SMS (least secure), the Google Authenticator app on your smartphone (more secure),
or a hardware security key that supports "FIDO U2F" like those from Yubico (most secure).
We strongly recommend that AppSynergy users with Administrator privileges use some form of
two-factor authentication on their account.
Temporarily Enabling the General Log
If you need to see a log of all database activity you can do so by enabling the general log.
Note this logs everything and can therefore get very big very quickly if left enabled.
Enabling the general log requires root access, so you will need to enable it and query the log via a third-party tool
like MySQL Workbench. The commands are simple:
SET GLOBAL general_log=1;
SELECT * FROM mysql.general_log;
After performing your diagnosis we strongly recommend that you turn off the general log via SET GLOBAL general_log=0;
Importing A Database Using Command Line Tools
Please refer to the AppSynergy Data Migration Guide
Exporting A Database Using Command Line Tools
AppSynergy automatically performs nightly backups of your entire database. You can also create a backup at any time
on demand to your own local computer.
The recommended way to create a local backup of your database is as follows:
mariadb-dump --host=dbs-myserver.parasql.com --user=root --password=rootPwd --ssl-verify-server-cert --triggers --routines --events --master-data --single-transaction db12345 > mybackup.sql
Note that in the example above the options --master-data --single-transaction acquire a global read lock on
all tables at the beginning of the backup for only a short period of time. This allows a backup to run against a
live database and still deliver a consistent snapshot without blocking reads and write.
Connecting To Your Database via MySQL Workbench
NOTE: AppSynergy has an integrated SQL Console which eliminates much of the need for MySQL Workbench.
See Tools > SQL Console for details.
You can connect directly to your AppSynergy database with MySQL Workbench.
Workbench allows you to issue any SQL command as root, which has slightly more permissions than a AppSynergy Administrator.