✨ Nifty Utilities and PostgreSQL Extensions for Exposed

✨ ExposedPowerUtils ✨

Utilities and Extensions for Exposed, because while Exposed is a pretty nice framework, it tries to support a lot of SQL dialects, so a lot of advanced features that aren't supported by the SQL standard aren't supported in Exposed.

Thankfully Exposed is very extendable, allowing you to support features that it doesn't support out of the box! This repository contains a bunch of Exposed tidbits that I use on a lot of my projects.

Getting Started

repositories {
  mavenCentral()
  maven("https://repo.perfectdreams.net/")
}

Then, in your dependencies...

dependencies {
  implementation("net.perfectdreams.exposedpowerutils:ModuleNameHere:CurrentVersionHere")
}

Here's an example!

dependencies {
  implementation("net.perfectdreams.exposedpowerutils:postgres-java-time:1.0.0")
}

Modules

`:exposed-power-utils`

Contains general purpose extensions and utilities for Exposed, not bound to a specific SQL dialect.

`:postgres-power-utils`

Contains PostgreSQL extensions and utilities for Exposed.

Basic jsonb support
- You get and store data using Strings, the way how you are going to serialize and deserialize your data is up to you!
Create and update PostgreSQL enums with Java enums

Example:

ExposedPowerUtils/tests/src/test/kotlin/TestStuff.kt

Lines 27 to 52 in 80fa05d

 transaction(test) { 

 // You need to create the PostgreSQL enum before creating any tables that depend on the enum 

 createOrUpdatePostgreSQLEnum(CharacterType.values()) 

 SchemaUtils.createMissingTablesAndColumns(PlayerInfo) 

 PlayerInfo.insert { 

 // The jsonb column returns a String, the serialization and deserialization is up to you! 

 it[PlayerInfo.information] = "{\"name\":\"MrPowerGamerBR\"}" 

 it[PlayerInfo.favoriteCharacter] = CharacterType.LORITTA 

 } 

 } 

 } 

 object PlayerInfo : LongIdTable() { 

 val information = jsonb("information") 

 val favoriteCharacter = postgresEnumeration<CharacterType>("favorite_character") 

 } 

 // Keep in mind that there are some reserved types in PostgreSQL 

 // https://www.postgresql.org/docs/current/sql-keywords-appendix.html 

 enum class CharacterType { 

 LORITTA, 

 PANTUFA, 

 GABRIELA 

 }

`:postgres-java-time`

Instant timestamp(...) with PostgreSQL's TIMESTAMP WITH TIMEZONE.

Avoids the issue described in #1536, #1305, #886
Here's why you should use TIMESTAMP WITH TIMEZONE: https://www.toolbox.com/tech/data-management/blogs/zone-of-misunderstanding-092811/

Example:

ExposedPowerUtils/tests/src/test/kotlin/PostgresJavaTimeTest.kt

Lines 23 to 60 in 80fa05d

 transaction(test) { 

 SchemaUtils.createMissingTablesAndColumns(ActionLog) 

 // Exposed's "timestamp" implementation has an issue where it depends on the system's time zone. 

 // So if you inserted "Instant.now()", it would depend on your system time zone, and that's not good! 

 // Imagine if you have two machines in different time zones, and you are trying to keep an audit log, 

 // even if both codes were executed at the same time, due to time zone differences, both times would 

 // be different! 

 // 

 // If you ran the following code with Exposed's "timestamp", it will fail. 

 // 

 // That's why we use the "timestampWithTimeZone", read more at https://www.toolbox.com/tech/data-management/blogs/zone-of-misunderstanding-092811/ 

 val now = Instant.now() 

 TimeZone.setDefault(TimeZone.getTimeZone("UTC")) 

 val id1 = ActionLog.insertAndGetId { 

 it[ActionLog.timestamp] = now 

 it[ActionLog.text] = "Hello from UTC!" 

 } 

 TimeZone.setDefault(TimeZone.getTimeZone("America/Sao_Paulo")) 

 val id2 = ActionLog.insertAndGetId { 

 it[ActionLog.timestamp] = now 

 it[ActionLog.text] = "Hello from America/Sao_Paulo!" 

 } 

 TimeZone.setDefault(TimeZone.getTimeZone("UTC")) 

 val timezone1 = ActionLog.select { ActionLog.id eq id1 }.first()[ActionLog.timestamp] 

 val timezone2 = ActionLog.select { ActionLog.id eq id2 }.first()[ActionLog.timestamp] 

 assert(timezone1 == timezone2) { "The instants aren't equal! $timezone1 $timezone2" } 

 } 

 } 

 object ActionLog : LongIdTable() { 

 val timestamp = timestampWithTimeZone("timestamp") 

 val text = text("text") 

 }

