Introduction

Splice is a native macOS database app. It is designed to be used by business users as well as DBAs and SQL professionals.

The Main Window

The main Splice window is a standard master-detail window with five components.

At the center of the toolbar at the top of the window is a status field, which provides a summary of the most recent operation performed. It also displays a progress bar while queries are being executed. If an error occurs during Splice operations, a red exclamation point will appear on the right side of the status field. You can click the exclamation point to see more information about the error.

The toolbar also contains a number of buttons that can be customized by right-clicking the toolbar and selecting the “Customize Toolbar” menu item. The following buttons are displayed by default:

Toggle Outline Navigator: hides or displays the Outline Navigator.
History Back: re-executes your previous statement, if one exists in your history.
History Forward: re-executes the next statement in your history, if one exists.
Reload: reloads the current results in the Results Table.
Transaction: provides a menu of options of enabling/disabling autocommit, committing or rolling back a transaction, and setting the current transaction isolation level.
Show Tabs: shows an overview of all the open tabs for the window and provides a user interface for adding, rearranging, or removing tabs.
Toggle Inspector Pane: hides or displays the Inspector Pane

Outline Navigator

The Outline Navigator is the pane on the left-side of the window, and provides a list of all the data sources configured in the Data Sources panel. Each data source is displayed along with an indicator whose color indicates the connection’s current status:

Gray: Not connected
Orange: Connecting…
Green: Connected
Red: Connection failure

When an outline item is expanded with the triangle next to the source name, it will automatically attempt to connect to the data source. You can also explicitly connect or disconnect from the database by right-clicking the node and selecting “Connect” or “Disconnect” from the menu.

Note: The connection indicator will occassionally flash yellow while the connection is periodically being validated. The interval for connection validation can be controlled in the General Preferences.

Once expanded, an item in the outline will display the hierarchy of the data source’s contents. This will typically be a database’s catalogs, schemas, tables, and columns, but different data sources may have different structures.

Results Table

The Results Table displays the output of the most recent successfully executed statement. This output can be contents of a table that has been double-clicked in the Outline Navigator or the results from the Filter Editor or SQL Editor.

The table is a standard macOS table view. You can selected one or more rows in the Results Table and perform operations on them, such as editing, deleting, or copying the data. Note that the table itself is not editable; data is edited using the Inspector Pane.

Results can be sorted by clicking on the table headers, which will generate a new query with the given ordering specification.

The Results Table uses a database cursor for those databases that support scrollable cursors. This means that while your table may contains millions of rows, only the first few will be displayed at any given time. Scrolling through the Results Table will dynamically load the underlying results from the database.

Inspector Pane

The Inspector Pane on the right of the application window shows the values of the currently selected rows from the Results Table. See Editing.

Query Editor

The Query Editor is the primary means of specifying queries to be executed against the current data store. See Querying.

Connecting

Splice can connect to a variety of Data Sources, which includes both network databases and file-based data files. A Splice project file can contain one or more data sources. This section discusses how to create and configure these data sources.

Adding Data Sources

To be able to work with your database, you must add it to the list of data sources. You can access this list from the “Data Sources…” item in the “View” menu. You will be presented with a dialog that allows you to choose from Splice’s built-in database types.

When you select a database, the configuration dialog will appear, allowing you enter the parameters required to create a connection. There are two categories of database: file-based databases like MS Access and SQLite, and network databases like Oracle and PostgreSQL.

For file-based databases, you will typically be asked to specify a Path to the database file, which you can select by clicking the folder icon next to the path field.

For network databases, you will typically need to fill in the Host field with the host name or IP address where the database server is running, as well as the Username and Password fields. If the database is not running on the standard port, then enter the port number into the Port field. Other fields, such as the Database field, are typically optional.

All databases will have a Title field where you can define the name of your data source as it will appear in the app, as well as a Read Only field which will allow you to specify that the connection should not permit any changes to be made to the database.

Once you have completed filling in the required information, click the Test button to initiate a test connection and verify that you are able to connect to the database. You can also browse and edit the various driver properties in the Advanced tab, but these can be typically left unchanged unless you have a non-standard configuration.

