Sign In

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).

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:

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.

UI Design

Auto Publish

Once your application is in use by other people, we strongly recommend turning Auto Publish OFF. See File > App Settings... for details.

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:

In benchmarking MariaDB 10.6.11 in December 2022 using a table with 1 million rows and 50 columns we found the following:

Manual Schema Changes

If you create or modify database schema outside of the AppSynergy user interface, beware of the following:

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:

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 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:

mysqldump --host=dbs-myserver.parasql.com --user=root --password=rootPwd --triggers --routines --events --master-data --single-transaction db12345 > mybackup.sql

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:

gsutil cp MyLocalFile.sql gs://db12345.storage.parasql.com/dumps/loadme.sql