Mayfly Documentation


Mayfly is an SQL implementation in Java which is intended for unit testing. You'll be linking the Mayfly jars into your application, and then writing unit tests which create mayfly databases (as in-memory objects) and connect to them (using the JDBC database interface or slight variations thereof).

One key feature is the ability to very cheaply snapshot the database (both metadata and data) and restore from that point. This is further described at net.sourceforge.mayfly.Database#dataStore().


Download and put the supplied jar files in your classpath. You probably want to first write a simple unit test which just creates a mayfly database, connects to it, and executes a few SQL commands. It's easier to make that work first and subsequently hook in Mayfly to the rest of your application.

The file within the main download is designed to make it easy to browse Mayfly source (for example, using the "attach sources" feature found in Java development tools). Either the sources or the Mayfly documentation can help you distinguish between methods you would ordinarily call and methods which are public but would generally only be called from within Mayfly.

The source download contains everything you need to rebuild Mayfly and run the Mayfly tests. Because it contains jar files for non-Mayfly databases, it is rather large.


The easiest way to create a database and connect to it is to call net.sourceforge.mayfly.Database#Database() to create a database object and then call net.sourceforge.mayfly.Database#openConnection() to open a JDBC connection to it.

Alternately, Mayfly supplies a JDBC Driver, described in net.sourceforge.mayfly.JdbcDriver.

The most commonly used parts of JDBC are implemented, including PreparedStatement and parameters. As usual, if you get an net.sourceforge.mayfly.UnimplementedException, we'd love to hear what features you'd like to see us add (via the mayfly mailing list).


Because Mayfly is intended to be used during development, it is a Mayfly goal to detect errors as soon as possible, and give an informative message. For example, if you specify WHERE a = NULL (instead of WHERE a IS NULL), Mayfly complains. For another example, if you specify a CREATE TABLE with duplicate column names, Mayfly tells you which column was duplicated. And so on.

If you find a problem with your SQL, and Mayfly gives an unclear or less than helpful message, we consider that to be a bug. Please let us know, on the mayfly mailing list, what you were doing and what would have helped you find your problem faster.

SQL Syntax

We don't want you to have to rewrite your SQL for Mayfly versus your production database. Of course, this ideal is not 100% realized due to the wide variation in SQL dialects, and the fact that we simply haven't gotten around to adding everything to Mayfly which we'd like to. Your odds are generally better if you stay close to standard SQL.

This section describes the syntax supported in Mayfly. In this description, Square brackets indicate optional elements, vertical bars indicate choices, curly braces simply group elements, and , ... indicates that the previous element can be repeated. Parentheses stand for themselves.

You may want to read these descriptions with reference to a more comprehensive SQL guide, such as those found at Here we emphasis conciseness and identifying our SQL subset, instead of a full description of the semantics and syntax.


Text from -- to a newline is a comment.


    {column-name data-type 
        [ DEFAULT default-value ]
        [ AUTO_INCREMENT ]
        {[ NOT NULL | UNIQUE | PRIMARY KEY ]}... 
    } |
    UNIQUE(column, ...) |
    PRIMARY KEY(column, ...)
    FOREIGN KEY(column) REFERENCES table(column)
      [ ON DELETE action [ ON UPDATE action ] |
        ON UPDATE action [ ON DELETE action ]
  }, ...

At the moment the data type is mostly ignored (what matters is what data you actually put into the database). Future versions of Mayfly are expected to make more use of the data type.

Supported data types for numbers are TINYINT, SMALLINT, INTEGER and BIGINT (8, 16, 32, and 64 bit integers, respectively). DECIMAL(x,y) is supported. For strings, there is VARCHAR(size) (with TEXT as a non-standard synonym). There is partial support for DATE.

An exception to the rule that Mayfly ignores data types are auto-increment columns. Either IDENTITY or SERIAL as datatypes indicate an integer column which causes the default value to start at one and increment after every row inserted.

Foreign key actions for ON DELETE are NO ACTION, CASCADE, SET NULL, and SET DEFAULT. Foreign key actions for ON UPDATE are NO ACTION only.



Remove the table name and all its contents. Without IF EXISTS, there must be a table by that name. With the IF EXISTS (in either position), if there is no table by that name, the command does nothing, without an error.


INSERT INTO table [ ( column-name, ... ) ]
  VALUES ( expression, ... )

If expression is DEFAULT, the default value that was specified when creating the table is used.


SELECT { * | alias.* | [alias . ] column}, ...
FROM table-reference, ...
[ WHERE condition ]
[ ORDER BY { [alias .] column }, ... ]
[ LIMIT count [ OFFSET start ] ]

A table-reference is:

  tablename [ alias ] |
    { INNER | LEFT OUTER } JOIN table-reference]
    ON condition

A condition is:

  condition OR condition |
  condition AND condition |
  NOT condition |
  expression = expression |
  expression { != | <> } expression |
  expression < expression |
  expression > expression |
  expression IS [ NOT ] NULL |
  expression IN ( expression, ... )

An expression is:

  0-9... |
  'character...' |
  [ alias . ] column |
  MAX ( [ ALL | DISTINCT ] expression )
  MIN ( [ ALL | DISTINCT ] expression )
  SUM ( [ ALL | DISTINCT ] expression )
  AVG ( [ ALL | DISTINCT ] expression )
  COUNT ( { [ ALL | DISTINCT ] expression } | * )

There is also some limited support for GROUP BY and HAVING.


UPDATE table {SET column = expression }, ... [WHERE condition]

If expression is DEFAULT, the default value that was specified when creating the table is used.


A Database object can contain several schemas - each one has its own tables and they do not interact with each other. Currently, you must call the SET SCHEMA command to select which schema you are going to operate on. Support for the syntax schema.table or schema.table.column is planned for the future but is not there now.

The syntax of the schema commands is:

CREATE SCHEMA name [AUTHORIZATION DBA] [ { create-table-command } ... ]


Transactions and Threads

It is not yet safe to share a database between several threads.

Furthermore, even those aspects of transactions which are visible from within a single thread (for example, rollback), and not yet implemented.


One good reference is SQL in a Nutshell, by Kline, Kline and Hunt, published by O'Reilly. It has a detailed guide to syntax and semantics, including the SQL2003 standard and the differences among the most common SQL implementations.

A more introductory book is The Practical SQL Handbook: Using Structured Query Language, by Judith S. Bowman, Sandra L. Emerson, and Marcy Darnovsky (third edition is from 1996).

One good SQL reference/tutorial web site is SQLzoo.

For more information on Mayfly see