vlcn-io / typed-sql Goto Github PK
View Code? Open in Web Editor NEWLicense: MIT License
License: MIT License
Previously discussed here: #4 (review)
We need to think on the ways to implemented prepared statements in typed-sql
. The good API should be:
Here I want to explicitly discuss prepared statements in the executional part of typed-sql
. Meaning if a user only uses query generation, they can already do prepared statements the way they want:
const query = sql`...`;
db.prepare(query.sql).all(query.params); // better-sqlite3 example
We want to provide an opinionated way to prepare and run SQL queries that makes common use cases simple and complex cases possible.
Given that we need to prepare a statement to execute it, we can save this work to not be done twice.
const query = sql`...`;
const data = await query; // prepares & executes
// ...
const data = await query; // just executes
This satisfies simple requirement as there will be no difference in code with prepared statements vs without them from user's perspective. To satisfy flexibility, an advanced user might explicitly request preparing a statement:
const query = sql`...`;
await query.prepare();
// ...
const data = await query; // just executes
In this approach prepared statements are tied to their query and its scope.
{
const query = sql`SELECT * FROM foo`;
const data = await query; // prepares & executes
}
{
const query = sql`SELECT * FROM foo`; // same query
const data = await query; // prepares (again) & executes
}
This assumes that better-sqlite3
or whatever provider is used does the same. Currently I found no evidence of this being the case. sqlite3_finalize
should be called to free the memory from a prepared statement. From looking at the code I've found that it is called only when using exec
, closing the database or incorrectly suppling multiple queries. So, maybe SQLite does some kind of automatic cleanup? Or maybe the memory impact from the prepared statements is so negligible it can be overlooked? If this is the case, the next approach might be favoured over this one.
Another thing we can do is to maintain the cache map of all the used queries. This way we can quickly lookup a correct prepared statement from the cache to be used across multiple scopes.
{
await sql`SELECT ${1}`; // prepares and caches as `SELECT ?`
}
{
await sql`SELECT ${2}`; // finds `SELECT ?` is cache, quickly executes
}
Let's look at a common way people write their DB functions:
// ...
async function getUserByID(id: number) {
return await sql`SELECT * FROM users WHERE id = ${id}`.then(x => x[0]);
}
// ...
Note, that here the query is always in its own scope and is recreated on every call. Putting stuff in the global scope can get really ugly really fast.
const getUserByIDQuery = sql`SELECT * FROM users WHERE id = ${/* what do we put here? */}`; // this one will be discussed below
async function getUserByID(id: number) {
return await getUserByIDQuery.then(x => x[0]);
}
In this case having a global cache that is handled internally by typed-sql
can bring huge benefits.
What shall we do to satisfy efficient requirement then? If the cache is global when (or should at all) we evict it? Maybe LRU or MFU or similar approaches would be appropriate here?
Given the current state of JS, observable and reactive patterns are everywhere and for a good reason. We need to think how we can integrate this behaviour with query parameters. This needs to be framework/library agnostic, yet we don't want to write our own RxJS just for this features.
I think we should integrate with the most common observable interfaces without implementing our own (similar to what we do with coercers). Some implementations we should definitely consider supporting: Solid Stores, Svelte Stores, RxJS observables, plain JS functions and maybe there is something similar in React?
const name = observable("Alice"); // this is pseudo-function just for demo purposes
const query = sql`SELECT * FROM users WHERE name = ${name}`;
await query; // returns Alice's data
//...
name.set("Bob"); // this may be triggered somewhere in UI
//...
await query; // next call to the query returns Bob's data
This declarative approach would play much nicer than the imperative call to the .bind
function in many modern scenarios. We will support .bind
though. The observables would be in addition to that.
So, @tantaman, what do you think? Maybe we should invite more people to discuss this?
count
should be handled but count
is returning an UNSPECIFIED
type rather than INTEGER
e.g.,
SELECT count(*) FROM foo;
before we do anything from #16 , this is a quick win
Can't seem to recover when a schema or query are in error in the same file
In Strut I have a package with schemas which is shared between client and server.
typed-sql doesn't seem to be picking this up when schema changes are made in the shared package.
SQLite supports type affinities with CREATE TABLE
:
Example Typenames From TheCREATE TABLE Statement or CAST Expression | Resulting Affinity | Rule Used To Determine Affinity |
---|---|---|
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 | INTEGER | 1 |
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB | TEXT | 2 |
BLOB no datatype specified | BLOB | 3 |
REAL DOUBLE DOUBLE PRECISION FLOAT | REAL | 4 |
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME | NUMERIC | 5 |
When I use type affinities in SQLite:
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE "artists"
(
ArtistId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Name NVARCHAR(120)
);
It generates invalid TypeScript types:
export const generatedSchema = schema<{
sqlite_sequence: {
name: any | null;
seq: any | null
};
"artists": {
ArtistId: number;
Name: NVARCHAR | null
};
}>;
Database originally from https://www.sqlitetutorial.net/sqlite-sample-database/
Id_of for keys of record types? Similar to what you generated for Aphrodite (https://aphrodite.sh) models.
Defining types like:
Then referencing them like:
Causes crappy (unreadable!) errors and mouse-overs:
See related typescript issue: microsoft/TypeScript#31940
create table t1 (a int);
SELECT t1.a FROM t1
creates the type:
{
t1 a: int | null
}
The key is incorrect. It should just be a
CREATE TABLE foo (a int, b int);
CREATE TABLE bar (a string, b string);
SELECT * FROM foo JOIN bar
returns the type:
{
a: string,
b: string
}
it should raise an error about ambiguous colum overlap from foo and bar.
When not using #18 and returning objects, we should force aliases against expressions that are not names or identifiers.
E.g.,
SELECT count(*) FROM foo
count(*) should be aliased rather than keyed as count(*)
Users may want to select relations (arrays or arrays) rather than objects.
I.e.,
SELECT * FROM foo -> [[1,2,3],[4,5,6],...]
vs
SELECT * FROM foo -> [
{ a: 1, b: 2, c: 3}, ...
]
Seems like we sometimes only generate a single fix at a time before applying and saving.
It would be great if typed-sql included a migrations runner. I like how kysely handles migrations, but there are many more examples out in the wild:
Strut requires ID types to be strongly typed.
E.g.,
const slideId: ID_of<Slide> = ...;
const deckId: ID_of<Deck> = ...;
SQLite lets us put arbitrary type hints into the column definition but this breaks down once we hit syntax like <, {, [
and so on.
We can get around this via a string literal type hint.
Current implementation generates this:
From this definition:
We just need to lift it out of the surrounding quotes and we're g2g.
When linting we need to generate schema types before query types.
When modifying a schema, we need to regen all query types across all files using that schema.
Shipping our own binary + watcher task would enable this. We can follow a similar architecture to Meerkat.
Generating types for composed template strings is going to be fun ๐
If you're up for it, we'll need to work on converting a string like the above to something we can pass to the SQL type generator.
So:
SELECT ${column} FROM ${table} ${condition} OR a=${"42"}
should be temporarily transformed to:
SELECT "a" FROM "a" WHERE a=? OR a=? OR a=?
at compile time and passed to the type-gen package. The TypeScript Compiler APIs let you inspect all the fragments that make up a template string and pull their type information. That should provide enough information to do the transform given sql.column("a")
; would return a literal column type of "a". Same with table.
Values we can ignore (at least to start. Balues in non-value positions would pose a problem) and substitute ?
in their place.
We'd do this in a future PR.
Originally posted by @tantaman in #4 (comment)
We'd need a LSP and TypeScript plugin to provide this inside of template tags.
Example from styled components:
https://github.com/styled-components/typescript-styled-plugin
We can leverage our existing type-gen
package as a reference.
These should be Str_of or Blob_of.
Execution decorators can deserialize the result and lift T?
Being able to define the schema is great! It would be nice to have the option to generate it too.
The node script from my project:
import Database from "better-sqlite3";
import fs from "fs-extra";
async function start() {
const db = new Database("chinook.db", {
fileMustExist: true,
});
db.pragma("journal_mode = WAL");
const statement = db.prepare("SELECT sql FROM sqlite_schema;");
const rows = statement.all() as { sql: string }[];
const schema = rows
.map((row) => row.sql)
// sql uses "\r\n"
.join(";\r\n\r\n")
// normalize to "\n"
.replace(/\r\n/g, "\n")
// remove type affinities
.replace(/NVARCHAR/g, "TEXT")
.replace(/DATETIME/g, "TEXT")
.replace(/NUMERIC/g, "REAL")
// remove braces
.replace(/\[/g, "")
.replace(/\]/g, "")
// remove quotes
.replace(/\"/g, "");
const template = `import { schema } from "@vlcn.io/typed-sql";
export const generatedSchema = schema\`
${schema}
\`;
`;
await fs.outputFile("./src/generated/schema.ts", template);
}
start();
we generate result types but if there are bind slots we should get types for those too!
The actual type name is ExtractResultType
, while the docs reference ResultType
. Which is the preferred naming convention?
The generated code does not play nicely with prettier formatting rules. It would be great if the generated types could match prettier rules.
Perhaps the cli could call prettier on the generated code before writing them to the file?
Just a list of steps encountered when installing locally:
Mac OS
Git
git clone [email protected]:username/typed-sql.git
typed-sql
git checkout -b branch-name
Ensure wasm-pack
is installed
brew install wasm-pack
Ensure rust is installed
cd packages/type-gen/
cargo build
error: Unable to update registry crates-io
, then
mkdir ~/.cargo/
touch ~/.cargo/config
[net]
git-fetch-with-cli = true
./build.sh
Error: wasm32-unknown-unknown target not found in sysroot
, then
rustup target add wasm32-unknown-unknown
./build.sh
againcd ../../
Ensure the a version of Node.js >= 19 is installed
npm install -g pnpm
pnpm i
pnpm -r build
tmlanguage based highlighting isn't ideal. Since we have to write an LSP for intellisense (#15), we might as well include semantic highlighting support.
Ignore braces in column names, as they are in the sql
column from the sqlite_schema
table:
CREATE TABLE "artists"
(
[ArtistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Name] TEXT(120)
);
Ignore quotes in the table names:
const query = generatedSchema.sql<{
GenreId: number;
Name: string | null
}>`SELECT * FROM "genres"`;
Without quotes, column selection fails:
const query = generatedSchema.sql<{
}>`SELECT * FROM genres`;
Ignore column and table case in the queries:
const query = schema.sql<{
Name: string | null
}>`SELECT Name FROM artists`;
Without matching case, column selection fails:
const query = schema.sql<{/*
Could not find selected column name in from clause
*/}>`SELECT name FROM artists`;
Have a user friendly error message when defining schema
and forgetting a semicolon or two:
CREATE TABLE "albums"
(
AlbumId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Title TEXT(160) NOT NULL,
ArtistId INTEGER NOT NULL,
FOREIGN KEY (ArtistId) REFERENCES "artists" (ArtistId)
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE sqlite_sequence(name,seq)
The @vlcn.io/typed-sql-cli
already supports a watch command, which is great! A one-off generate command (without watching) would also be very helpful.
EDIT: Simply put, a plain run
command.
Ensure we don't hit cyclical type-regenerations or "misplace replacements."
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.