cashapp/sqldelight

SQLDelight - Generates typesafe Kotlin APIs from SQL

SQLDelight

See the project website for documentation and APIs

SQLDelight generates typesafe Kotlin APIs from your SQL statements. It verifies your schema, statements, and migrations at compile-time and provides IDE features like autocomplete and refactoring which make writing and maintaining SQL simple.

SQLDelight understands your existing SQL schema.

CREATE TABLE hockey_player (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  number INTEGER NOT NULL
);

It generates typesafe code for any labeled SQL statements.

---

SQLDelight supports a variety of dialects and platforms:

SQLite

• Android
• Native (iOS, macOS, or Windows)
• JVM
• Javascript
• Multiplatform

MySQL (JVM)

PostgreSQL (JVM)

HSQL/H2 (JVM) (Experimental)

Snapshots

Snapshots of the development version (including the IDE plugin zip) are available in Sonatype's snapshots repository. Note that the coordinates are all app.cash.sqldelight instead of com.squareup.cash for the 2.0.0+ SNAPSHOTs.

Documentation pages for the latest snapshot version can be found here.

License

Copyright 2016 Square, Inc.

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

sqldelight's Issues

If files are renamed or packages change, delete old generated file

Update to latest intellij-gradle-plugin

Blocked by JetBrains/intellij-platform-gradle-plugin#50

Check that gradle plugin is installed from intellij plugin

I think I remember kotlin doing this where if you are in a .kt file in a project that does not have the kotlin gradle plugin applied it complains at you with an actionable dialog that will add the plugin for you. Would be nice if we did that same for .sq files

Generate mappers for queries

If a sql_stmt inside the .sq file is a query, generate a custom mapper for it

Support android build variants

.sq files should support build variants as well as main/test distinctions

Guard generated files from editing

https://devnet.jetbrains.com/thread/267895

Antlr dependency clashes with Databinding plugin.

When you use the databinding plugin with sqldelight you'll get an exception like:

java.io.InvalidClassException: org.antlr.v4.runtime.atn.ATN; Could not deserialize ATN with UUID aadb8d7e-aeef-4415-ad2b-8204d6cf042e (expected ab35191a-1603-487e-b75a-479b831eaf6d or a legacy UUID).

This is because it uses com.tunnelvisionlabs:antlr4 instead of org.antlr:antlr4. These both ship the same classes in the same package but are incompatible on the same classpath.

An issue has been filed with Google to switch at http://b.android.com/200925

Until a new android plugin is released, the following workaround can (maybe?) be used:

buildscript {
  configurations.all {
    resolutionStrategy {
      dependencySubstitution {
        substitute module('com.tunnelvisionlabs:antlr4') with module('org.antlr:antlr4')
      }
    }
  }
}

Set up travis to run tests as condition for merging a PR

Indent parsed incorrectly

Creates over-indented, mis-aligned lines after the first one.

  String FEMALES = ""
      + "SELECT *\n"
      + "    FROM users\n"
      + "   WHERE gender = 'FEMALE'";

Generated mapper type doesn't need name prefix

The class can only be accessed in subclasses of the enclosing class (since it is final itself) which means there's no chance of collision. Should just be called Mapper.

Work with kotlin

Right now theres a lot of java dependencies. The generated code is only seen by the java compiler, not the kotlin one, and I imagine a lot of the java specific intellij plugin features dont work as well (class autocomplete/clickthrough, enum typecheck)

Autocomplete package names from both Java and sqlite directories

Remove `putNull` logic.

All put methods can handle null values.

Forbid named queries in a file with a table that match a column name.

Find usages on declared sql statements

someSql =
  SELECT *
    FROM ....

someSql should be find usages-able

Adding underscore to column name did not cause IDEA plugin to re-gen sources.

Changed foobar to foo_bar and sources still had foobar in create constant, mapper types were stilled named BlahFoobarBlah. Had to cut and paste to trigger regeneration.

Switch key/value tables to using columns, not rows

Columns give proper types to each key-value pair and remove a bunch of special casing internally.

This issue seems like it basically could just be titled: remove key/value support. I'm not sure we have to do anything in the lib after this switch.

Convert to Kotlin!

Support intdef columns

see title

Views should be handled like tables

CREATE VIEW some_view AS ...

should give you the same IDE benefits of declaring a table (autocomplete, refactoring, etc.)

Ideally we resolve the query after the AS and learn about the columns it shows too. Somewhat related to #36

IllegalStateException in TableGenerator.kt:129 uncaught in plugin

Adding a .sq file outside of expected /src/main/sqldelight directory throws exception that plugin doesn't catch. Intellij/Android Studio 'helpfully' suggests disabling plugin.

Minimum fix would cause an error balloon to display. Desired fix would be to present user with action to move new file to desired file structure.

Just get the package name from the file path

Should be able to use column types for named queries.

Correctly illegal:

text TEXT NOT NULL

but incorrectly illegal:

text:
SELECT * FROM words WHERE things='text'

Use NameAllocator for fields, locals to avoid collision.

Make sqlite/ folder children appear/behave like Java packages

Currently it's like:

sqlite/
  com/
    example/
      data/
        People.sq

Should be like:

sqlite/
  com.example.data/
    People.sq

Generic types for sql column class type

see https://github.com/JakeWharton/butterknife/blob/6ab6fb5e98a9da8eeaecc4f8917f06adbc8b67a7/butterknife-compiler/src/main/java/butterknife/compiler/BindingClass.java#L483-L508

ex:

create table stuff (
  list_of_string CLASS('java.util.List<String>')
)

forbid column names that are sqlite types

Example:

create table test (
  text STRING NOT NULL
);

Figure out what to do about case mapping

_id currently becomes Id() method.

Formatting for .sq files`

Ideally queries and sql statements should format automatically with the intellij plugin

Include javadoc on generated sql statements

Solution for enums in where clauses

Right now enums are tricky to deal with, typically you have something like

females =
  SELECT *
    FROM users
   WHERE users.gender = 'FEMALE'

the enum values could be auto-completed for the user. Need to be a bit careful since updating the name of the enum would break the db (and require a migration)

Custom Mappers/Marshals should also be renamed

If I refactor a custom column then the Marshal and Mapper for that column should also be refactored.

find usages

sql code as well as java

Figure out what to do about migrations, if anything.

Working:

• You can currently define inserts, updates, and deletes in a file and use them for migrations.

Annoying:

• Table creation generates a bunch of nonsense you might not need if the table is deleted/altered by later migrations
• Table creation is restricted to one per file

Missing:

• Detection of breaking changes in the schema and warning about it
• Generation of migrations automatically when you use refactoring tools on the create table statements?

As we discussed, we could go as far as generating the onUpgrade switch automatically. We'd probably want to create something like .sqm files (for migration) which relax the one-table-per-file restriction and don't actually generate anything for them.

Lots to think about since this probably won't happen for a while.

Generate functions for sql statements with ? turned into parameters

You could use ?? for array parameters and potentially make the whole thing type safe since we know about columns.

Generate type-safe query factories

Queries with parameters (aka question marks) should get methods (not constants) generated with type-inferred parameters.

forFirstName:
SELECT * FROM user WHERE first_name = ?

public static ?? forFirstName(String first_name) { ... }

Not sure what the return type should be since the query and its args are two instances passed to SqliteDatabase. Maybe the method returns the String[] and there's still a constant for the query string? Since this won't be implemented for a while we have time to think.

Method for update clauses in .sq files

Right now update methods just ask for the where statement, one possibility is having the statement look like a normal update:

setName =
UPDATE user
   SET ...
 WHERE user.name = 'Alec'

could just generate the string

String SET_NAME = "user.name = 'Alec'";

It could also be annotated with something that says its only applicable on the user table. In fact all queries could do that which would be cool, throw a lint error if its on the wrong table or something.

Add "new file" template

Should automatically populate package and have an empty create table statement.

Tests for the intellij PSI plugin classes

Make the version of the runtime dependency embedded in the plugin dynamic.

Pull it from the gradle.properties of the enclosing project by writing it somewhere that can be embedded in the plugin.

Carets are not always accurate in reporting

The important test case is duplicateColumnName. For some reason the element passed in has a stop index which is 2 sooner than it should be.

Refactoring a table/column name should refactor java code as well

When you change the name of the table/column in a .sq file it should also references to:

Model file field name
Model file method name
Marshal class method name

Support imports like Java in .sq files

Also remove quotes around types when specified in CREATE TABLE so the types can be referenced (kind of) like Java.

import com.example.foo.Bar
// ...
balance Bar NOT NULL

Update to use released JavaPoet library.

IDEA 16 updated to support Java 8, but Android Studio (as of 2.0) is still based on IDEA 15. Once both IDEA and AS have a stable (or at least beta) release based on IDEA 16 we should point at the release artifact.

Bug: tables with columns of the same name have their references jumbled

One of the column definitions will think it references the other table's column with the same name, when really it is the root of references for columns namespaced by its tables name, same goes for the other column

Include offending identifier in collision error message.

Message would be annoyingly useless to a newcomer.

Custom class columns should be able to provide underlying column type.

Right now the runtime code lets you pick any type, but the underlying storage type is a BLOB which affects how things like sorting and filtering behave.

Test case: store java.util.Date as a unix timestamp (i.e., number) in the DB

Script for generating expected files.

When generated code fails, copy/pasting the expected files around whenever they change isn't feasible long-term. Wire uses a script to re-generate all expected files from the latest master. This makes it easy to update while also providing exact representations for PR diffs.

Syntax highlight SQL in generated strings

IDEA does this for regex, can we do it for the queries?

Support Kotlin projects

We don't want to have to do this explicitly.

Need JetBrains/kotlin#820 (or something like it) to be released.