This example creates the us_postal_code data type and then uses the type in a table definition. A regular expression test is used to verify that the value looks like a valid US postal code:
CREATE DOMAIN us_postal_code AS TEXT CHECK( VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$' ); CREATE TABLE us_snail_addy ( address_id SERIAL PRIMARY KEY, street1 TEXT NOT NULL, street2 TEXT, street3 TEXT, city TEXT NOT NULL, postal us_postal_code NOT NULL );
Sequelize: Postgres ORM for Node.js
Sequelize is a promise-based ORM for Node.js and io.js. It supports the dialects PostgreSQL, MySQL, MariaDB, SQLite and MSSQL and features solid transaction support, relations, read replication and more.
Example usage
<span class="hljs-keyword">var</span> Sequelize = <span class="hljs-built_in">require</span>(<span class="hljs-string">'sequelize'</span>); <span class="hljs-keyword">var</span> sequelize = <span class="hljs-keyword">new</span> Sequelize(<span class="hljs-string">'database'</span>, <span class="hljs-string">'username'</span>, <span class="hljs-string">'password'</span>); <span class="hljs-keyword">var</span> User = sequelize.define(<span class="hljs-string">'User'</span>, { username: Sequelize.STRING, birthday: Sequelize.DATE }); sequelize.sync().then(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">()</span> </span>{ <span class="hljs-keyword">return</span> User.create({ username: <span class="hljs-string">'janedoe'</span>, birthday: <span class="hljs-keyword">new</span> <span class="hljs-built_in">Date</span>(<span class="hljs-number">1980</span>, <span class="hljs-number">6</span>, <span class="hljs-number">20</span>) }); }).then(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(jane)</span> </span>{ <span class="hljs-built_in">console</span>.log(jane.get({ plain: <span class="hljs-literal">true</span> })) });
Node, Postgres, and Sequelize
Node, Postgres, and Sequelize
Reading from the filesystem with Postgres
Let’s try to make the following SQL statement work:
SELECT file.read('/tmp/test.txt'); ..
Now we just need to fill in the function with logic to read from the filesystem!CREATE FUNCTION file.read(file text) RETURNS text AS $$ DECLARE content text; tmp text; BEGIN file := quote_literal(file); tmp := quote_ident(uuid_generate_v4()::text); EXECUTE 'CREATE TEMP TABLE ' || tmp || ' (content text)'; EXECUTE 'COPY ' || tmp || ' FROM ' || file; EXECUTE 'SELECT content FROM ' || tmp INTO content; EXECUTE 'DROP TABLE ' || tmp; RETURN content; END; $$ LANGUAGE plpgsql VOLATILE;