Similar projects:

✨ Nifty Utilities and PostgreSQL Extensions for Exposed

Related tags

Overview

✨ ExposedPowerUtils ✨

Getting Started

Modules

`:exposed-power-utils`

`:postgres-power-utils`

`:postgres-java-time`

You might also like...

A simple NoSQL client for Android. Meant as a document store using key/value pairs and some rudimentary querying. Useful for avoiding the hassle of SQL code.

An Android helper class to manage database creation and version management using an application's raw asset files

SquiDB is a SQLite database library for Android and iOS

lightweight and minimalist ORM for Java/Android. works with SQLite & MySQL. (not actively maintained)

Android library for auto generating SQL schema and Content provider

lightweight and minimalist ORM for Java/Android. works with SQLite & MySQL. (not actively maintained)

An ORM for Android with type-safety and painless smart migrations

A simple ToDo app to demonstrate the use of Realm Database in android to perform some basic CRUD operations like Create, Update and Delete.

sql-delight example, a plugin by Square which is pure kotlin and it is useful in kmm

Owner

Exposed spring integration and code generator for dsl interface

Upsert DSL extension for Exposed, Kotlin SQL framework

Upsert DSL extension for Exposed, Kotlin SQL framework

JAKO: Just Another Kotlin Orm (PostgreSQL)

Reactive extensions for SimpleNoSQL

AndroidQuery is an Android ORM for SQLite and ContentProvider which focuses on easy of use and performances thanks to annotation processing and code generation

LiteGo is a Java-based asynchronous concurrency library. It has a smart executor, which can be freely set the maximum number of concurrent at same time , and the number of threads in waiting queue. It can also set waiting policies and overload strategies.

This is an Online Book App in which user can read and add their books on favourites fragment and also give rating on it.

A blazing fast, powerful, and very simple ORM android database library that writes database code for you.

A blazing fast, powerful, and very simple ORM android database library that writes database code for you.

	transaction(test) {
	// You need to create the PostgreSQL enum before creating any tables that depend on the enum
	createOrUpdatePostgreSQLEnum(CharacterType.values())

	SchemaUtils.createMissingTablesAndColumns(PlayerInfo)

	PlayerInfo.insert {
	// The jsonb column returns a String, the serialization and deserialization is up to you!
	it[PlayerInfo.information] = "{\"name\":\"MrPowerGamerBR\"}"
	it[PlayerInfo.favoriteCharacter] = CharacterType.LORITTA
	}
	}
	}

	object PlayerInfo : LongIdTable() {
	val information = jsonb("information")
	val favoriteCharacter = postgresEnumeration<CharacterType>("favorite_character")
	}

	// Keep in mind that there are some reserved types in PostgreSQL
	// https://www.postgresql.org/docs/current/sql-keywords-appendix.html
	enum class CharacterType {
	LORITTA,
	PANTUFA,
	GABRIELA
	}

	transaction(test) {
	SchemaUtils.createMissingTablesAndColumns(ActionLog)

	// Exposed's "timestamp" implementation has an issue where it depends on the system's time zone.
	// So if you inserted "Instant.now()", it would depend on your system time zone, and that's not good!
	// Imagine if you have two machines in different time zones, and you are trying to keep an audit log,
	// even if both codes were executed at the same time, due to time zone differences, both times would
	// be different!
	//
	// If you ran the following code with Exposed's "timestamp", it will fail.
	//
	// That's why we use the "timestampWithTimeZone", read more at https://www.toolbox.com/tech/data-management/blogs/zone-of-misunderstanding-092811/
	val now = Instant.now()

	TimeZone.setDefault(TimeZone.getTimeZone("UTC"))
	val id1 = ActionLog.insertAndGetId {
	it[ActionLog.timestamp] = now
	it[ActionLog.text] = "Hello from UTC!"
	}

	TimeZone.setDefault(TimeZone.getTimeZone("America/Sao_Paulo"))
	val id2 = ActionLog.insertAndGetId {
	it[ActionLog.timestamp] = now
	it[ActionLog.text] = "Hello from America/Sao_Paulo!"
	}

	TimeZone.setDefault(TimeZone.getTimeZone("UTC"))

	val timezone1 = ActionLog.select { ActionLog.id eq id1 }.first()[ActionLog.timestamp]
	val timezone2 = ActionLog.select { ActionLog.id eq id2 }.first()[ActionLog.timestamp]
	assert(timezone1 == timezone2) { "The instants aren't equal! $timezone1 $timezone2" }
	}
	}

	object ActionLog : LongIdTable() {
	val timestamp = timestampWithTimeZone("timestamp")
	val text = text("text")
	}