You can continue to add and configure multiple data stores in the same manner. Splice has no limit on the number of data stores you can create and connect to. Once you are finished, click the “Save” button to close the dialog and add your databases to the outline view on the left side of the main Splice window.

Passwords and File Paths

When a database has a Password field, note that the password value that you fill in will not be stored directly in the .splice project file. Instead, it will be stored in the macOS Keychain. This means that if you take a .splice project file and copy it to a different machine, the password will not be present on the destination machine, and you will need to enter it again in the data sources editor.

Creating File-based Data Source via Drag-and-Drop

File-based databases, such as MS Access or SQLite, can be added without using the Data Sources Editor. Simply drag the file from the Finder onto the icon for the Splice app. This will create a default data source for the database file without having to use the Data Sources Editor.

Browsing

Splice can browse database catalogs, schemas, tables, columns, and other aspects of the database.

Browsing Database Schemas

Once you have configured a data source (see Adding Data Sources), you will be able to see it in the Outline view on the left side of the main Splice window. You can see a list of schemas in the database by clicking on the arrow next to the data source’s name, or by simply double-clicking on the data source.

The schema list contains a list of the various catalogs or schemas that the data source contains. Some databases will contain a single schema, while others will contain many. Each schema item will contain one or more tables, which can be viewed by clicking the arrow next to the schema name.

Hiding Schema Items

Databases often contain many schemas or tables that you rarely want to use. You can hide these from view by right-selecting the schema or tables and selecting the “Hide Item(s)” menu option. If you later want to un-hide these items, you select the “Display Hidden Items” item from the “View” menu, which will cause all hidden items to be displayed temporarily. You can then right-select the items you want to be un-hidden and deselect the “Hide Item(s)” menu for those items.

Querying

To browse the complete contents of a table, you can double click the table name, and the Results Table in the center of the app window will be populated with the rows of the table. You can also view all the rows in the table by right-selecting the table name and selecting the “Issue Query” menu item.

Note: By default, Splice only fetches a subset of the available rows in the database, so while your table may contain millions of rows, only the first hundred or so will be displayed by default. As you scroll through the Results Table, Splice will dynamically populate additional rows by fetching the values from the database. The number of rows Splice fetches by default will vary from database to database, but you can specify how many you would like it to fetch with the “Row Fetch Batch Count” drop-down in the General tab of the preferences window.

Filter Editor

While browsing the complete contents of a table can be useful, it is often desirable to show just a subset of the rows by refining the selection with a filter. Splice has a built-in filter editor that enables you to issue complex queries without needing to compose the complete SQL query yourself.

To use the filter editor, you should first select the entire contents of a table by double-clicking on the table name. You can then view the filter editor by clicking the “Filter” tab of the bottom toolbar. The filter editor is a standard macOS predicate editor that allows you to define a list of critera to be displayed in the Results Table. Each row in the filted editor starts with a drop-down menu containing a list of all the columns of the given table, followed by an operation (such as “is”, “is not”, or “starts with”) and then a comparison value.

For example, if you want to return a list of all the FILM records whose TITLE column begins with “wa”, you would select the TITLE first on the left of a filter row, the “starts with” menu item in the center, and type “wa” in the field on the right. In order to send the query to the backend data source, you can select “Execute Query” from the “Data” menu, or click the right-facing play button in the Query Editor toolbar, or just use the Command-Enter keyboard shortcut for performing a query.

Note: Some operations have additional options that can apply to the filter. For example many databases can perform a case-insensitive comparison against a string field. When there are options for a comparison, they will be displayed in a drop-down menu to the right of the value element of the filter row.

You can filter using multiple criteria by clicking the “+” in the rightmost part of a filter row. Adding multiple rows will add to the compound criteria for the filter. You can control whether a filter is a logical and query by selecting “All” at the top of a filter group, or a logical or query by selecting “Any” at the top of the group.

Compound Filters

The filter editor by default shows a single group of filter rows, which can either be an AND (“All”), an OR (“Any”), or a NOT (“None”) compound expression. In addition, you can nest multiple additional compound expressions beneath the top level by holding down the option key and pressing the “+” button in the filter editor, which will create a new compound sub-group. In this way, you can construct complex queries using the filter editor.

