AppSynergy Best Practices

This document highlights what we believe are best practices when developing applications with AppSynergy.

Naming Conventions

Consistent naming patterns make databases much easier to work with – especially as they become larger and more complex.

Table Names

We recommend that you name your tables in the singular and in CamelCase (i.e. Invoice rather than Invoices and InvoiceLine rather than InvoiceLines).

Column Names

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.

Trigger Names

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 or stored function we recommend that you use a naming convention like this: TableName_MyAction. For a stored function that will return a value the action part of the name should start with the word "Get" to signify that something will be returned (e.g. Invoice_GetHtml might return the HTML for a given Invoice). For a stored procedure there is no return value so the action part of the name should describe what it does (e.g. Invoice_EmailToCustomer might email a given Invoice to a Customer).

Primary Keys

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.
• Short tables. For example, you might have a table of warehouses that only has five rows each with a 3 or 4 letter abbreviation that your company already uses.

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:

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:

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.

UI Design

• 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 tutorial videos.
• 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.

Auto Publish

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.

Modifying Tables

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.
• AppSynergy linked user accounts: 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 editing 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 G Suite account, a Gmail account, or a free Google Account that uses 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:

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 server. You can also create a backup at any time on demand to your own local computer.

Note that in the example below the options --master-data --single-transaction acquire a global read lock on all tables at the beginning of the dump for only a short period of time. This allows a dump to run against a live database and still deliver a consistent snapshot without blocking reads and write.

Recommended way to create a local backup of your database:

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.

The gsutil Command Line Utility

To copy files to or from your private cloud storage bucket you will need Google's gsutil program installed on your computer. The gsutil program is a free Google utility that you can download from here. When you configure gsutil be sure you are logged in with the same Google account you used to create your AppSynergy account (we can add additional users to your bucket upon request). When gsutil config asks you for a project id, please specify: parasql-app

Example: copy a local file to your AppSynergy cloud bucket using gsutil: