About Liya

Liya allows a user to access three Database Systems simultaneously from a single visual interface. 

Liya can access MySQL, PostgreSQL & SQLite3 databases, all in the same way, using a simple 3-panel display — one panel for selecting the database and the table, another for editing the table structure, and the last for editing the contents of the table.

Liya can access databases in all three systems simultaneously, using multiple windows, with each window showing the contents of a single database.

And Liya will move data from one database to another, just by dragging and droppiing. If you need to copy the table structure of one table to another, even across database systems, simply drag and drop across windows. Do the same with data. You can export data from one database for backups that can be read by any of the other databases.

Getting Started

Make sure you have access to MySQL or PostgreSQL databases. Or you can use Liya to create a standalone SQLite database.

To get you started quickly, we have one-click installers for MySQL and PostgreSQL databases on Catalina and Big Sur. Just click on their respective links, on the right bar, to download.

They're 64-bit binaries. Once installed, new versions of these installers will upgrade the previous version, including bringing over existing databases, automatically.

Logging In

If you've used our installers to set up the MySQL/MariaDB or PostgreSQL database (or used a standard installation of either database), this is how you'll login to the respective databases when you start up Liya running locally on the database server machine.

Use the Database Type popup menu, below, to select MySQL or PostgreSQL.

Enter "localhost" for the Database URL. Then for MySQL, use "root" as the User Name. Use "postgres" for PostgreSQL. And leave the password blank as this is the default setup for these special admin-level users.

(Note that you can only login in as "root" or "postgres" locally on the database server machine. You can't use these accounts to login from a remote machine.)

Once you've logged in, you can review the list of databases already existing on your system by clicking on the "Select a Database..." popup menu, as shown below:

If you've logged in as "root" or "postgres", you would see a button named “Users List". Clicking this button will bring up a dialog box, as shown below:

This dialog box allows you to set a password for the "root" user, or create additional admin-level users who can log in to the system from a remote machine, unlike the "root" and "postgres" user.

You can use this dialog box to add or remove the other admin-level users, or change their passwords.

Only the "root" and "postgres" user can manage the Users List and then only from the local server machine.

Admin-level users can create databases, and create and edit tables and their field structures.

At the moment this feature is a convenience - it's meant to help a Liya user get going quickly setting up databases, designing their data structures, and enter or retrieve data from these databases.

A further feature would probably be to allow the "root" and "postgres" user to create non-admin level users, i.e., users who are only able to access the data (but not to edit the data structures) or to have restricted read-only access to the data. But this is for a future version of Liya.

Database, Tables and Field Structure

Select either an existing database or create a new one.

And then you can create a new table and the fields for each table, as shown below, which can be easily modified, even if you have data already entered into the database:

However, Liya's focus is not on having the best GUI for editing MySQL databases, or PostgreSQL databases, for that matter (though it may one day come to have that, too).

We created Liya, and the SQL database installers to go with it, so that we can (1) have a very quick way to set up database systems on freshly installed Macs, (2) design the database structures and quickly populate them with data, and then (3) get these data onto an iPhone, iPad or the Mac.

And we didn't want to be stuck with just MySQL. So we focused on a more limited subset of the SQL data tyes that can be easily mapped from one database system to another and to Objective-C data types like NSString, NSData, NSDate and NSDecimal.

It needs the addition of the time data type. Then this set should probably work well for our purposes - which is to create database-centric applications quickly and easily for the Mac, iPhone and iPad. (Update: If you're interested in how we handle timestamp fields in Liya, please read this weblog entry.)

Tech Note: The time zone used for data entry of a TimeStamp field in Liya is that of the client and not the server, for both MySQL and PostgreSQL. (For MySQL, the Liya client emits a "Set time_zone=". For PostgreSQL, this is the default). The server is then assumed to convert this to its own time zone before saving it in UTC format. If this assumption holds, then a timestamped event recorded by Liya will mark the exact same point in time, no matter where you physically move the client or the server.

What remains is to make Liya perform better as a database design tool in its own right and, possibly, make its application programming interface publicly available as an API, if we can find the time.

Accessing the Data in a Table

This is Liya accessing the data from a table of account codes in the Luca database in MySQL:

Just select a table in the list of Tables and hit the Go button. You can also choose only selected fields to display the data for by dragging fields from the list of fields and dropping them onto the Contents area under the Go button.

Once the data is displayed you can search through them, sort according to a selected field, make modifications to the data, and save them back to the database (using the File->Save menu command). You can revert your changes before you commit to saving them.

Copying Tables, Fields and Data from one Database System to Another

Just use Liya to open a window to establish a connection to another (target) database system while a window is already open to a source database system.

For example, imagine we want to copy the "act_accounts" table shown above to a PostgreSQL system.

We just login to Postgres, create a new empty database there, and literally drag the "act_accounts" table from MySQL to Postgres, as shown below :

And this what you will see in PostgreSQL (picture below). Modify it to suit your needs:

Save these modifications to the Postgres database using the Save command in Liya's File menu.

You can drag in more tables from MySQL, or you can drag in selected fields from selected tables, and you can drag over data, too, in the same way.

You can move data and database structure from any one of the three database systems supported by Liya - MySQL, PostgreSQL, SQLite - into any one of the three.

There is also a way to export and import whole databases. Use the left and right arrows next to the "Manage Users" button.

Liya is still very much a work in progress. Right now, it's a reasonably good tool for designing database systems that we've set up quickly using our SQL database installers. Also, using the same libraries that we've used to build Liya, but re-compiled to run on Arm processors, we're able to get these data quickly onto our iPhones, iPads and iPod touches. There's more we can build on top of this.

Release Log

2.0 Aug 6th 2011. Liya version 2.0 for Lion released.

2.0.1 Aug 19th 2011. Added the Minimum System Key to denote that the app will only work on Lion.

2.0.2 Aug 24th 2011. Improved support for SQLite databases. Liya now knows how to handle SQLite tables which were created with data types specified in UPPER-CASE, as well as SQLIte databases which include stat tables. Liya can now open such databases properly.

2.0.3 Nov 7th 2011. Minor bug fixes.

2.0.4 Feb 19th 2012. Fixed a problem whereby a new PostgreSQL database user, created from Liya, does not have the correct privileges granted to it to create tables in the database.

2.0.5 Mar 8th 2012. Bug fixes. Includes updated database connectors to match the latest versions of MySQL, Postgres and SQLite3. Also, Liya will now do everything with a Postgres database that it can currently do with a MySQL database. For example, Liya can now do data entry and searches for data in fields with quotes, double-quotes, apostrophes, umlauts, diacriticals, any human language (like Traditional or Simplified Chinese or Tamil), and even partial date strings, for both MySQL and PostgreSQL databases, and it will do it consistently across Mac, iPhone or iPad devices. 

2.0.6 July 1st 2012. Bug fixes for timestamp handling in PostgreSQL and SQLite3, and for Boolean handling in SQLite3. Plus numerous other bug fixes. Please keep your bug reports coming. The Search function has been changed to bring it more in line with the way Liya works on iOS devices, i.e., Liya now searches against the database, as opposed to searching against the records retrieved. This is meant to help Liya work faster across a slow Internet connection and the user can now specify a batch size for the number of records Liya should fetch each time. Finally, there is a revamped interface for sending arbitrary SQL commands to the database. And, one more thing, this is also the first version of Liya that will work on Mountain Lion, though we haven't implemented the Sandbox yet (that'll bring some restrictions and loss of features, believe me).

2.0.7 July 24th 2012. Bug fixes. Also fixed Liya so that SQLite databases (with .sqlite, .sqlite3 and .db file extensions) can simply be opened by double-clicking from the Finder, as has often been requested.

2.0.8 Aug 23rd 2012. Bug fixes for SQLite date field handling. Added ability to open connections to MySQL and PostgreSQL databases on non-standard ports. This version tested also to work with MariaDB databases. Will work on Mountain Lion.

2.0.9 Sep 2nd 2012. Added the ability for Liya to connect to MySQL, MariaDB and PostgreSQL databases via specified named sockets. Our SQL database installers for MySQL, MariaDB and PostgreSQL now come with Preference Panes that allow you to start the database from custom ports.

2.0.10 Sep 2nd 2012. Tabular data from Liya can be dragged and dropped onto a spreadsheet like Numbers but the number of columns brought over was always one short because of a stupid bug. This has been fixed in version 2.0.10. 

2.0.11 Dec 1st 2012. Fixed Liya so it'll handle SQLite fields declared as Bool. Liya will treat it as an integer, and map it to the Cocoa NSNumber data type. Also, while it was always possible to drag and drop Liya table contents data onto a spreadsheet like Numbers, this version makes it easier by turning on also copy and paste. Liya will now also show the version number of the MySQL, MariaDB or PostgreSQL database that it is connecting to in the title of the connection window.

2.0.12 Dec 1st 2012. Fixed Liya so it'll handle SQLite fields declared as Real. Also added .sql and .sqldb to the document file types (a set which includes sqlite, sqlite3 and db) that will automatically launch Liya.

2.0.13 Oct 23rd 2013. Fixed Liya so it'll handle SQLite fields declared as very long 64-bit integers. Some bug fixes for date and number formatting. This version has also been tested on Mavericks. From this point on, Liya will be a 64-bit binary, dropping support for 32-bit machines and OS X Lion.

3.0 March 8th 2014. Support for the App Sandbox in the (currently awaiting review) App Store version, plus support for SQLite dates created by Core Data, plus some bug fixes and minor interface improvements.

3.0.1 March 18th 2014. The Mac Sandbox requires the Open Panel to be used for navigating to SQLite files, and so the URL and Database Name fields, in Liya 3.0, were locked from editing to force the user to use the Open Panel. However, if the user has Command-copied a path to a SQLite file (onto the pasteboard), Liya 3.0.1 now allows the user to Control-Click on the URL field to get a contextual menu that allows the URL to be pasted into the URL field, which then helpfully opens the Open Panel, thus restoring the copy-and-paste functionality, while still meeting the requirements of the Sandbox. Also, for the non-App Store version - restored the ability to check for updates to Liya from the Help menu.

3.0.2 April 13th 2014. This a bumper bug fix release. Fixed some of the more quirky user-interface oddities, so it’ll work as modelessly (and smoothly) as I can make it. This is the first version to get iCloud support for the App Store version. You can share SQLite files in iCloud with the (soon-to-be-released) iPad version of Liya. Fixed a bug when working in Mavericks where the smart quotes get in the way of entering custom SQL commands. Also, at some point, MySQL stopped allowing the time zone to be included in a timestamp field. Updated Liya to handle this rejection more gracefully. Finally, one oft-requested feature - Liya now reports SQL command line errors when users run custom SQL commands (or when Liya has a bug). I’ve made so many changes in this version that I could have introduced even more bugs. As usual bug reports are very welcome.

3.0.3 April 29th 2014. Minor bug fixes.

3.0.4 May 29th 2014. Bug fix: Custom SQL Command field didn't allow the use of the Character Input Menu, e.g., to choose Pinyin to enter Chinese characters into the SQL query string. This has been fixed. Made MySQL connections work more like PostgreSQL connections, in terms of the choice of character set used. The character set used in each connection to the database follows that of the database. If at the point of the connection, no database has been chosen, then the connection defaults to using UTF8. New databases in Liya are created using UTF8 as the default character set. Also fixed a crash while importing and exporting databases that occurred after version 3.0.3, when Liya was made to be Sandbox-compliant.

3.0.5 October 27th 2014. The Mac App Store version of Liya on Yosemite now works with iCloud Drive.

4.1 March 14th 2018. Reduced the number of Mac OS versions Liya works with to El Capitan, Sierra and High Sierra. This version includes some minor user interface bug fixes.

5.0 February 28th 2020. This version of Liya works with MySQL 8 and PostgreSQL 11.

5.0.2 May 11th 2020. Liya calculates timestamp for SQLite from 1st January 1970, if the data is stored as an integer or real number rather than a date string.

5.0.3 November 6th 2020. This is the version of Liya that will work on both Catalina and Big Sur. It is a “fat binary” that will work on Intel-based Macs (Catalina and Big Sur) and ARM-based Macs (running Big Sur).

5.0.4 November23rd 2020. Fixed interface oddities when the app is viewed in Dark Mode.

5.0.5 February 2021. User interface bug fix. The OK button was inadvertently occluded by an invisible UI element, making it sometimes difficult to detect a mouse click. This is fixed in this version.

5.0.6 May 22nd 2024. Includes an updated SQLite version 3.45.3

6.0 September 16th 2024.Will work now with MySQL 9, including creating and alteriing user passwords using sha256.  Will work also on MacOS Sequoia. 

Mac App Store Reviews

Just for fun. Compiled by App Annie.

 

  Jan 30, 2014   by arc4random

Anonymous - Liya works great for my purposes. That is inspecting databases I create in Xcode. I would happily pay for this app.

 

  Dec 21, 2013   by  shiyongjiang

非常好 - 用来查看SQLite，简单好用.

 

  Aug 03, 2013   by MassimoImpulso

Buona app! - Fa tutto quello che serve sui database supportati (provato con MySQL e SQLLite).Ha anche la possibilità di scrivere e lanciare una query personalizzata, scritta in SQL.Forse l'interfaccia potrebbe essere migliore, questa è un'interfaccia di base, ma permette di fare tutto quello che serve dopo pochi…

   

  Dec 8, 2012   by  tkroll

Bemerkenswert umfangreich und gut! - Das Programm hat viele Funktionen, es ist verlässlich und intuitiv zu bedienen. Eine echte Hilfe. Es funktioniert wie beschrieben und hat einen wirklich großen Funktionsumfang. Vielen Dank an die großzügigen Entwickler!

 

  Nov 23, 2012   by  cissu

非常好用的sqlite软件 - 只用它连接过sqlite, 速度很快且功能实用, 可以满足我大部分的需求, 但是上一个版本有一些bug用起来让人很不舒服, 不知道这个版本是否修复了.

 

  Nov 18, 2012   by  SirFuse

Works well - I'm finding Liya very useful while developing applications that use Sqlite databases. Nice basic functionality, which is all I require and all one can ask of a free app.

 

 Nov 14, 2012  by  Biguanodon

Great app! - I was looking for something we could use to manage and run custom queries on our SQLite database both for the Mac & on our iPads. This app worked perfectly! The developer answered my query quickly and the Mac OS X version now supports drag-and-drop support from the app to Numbers! Awesome. Kudos and more power to you, Bernard! :)

 

  Aug 9, 2012   by  TnGNFC

Simple and Convinient - Simple to use, just what I need to simply check or make some quick changes on SQLite files.

 

  Jul 10, 2012   by  Callum Haywood

Very useful - Dealing with a lot of databases this application provided me exactly what I wanted; a simple to use interface that lets me browse and run queries on my databases. Also it is free which is amazing! Thanks to the developer for this excellent app.

 

Jun 26, 2012   by  Boon Yee Yuen

Excellent SQLite/Core Data database browser - Great free app for viewing the data in a iOS Core Data database. Definitely consider using this if you're an iOS developer.

 

  Mar 21, 2012   by  Paul Axten

Great time saver - Having this app to hand saved no end of time troubleshooting various core data databases. Thanks very much.

 

  Mar 19, 2012   by  Marco Mirisola

Ottima! Excellent! - Molto semplice e funzionale, fa il suo lavoro. Ma l'icona va cambiata!Very simple and functional, it does its job well . But you have to change the icon!

 

  Mar 13, 2012   by  xdracco

Liya - Great application. My only request is that you add the ability to launch Liya when I try to open a SQLite DB. This is perfect for iOS development.

 

  Feb 28, 2012   by  weiwei Yang

Beautiful free application, works well - I just wanna view a sqlite3 databasa and then found this wonderful application, it's beautiful and free. what is to be most important, it works well.

 

  Feb 20, 2012   by  Stephen Counsell

MySQL Access from Lion - Excellent App - Thanks for writing this app. It's excellent. I'd have paid something for it.

 

  Dec 17, 2011   by  Wayne Hartman

Good, but needs polish - I just needed something to edit SQLite databases for iOS development and didn't want to pay money for a solution that had more features than I needed. This app fits the bill pretty well, but could use a but more polish. For example, the app needs to indicate to the user that a custom SQL query is invalid. Also, it would be nice to be able to double click a SQLite database in Finder to launch the app. You have to launch the app first, create a new connection, and then point it to the file. Other than these items, this is a no-frills SQLite viewer that does everything I need it to do.

 

  Nov 15, 2011   by  dp.proximo

I use this app for fast looking into SQLite DB - And it works just as i expect.

 

  Nov 1, 2011   by  tim.hatch

Good, basic database viewer/editor - I downloaded this primarily to inspect and edit SQLIte databases, so I can't comment on the MySQL/PostGreSQL functionality.I had a few problems with databases created outside the app, but the author responded very quickly on each of the identified problems and version 2.0.2 incorporates all of the fixes. Th UI seems a little 'quirky' at times, but nothing really problematic. Probably the only things it doesn't do that I would like to see are (1) it won't show any VIEWS in your database (it may or may not allow you to create VIEWS by query - I haven't tried) and (2) it won't show any TRIGGERS that within the schema. But to be clear this is not a criticism - if I really felt these things were important then I would have raised them with the author and we'd probably be on v2.0.3 instead ;-)Nice app. Does most of what I need it to. Responsive author.

 

  Sep 9, 2011   by  qhdysuchx

不错 - 一款简单的数据库管理软件，值得肯定

Download
Liya.zip