SQL Editor

The filter editor is a useful interface for easily querying the contents of a table, but it is limited to a set of static comparison operations. The filter editor merely constructs a statement in the native query language of the data source, which is typically SQL (Structured Query Language). While Splice is designed to be a tool that can be used without having to know SQL, it can often be useful to be able to compose queries manually.

The “SQL” tab of the Query Editor below the Results Table presents a text editor that enables you to write any SQL statement that the back-end data source can understand. When the filter editor creates a query, it places the raw SQL for that query in the SQL editor, which you can then modify and re-execute as needed.

Bookmarks

If you want to save a particular query to be re-executed later, you can add it as a bookmark with the “Add Bookmark” item of the “Bookmarks” submenu of the “Data” menu. When you add a bookmark, you will be prompted to provide a title for the bookmark, which can be anything you like.

The list of existing bookmarks will be shown in the “Bookmarks” submenu. Selecting one of these bookmarks will execute that query.

Note: Bookmarks are saved and displayed on a per-datastore basis. For example, if you create one bookmark for a query you run against your PostgreSQL database and a second bookmark for a query against your Oracle database, the bookmark menu will only display the bookmarks for the particular database that is currently selected in the navigation outline view.

Further management of your bookmarks, such as renaming, reordering, or deleting bookmarks can be done using the panel displayed by selecting the “Edit Bookmarks” item of the “Bookmarks” submenu of the “Data” menu.

Exporting

You can export your results with the “Export Results” submenu of the “Data” menu. You can currently export to CSV, TSV, JSON, and SQL insert statement formats.

Note: If you currently have one or more rows selected in the Results Table when you initiate the export, only those selected rows will be exported. In order to export the entire result set, de-select any selected row before exporting.

Copying

Selecting the “Copy” item from the “Edit” menu will copy any selected rows to the clipboard. By default, rows will be copied in the TSV format, which makes them suitable for pasting into most spreadsheet programs like Numbers or Excel. You can change the default format for copying results in the “Formatting” pane of the preferences panel.

Editing

Splice has support for editing, deleting, and inserting records for data sources that support modification and whose connection is not set to be read-only.

Note: Some data sources, such as SQLite, cannot currently be modifed in Splice; support for editing these sources will be added in a future version.

Modifying Records

When selecting rows in the Results Table, the Inspector Pane on the right side of the window will be populated with the values of the rows. To modify a record, select the values you wish to change and enter the new values. Once all changes have been entered, click the “Update” button to send the changes to the database.

Adding Records

When there is no selection in the Results Table, entering values into the Inspector Pane will enable the “Insert” button. When clicked, the “Insert” button will insert the values into the table as a new row.

Note: Note all result sets dislayed in the Results Table are able to accept new row inserts. For example, if your current table shows the results if a join query between multiple tables, you won’t be able to insert a new row into that result set. Typically, you will want to insert rows into results that are derived from a single table, for example, one that was created by double-clicking a table name in the Outline Navigator.

Deleting Records

One or more rows can be selected and deleted with the “Delete” item of the “Edit” menu. A confirm dialog will be presented, which notifies you that the deletions cannot be undone.

Note: Although you cannot undo a delete, you can perform a delete in the context of a transaciton, which you can later rollback in order to revert the delete operation. See Transactions.

Transactions

By default, Splice operates in “Autocommit” mode, which means that any inserts, deletes, or updates you perform are immediately sent to the database. In some cases, you may want to start a transaction before performing multiple operations against the database in order to ensure that they are performed in a transactional “all-or-nothing” atomic operation.

To begin a transaction, disable the “Autocommit Transactions” item of the “Transactions” sub-menu of the “Data” menu. Subsequent operations will then take place in the context of a transaction, which can either be saved with the “Commit” menu item, or discarded with the “Rollback” menu item.

Note: Not all database configurations support transactions. For example, if you are modifying a MySQL MyISAM table, then transactions will not be used, and you will be unable to rollback any changes you make to the table.

Splice Documentation